Monday, November 26, 2012

Linq Query to compare only Date part of DateTime

Shout it kick it on DotNetKicks.com
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
WHERE 
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)
                  .ToList();

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")
                            .ToList()
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                            .ToList(); 

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.

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

3 comments:

  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

    ReplyDelete
  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

    ReplyDelete
  3. There is an element of play and planning for the real high performance racing used cars in dayton
    ohio. You'll want to contemplate the expenses incurred in restoring the classic car as follows, quote 'The Ford Boss 302 is another car that has been significant and self-defining for you.
    Add a sensor in the reservoir of the battery coolant system,
    in the hopes of alternative fuel like solar energy.
    The answer to this is seeing what sort of kit used cars
    in dayton ohio for sale.

    my weblog; used car sites

    ReplyDelete