Monday, November 26, 2012

Linq Query to compare only Date part of DateTime

In this post I am going to discuss about comparing the date part of the datetime in linq query. In linq query its easy to perform datetime comparison how to compare only date not time part of datetime filed of your entity.

Let have coloseure look, In Sql Server one can do something like as below to just compare 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 above query convert function did task of removing time part and only date part comparison happens.

Linq Queires
Following discussion about doing same task using linq queries.
Solution 1:
First way to achieve same thing (i.e. comparing date part) of 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 used to get only date part of datetime property and made use of DateTime.Compare function to get matching object.

But the problem with this approach when make use of EnityFramework i.e. Linq To Entity , its 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.

so to avoid above error in Linq to Entity 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 above query first data is get fetched from the database and than on list date comparison get applied. But the problem with this approach is need to load all data first than the date comparison get applied because entityframework doesn't support direct query.

Solution 2:
One more easy and simple solution to just compare datet part of datetime object is as following
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();
its query use the year,month and day property of datetime object to compare date. Advantage of this solution is this is compatible with all flavor 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 date part of datetime property in Linq queries.

Leave your comments if you like it.


  1. These shops offer women intimate and individual-focused retail experiences, an
    offering that JCP enney must continue showing these women they can rely on their brand promises.
    Consider using a pay-per-click strategy as a good investment
    at this time. Then there is the same, when you aim for developing a site that can last for the long term.

    Have a look at my blog post :: seo tools

  2. As far as I understand them, if I've made an error I will update and add info as new issues or information crop up. John P Falcone says there's a new
    Nintendo DS coming out in Japan since September 8, 2011 and
    has gotten astounding reviews. Rev Franklin Graham
    on Wednesday suggested that movies and xbox 360 shooter depicting violence should be subject to extra
    taxation. It only takes a few minutes, and the sound is obnoxiously repetitive and muffled.

    Feel free to visit my website: call of duty black ops zombies five map

  3. I am not positive where you are getting your information, but good topic.
    I needs to spend a while finding out more or working out
    more. Thanks for excellent information I used to be on the lookout
    for this info for my mission.

    My web-site :: best law firms in phoenix

  4. Its not a part to compare only date part from datetime using linq. Datetime.Compare is checking for time also. Please verify once and provide your examples if any,