Error in comparing DateTime

Question

My first attempt to compare DateTime

var searchResult = new PagedData<SAMPLE_STOCK>
{
Data = _db.SAMPLE_STOCK.Where(m => m.LAST_UPDATED.Date == lastUpdate.Date)
};
return PartialView(searcReasult);

it gives the following error

The specified type member 'Date' is not supported in LINQ to 
Entities. Only initializers, entity members, and entity navigation 
properties are supported.

Then I write following code

var searchResult = new PagedData<SAMPLE_STOCK>
{
Data = _db.SAMPLE_STOCK.Where(m => m.LAST_UPDATED.Day == lastUpdate.Day 
                                && m.LAST_UPDATED.Month == lastUpdate.Month
                                && m.LAST_UPDATED.Year==lastUpdate.Year)
};

It works fine. My question is.... What is the difference between these??

Read this: http://stackoverflow.com/questions/16736252/using-datetime-in-linq-to-entities — Snake Eyes, Jun 26 '13 at 08:03
Possible duplicate: http://stackoverflow.com/questions/7320608/simple-way-to-compare-dates-in-datetime-attribute-using-entity-framework-4-and-l — Abbas, Jun 26 '13 at 08:03

score 0 · Answer 1 · answered Jun 26 '13 at 08:15

The message

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

is pretty self explanatory. SQL won't know how to fetch Date property of a DateTime type. It can work with DateTime. It can work with Integers. In you case, you'll have to make it work with integers.

As far as performance it concerned.. I don't see any bad impacts.

PS:The support of Date datatype in SQL is not related to this.

score 0 · Accepted Answer · answered Jun 26 '13 at 08:21

The exception is thrown because your predicate cannot be translated into SQL. If you cast it to Enumerable, it could work because you force to use linq2object filtering, but I think your second query is more efficient than this:

var searchResult = new PagedData<SAMPLE_STOCK>
{
    Data = _db.SAMPLE_STOCK.AsEnumrable().Where(m => m.LAST_UPDATED.Date == lastUpdate.Date)
};
return PartialView(searcReasult);

It's your choice, this option provides less code, but more memory usage.

Error in comparing DateTime

2 Answers2