SQL Server: DateTime.DayOfWeek translation

[AndrewBoklashko]

Currently there is no translation for DayOfWeek property of DateTime value, it evaluates on client. When using in complex queries it can cause a lot of client evaluation:

 var requests = await db
                     .Set<Request>()
                     .Where(r => schedules
                        .Any(s => s.DayOfWeek == (int)r.request.Job.StartJob.DayOfWeek
                        && (r.timeStart != 0 && r.timeEnd != 0
                            && r.timeEndUpperBound >= s.TimeStart
                            && r.timeEndLowerBound <= s.TimeEnd
                            && r.timeStartUpperBound >= s.TimeStart
                            && r.timeEndLowerBound <= s.TimeEnd
                        ||
                            r.timeStart == 0
                            && r.timeEndUpperBound >= s.TimeStart
                            && r.timeEndLowerBound <= s.TimeEnd
                        ||
                            r.timeEnd == 0
                            && r.timeStartUpperBound >= s.TimeStart
                            && r.timeStartLowerBound <= s.TimeEnd)))
                    .Select(r => r.request)
                    .OrderBy(x => x.Job.StartJob)
                    .MapToRequestInListDTO()
                    .ToListAsync();

The LINQ expression '"where (([s].DayOfWeek == ((Convert([x.Job].StartJob.DayOfWeek, Int32) + 6) % 7)) AndAlso ((((((((([r].HoursStart + ([r].MinutesStart / 60)) != 0) AndAlso (([r].HoursEnd + ([r].MinutesEnd / 60)) != 0)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) OrElse (((([r].HoursStart + ([r].MinutesStart / 60)) == 0) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd))) OrElse (((([r].HoursEnd + ([r].MinutesEnd / 60)) == 0) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) - __minutesShift_3) <= [s].TimeEnd))))"' could not be translated and will be evaluated locally. (011792c6)
The LINQ expression '"Any()"' could not be translated and will be evaluated locally. (011792c6)
The LINQ expression '"where {from Schedule s in __schedules_7 where (([s].DayOfWeek == ((Convert([x.Job].StartJob.DayOfWeek, Int32) + 6) % 7)) AndAlso ((((((((([r].HoursStart + ([r].MinutesStart / 60)) != 0) AndAlso (([r].HoursEnd + ([r].MinutesEnd / 60)) != 0)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) OrElse (((([r].HoursStart + ([r].MinutesStart / 60)) == 0) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd))) OrElse (((([r].HoursEnd + ([r].MinutesEnd / 60)) == 0) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) - __minutesShift_3) <= [s].TimeEnd)))) select [s] => Any()}"' could not be translated and will be evaluated locally. (011792c6)
The LINQ expression '"where (([s].DayOfWeek == ((Convert([x.Job].StartJob.DayOfWeek, Int32) + 6) % 7)) AndAlso ((((((((([r].HoursStart + ([r].MinutesStart / 60)) != 0) AndAlso (([r].HoursEnd + ([r].MinutesEnd / 60)) != 0)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd)) OrElse (((([r].HoursStart + ([r].MinutesStart / 60)) == 0) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) + __minutesShift_6) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursEnd + ([r].MinutesEnd / 60)), Decimal) - __minutesShift_5) <= [s].TimeEnd))) OrElse (((([r].HoursEnd + ([r].MinutesEnd / 60)) == 0) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) + __minutesShift_4) >= [s].TimeStart)) AndAlso ((Convert(([r].HoursStart + ([r].MinutesStart / 60)), Decimal) - __minutesShift_3) <= [s].TimeEnd))))"' could not be translated and will be evaluated locally. (011792c6)
The LINQ expression '"Any()"' could not be translated and will be evaluated locally. (011792c6)

I fixed this by rewriting query using EF Core database scalar functions and SQL DATEPART and now it's fully executed on database.

If I get things right the main problem with DATEPART is that its result is one-based and depends on @@DATEFIRST value, that's why it cant just be matched to System.DayOfWeek enum. On the other hand as you could see it is really important to get it translated.

P. S. Actual query is a bit simplified, so that log doesn't exactly corresponds to it, but that doesn't really change things a lot.

[AndrewBoklashko]

I would like to contribute a pull request regarding this issue. I figured out the next formula to get DayOfWeek value:

SELECT (DATEPART(DW, @date) + @@DATEFIRST + 6) % 7

Next code verify correctness, it just prints 'OK!' 49 times:

-- offset from Sunday e.g. .NET DayOfWeek value
DECLARE @offset INT = 0

-- SQL @@DATEFIRST value
DECLARE @df INT

-- formula result
DECLARE @result INT

-- loop week days
WHILE @offset < 7
BEGIN
    -- add offset to Sunday date
    DECLARE @date DATETIME2 = DATEADD(DAY, @offset, CAST('2017-11-26 23:20:44.427' AS DATETIME2))

    -- reset datefirst
    SET @df = 1

    -- loop datefirst
    WHILE @df < 8
    BEGIN
        SET DATEFIRST @df

        -- calculate result within formula
        SET @result = (DATEPART(DW, @date) + @@DATEFIRST + 6) % 7

        IF @result != @offset -- compare with offset e.g. .NET DayOfWeek value
            PRINT 'ERROR!'
        ELSE
            PRINT 'OK!'

        SET @df = @df + 1
    END

    SET @offset = @offset + 1
END

But in order to not doing useless job it would be good if someone from EF Core team confirm that we really need this and accept proposed implementation.

[ErikEJ]

What happens if you change to another server language?? https://docs.microsoft.com/en-us/sql/t-sql/functions/datefirst-transact-sql

[AndrewBoklashko]

The above formula works preserving @@DATEFIRST value, so even if you change server language the result will still be the same. Test code demonstrates it: @@DATEFIRST is changing inside the inner loop, but the result actually remains equal to offset which doesn't change inside the inner loop.

[smitpatel]

How about translating using DateName function on SqlServer? Raw DatePart could be also fine actually depending on how it is consumed since most enums are mapped to underlying type in database. In SQLite, strftime with %w gives 0 indexed day of the week.

[AndrewBoklashko]

DateName depends on Sql Server language, so weekday names will be displayed in a language, that was set on database server and here is where mapping problem appears. Also I reviewed my formula and I think the next equivalent is a little more easier to understand: SELECT (@@DATEFIRST - 1 + DATEPART(DW, @date)) % 7

Regarding Sqlite, you are right, that's pretty straightforward.

[bricelam]

Added to SQLite in #11714

[cyptus]

are there any news here? any workaround?

[ajcvickers]

@cyptus This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

The only workaround I know of is to not use DateTime.DayOfWeek in a SQL Server query.

[cyptus]

thanks @ajcvickers I found another workaround. i can make use of the SQL datepart function with the DbFunction. Tricky part is to tell ef core not to handle the datepart type parameter as a string. Example:

DbContext:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

Query:

repository.Where(x => dbContext.DatePart("weekday", x.CreatedAt) == DayOfWeek.Monday);

more info: https://github.com/aspnet/EntityFrameworkCore/issues/9549

watch out not to call the DbFunction method on an interface of DbContext. The call must happend directly on the DbContext instance (you can cast IDbContext to DbContext for the call).

[angularsen]

This workaround works for me on EF Core 2.2.6:

public static IQueryable<Appointment> FilterOnDayOfWeek(
    IQueryable<Appointment> query,
    params DayOfWeek[] daysOfWeek)
{
    int[] dayOfWeekValues = daysOfWeek.Select(wd => (int) wd).ToArray();
    DateTime firstSunday = new DateTime(1753, 1, 7);
    return query.Where(appointment => dayOfWeekValues.Contains(EF.Functions.DateDiffDay(firstSunday, appointment.Start) % 7));
}

// Usage
var weekdays = new[] { DayOfWeek.Friday, DayOfWeek.Saturday };
List<Appointment> appointments = await FilterOnDayOfWeek(dbContext.Appointments, weekdays).ToListAsync();

One improvement I'd like to do, is to reuse the method for any entity type. I haven't done this before, but I assume I'll have to build the expression dynamically somehow.

I tried @cyptus' solution first, but had problems with one-off mapping to DayOfWeek enum, probably due to a different @@DATEFIRST on my SQL server. Then I realized from this article that it would be more robust to count days since a fixed date to get day of week.

[julienGrd]

what is the current situation of this issue ? i still can't use DayOfWeek in my queries in EF Core 7.

I tried to use the function of @cyptus but it seem the implementation of especially SqlFunctionExpression changed a lot since, so i can't make a code which compile.

Is it possible to put a workaround which work for EF 7 ?

thanks !

[roji]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 8.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.

[julienGrd]

I was able to adapt the workaround put previously to make it compile and work in EF7, for those interested

 public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

 partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
 {
     var methodInfo = typeof(ModelContainer).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
     modelBuilder
         .HasDbFunction(methodInfo)
     .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart),
                                                         new[]
                                                         {
                                                             new SqlFragmentExpression(((SqlConstantExpression)args[0]).Value.ToString()),
                                                             args[1]
                                                         },
                                                         false,
                                                         new List<bool>() { false, false, false },
                                                         typeof(int?),
                                                         null
                                                         )
     );
 }

im really not sure about the arguments, feel free to correct it

[dharmeshtailor]

How do we use this DatePart on the existing queries where we are using DayOfWeek? Does it automatically translates it to DatePart or do we have to do something manually?

[julienGrd]

@dharmeshtailor like this for example

//all appointments happen on a monday
var list = context.Appointments.Where(a => context.DatePart("weekday", a.Date) == (byte)DayOfWeek.Monday).ToList();

[dharmeshtailor]

@dharmeshtailor like this for example

//all appointments happen on a monday
var list = context.Appointments.Where(a => context.DatePart("weekday", a.Date) == (byte)DayOfWeek.Monday).ToList();

Is there a way we can replace DatePart on each occurrence DayOfWeek in query by default using QueryExpressionInterceptor?

Update: I have applied the fix following the solution in below StackOverflow post:

https://stackoverflow.com/questions/77146801/ef-core-queryexpressioninterceptor-for-dayofweek/77148925#77148925

[mip1983]

Can I add a vote for dayofweek to go into EFCore as a datetime function rather than these work arounds? Seems to have been in the backlog a long time.

[roji]

Yep, upvote (👍) the issue!

[pablokawan]

Sooo, we need this when creating complex queries...

[roji]

@pablokawan be sure to upvote (👍), this issue currently has only 13 votes and so is low-priority.

[julienGrd]

since .NET9.0 the workaround not work anymore (for DayOfWeek at least), it finish in exception with ": ''DATEPART' was constructed with 2 arguments, but the nullability was defined for 3 arguments. When in doubt use 'false' for nullability argument.'".

I was able to make it work again by changing this function

  modelBuilder.HasDbFunction(() => DayOfWeekImpl(default))
      .HasTranslation(args =>
          new SqlFunctionExpression("DATEPART",
              new[] { new SqlFragmentExpression("weekday"), args[0] },
              false,
              new[] { false, false, false },
              typeof(int),
              null
          ));

to

  modelBuilder.HasDbFunction(() => DayOfWeekImpl(default))
      .HasTranslation(args =>
          new SqlFunctionExpression("DATEPART",
              new[] { new SqlFragmentExpression("weekday"), args[0] },
              false,
              new[] { false, false },
              typeof(int),
              null
          ));

[roji]

@julienGrd if you're seeing a regression from 8.0 to 9.0, can you please open a new issue with a minimal, runnable code sample that shows the problem?

[julienGrd]

@julienGrd if you're seeing a regression from 8.0 to 9.0, can you please open a new issue with a minimal, runnable code sample that shows the problem?

Actually its not a regression of .net since the functionnality is still not available. Its just the workaround i was talking previously stopped working and can be fixed with the little adaptation i put in my previous message