I'm having an entity with a TimeSpan
property (Start
) and an int
(Duration
in minutes) property.
I want to perform a linq query that will fetch all the rows that apply to the following logic:
Start <= DateTime.Now <= Start + Duration
My LINQ
from n in _context.Notifications
where n.Start <= DateTime.Now.TimeOfDay && DateTime.Now.TimeOfDay <= n.Start.Add(new TimeSpan(0, n.Duration, 0))
select n;
translates into (as taken from the SQL Server Profiler)
SELECT [n].[Id], [n].[Active], [n].[Created], [n].[Day], [n].[Duration], [n].[Name], [n].[Start], [n].[Type]
FROM [Notifications] AS [n]
WHERE [n].[Start] <= CAST(GETDATE() AS time)
The warning that I'm getting from the EntityFramework Core is
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where (DateTime.Now.TimeOfDay <= [n].Start.Add(new TimeSpan(0, [n].Duration, 0)))' >could not be translated and will be evaluated locally.
The SQL query that I want is this
SELECT [n].[Id], [n].[Active], [n].[Created], [n].[Day], [n].[Duration], [n].[Name], [n].[Start], [n].[Type]
FROM [Notifications] AS [n]
WHERE CAST(GETDATE() AS TIME) BETWEEN [n].[Start] AND DATEADD(MINUTE, [n].[Duration], [n].[Start])
Simple solution: do not run the calculation as part of the LINQ query. Do the timespan first, then run 2 datetiime instances in as parameters. Done.