In this post I am going to discuss about comparing the date part of datetime in a LINQ query. In a LINQ query it is easy to perform datetime comparisons, to compare only date not time part of a datetime field of your entity.
Let us have a coloser look. In SQL Server we can do something like below to just compare the date part of the field.
SELECT * FROM dbo.tbl_MyTable
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) =
CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)
So in the above query convert function does the task of removing the time part and only the date part comparison happens.
Following discussion is about doing the same task using LINQ queries.
The first way to achieve the same thing (i.e., comparing date part) of an entity or object is following:
var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong"
&& DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
Here Date property of DatTime is used to get only the date part of the datetime property and makes use of the
function to get the matching object.
But the problem with this approach is when make use of EnityFramework, i.e., LINQ to Entity, it gives following error at runtime:
The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
To avoid the above error in LINQ to Entity, the query is modified to following:
var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong")
.Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
So in the above query first data is fetched from the database and then on list, date comparison get applied. But the problem with this approach is we need to load all data first then the date comparison gets applied because Entity Framework doesn't support a direct query.
One more easy and simple solution to just compare the date part of a datetime object as follows:
var data1 = context.t_quoted_value.Where(x => x.region_name == "Hong Kong"
&& x.price_date.Value.Year == dt.Year
&& x.price_date.Value.Month == dt.Month
&& x.price_date.Value.Day == dt.Day).ToList();
The query uses year, month, and day properties of the datetime object to compare dates. The advantage of this solution is this is compatible with all flavors of LINQ, i.e., it works in LINQ to SQL, LINQ to object, and also in LINQ to Enitity.
So the post is useful when you need to compare only the date part of the datetime property in LINQ queries.