dotnet
SQL Server: DateTime.DayOfWeek translation
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.
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.
What happens if you change to another server language?? https://docs.microsoft.com/en-us/sql/t-sql/functions/datefirst-transact-sql
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.
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.
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.
@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.
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).
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.
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 !
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.
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
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?
@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 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
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.
@pablokawan be sure to upvote (👍), this issue currently has only 13 votes and so is low-priority.
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
));
@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 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
The workaround used in .net9 have some strong side effect in .net10 (https://github.com/dotnet/efcore/issues/37191).
Indeed, now my DbContext finish with this exception after a bit "ManyServiceProvidersCreatedWarning" and the performance are horrible.
Remove these extensions method solve the problem but we are back to the priginal problem, we can't use DayOfWeek in EF Queries.
Do you guys have another workaround to solve this case ?
thanks !
I have no idea what exact workarounds you were using, why anything changed in .NET 10 around them or why they'd produce a ManyServiceProvidersCreatedWarning. In any case, for now the only workaround is to use FromSql().
The reason the stackoverflow workaround doesn't work anymore is this change: a06e9cb1b3e95a0542a0f67e21cd62e62de868e0
You have to change your implementation to use the singleton pattern and make sure the same instance is added as interceptor. When I did this, our performace was back to normal.
@wimr ok thanks for the explanation ! but i finally move on to FromSql, i will wait for official support now