How to convert following sql query to Linq,
Select F1, F2
From [Table]
Where Convert(Varchar(10), [OnDate], 103) = '12/08/2019'
1 Answer
The correct way to filter datetime values by date only is to use cast( ... as date) :
declare @myDate date='20190812'
Select F1, F2
From [Table]
Where cast([OnDate] as date)=@myDate
This avoids any parsing and localization errors and takes advantage of indexes that cover the OnDate column. Without it the server would have to scan the entire table to convert the dates into strings before comparing them.
This is due to the dynamic seek optimizations introduced at least as far back as SQL Server 2008 R2.
LINQ by itself doesn't query databases. It's a query language that gets translated by an ORM into actual SQL statements. Writing the equivalent of cast( ... as date) depends on the ORM.
In LINQ to SQL, calling DateTime.Date generates a cast( as date) :
var data = context.MyTable.Where(row=>row.OnDate.Date=someDate.Date);
or
var data = from row in contect.MyTable
where row.OnDate.Date=someDate.Date
select row;
EF doesn't recognize this and requires the DbFunctions.TruncateTime call :
var data = context.MyTable.Where(row=>DbFunctions.TruncateTime(row.OnDate)=someDate.Date);
EF Core once again recognizes DateTime.Date :
var data = context.MyTable.Where(row=>row.OnDate.Date=someDate.Date);
answered Sep 10, 2019 at 10:19
Panagiotis Kanavos
134k16 gold badges211 silver badges271 bronze badges
WHERE OnDate=@someDateorWHERE cast(onDate as date)=@someDateif you want to match only on the date part. SQL Server is smart enough to convert that to a range query. Once you fix that bug, LINQ is a simple.Where(row=>row.OnDate=someDate)someContext.ThatTable.Where(row=>row.OnDate=someDate), with aSelectif only a couple of fields are required. LINQ would generate a parameterized query for this with the date value passed as a parameter