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
WHERE 
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.

LINQ Quries
Following discussion is about doing the same task using LINQ queries.
Solution 1:
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)
                  .ToList();

Here Date property of DatTime is used to get only the date part of the datetime property and makes use of the DateTime.Compare 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")
                            .ToList()
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                            .ToList(); 

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.

Solution 2:
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.