How to translate TimeSpan calculations to SQL via LINQ

.net-core c# entity-framework-core linq

Question

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])
1
4
1/15/2019 8:32:26 AM

Popular Answer

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.

1
1/15/2019 8:34:47 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow