The specified type member Date is not supported in LINQ to Entities

Can you ignore the time-part of the DateTime in a Silverlight RIA with EF?

On a RIA-enabled Silverlight project I wrote the following LINQ to Entities query in my Domain Service:

public IQueryable<Order> GetOrders() {
    var qry = from order in ObjectContext.orders
                where order.OrderDate.Date == DateTime.Now.Date
                select order;
    return qry;
}

I want to ignore the time-part of the DateTime so I only compare the date parts but when I ran the query I got this error:

Load operation failed for query 'GetOrders'. The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Solution Part 1 (Works in a viewmodel)

I’ve had this error before and remember I had to check if my date was between a start and end date. Like this:

var qry = from order in ObjectContext.orders            
    where order.OrderDate >= DateTime.Now.Date           
    && order.OrderDate < DateTime.Now.Date.AddDays(1)            
    select order;
    return qry;

But while this works if you build this query on the client (like in a viewmodel), it fails when it’s constructed in a DomainService with the following error:

Load operation failed for query 'GetOrders'. LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

Solution Part 2 (Works in DomainService)

Well, the answer is easy enough but not too elegant. We need a helper variable where you add a day to the datetime:

var date2 = DateTime.Now.Date.AddDays(1);
var qry = from order in ObjectContext.orders            
             where order.OrderDate >= DateTime.Now.Date            
             && order.OrderDate < date2            
             select order;
             return qry;
Written by Loek van den Ouweland on September 02, 2010. Questions regarding this artice? You can send them to the address below.
By using this site, you acknowledge that you have read and understand our Cookie and Privacy Policy.