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

I got a exception while implementing the following statements.

 DateTime result;
 if (!DateTime.TryParse(rule.data, out result))
     return jobdescriptions;
 if (result < new DateTime(1754, 1, 1)) // sql can't handle dates before 1-1-1753
     return jobdescriptions;
 return jobdescriptions.Where(j => j.JobDeadline.Date == Convert.ToDateTime(rule.data).Date );

Exception

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

I know what the exception means but i don't know how to get rid of it. Any help?

You can use the [TruncateTime][1] method of the [EntityFunctions][2] to achieve a correct translations of the Date property into SQL:

using System.Data.Objects; // you need this namespace for EntityFunctions

// ...

DateTime ruleData = Convert.ToDateTime(rule.data).Date;
return jobdescriptions
    .Where(j => EntityFunctions.TruncateTime(j.JobDeadline) == ruleData);

Update: EntityFunctionsis deprecated in EF6, Use DbFunctions.TruncateTime [1]: http://msdn.microsoft.com/en-us/library/dd395596.aspx [2]: http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx

LINQ to Entities cannot translate most .NET Date methods (including the casting you used) into SQL since there is no equivalent SQL.

The solution is to use the Date methods outside the LINQ statement and then pass in a value. It looks as if Convert.ToDateTime(rule.data).Date is causing the error.

Calling Date on a DateTime property also cannot be translated to SQL, so a workaround is to compare the .Year .Month and .Day properties which can be translated to LINQ since they are only integers.

var ruleDate = Convert.ToDateTime(rule.data).Date;
return jobdescriptions.Where(j => j.Deadline.Year == ruleDate.Year 
                       && j.Deadline.Month == ruleDate.Month 
                       && j.Deadline.Day == ruleDate.Day);

For EF6 use DbFunctions.TruncateTime(mydate) instead.

"EntityFunctions.TruncateTime" or "DbFunctions.TruncateTime" in ef6 Is Working but it has some performance issue in Big Data.

I think the best way is to act like this:

DateTime ruleDate = Convert.ToDateTime(rule.data);

DateTime  startDate = SearchDate.Date;

DateTime  endDate = SearchDate.Date.AddDay(1);

return jobdescriptions.Where(j.Deadline >= startDate 
                       && j.Deadline < endDate );

it is better than using parts of the date to. because query is run faster in large data.

Need to include using System.Data.Entity;. Works well even with ProjectTo<>

var ruleDate = rule.data.Date;
return jobdescriptions.Where(j => DbFunctions.TruncateTime(j.Deadline) == ruleDate);

What it means is that LINQ to SQL doesn't know how to turn the Date property into a SQL expression. This is because the Date property of the DateTime structure has no analog in SQL.

It worked for me.

DateTime dt = DateTime.Now.Date;
var ord = db.Orders.Where
      (p => p.UserID == User && p.ValidityExpiry <= dt);

Source: Asp.net Forums

I have the same problem but I work with DateTime-Ranges. My solution is to manipulate the start-time (with any date) to 00:00:00 and the end-time to 23:59:59 So I must no more convert my DateTime to Date, rather it stays DateTime.

If you have just one DateTime, you can also set the start-time (with any date) to 00:00:00 and the end-time to 23:59:59 Then you search as if it were a time span.

var from = this.setStartTime(yourDateTime);
var to = this.setEndTime(yourDateTime);

yourFilter = yourFilter.And(f => f.YourDateTime.Value >= from && f.YourDateTime.Value <= to);

Your can do it also with DateTime-Range:

var from = this.setStartTime(yourStartDateTime);
var to = this.setEndTime(yourEndDateTime);

yourFilter = yourFilter.And(f => f.YourDateTime.Value >= from && f.YourDateTime.Value <= to);

As has been pointed out by many here, using the TruncateTime function is slow.

Easiest option if you can is to use EF Core. It can do this. If you can't then a better alternative to truncate is to not change the queried field at all, but modify the bounds. If you are doing a normal 'between' type query where the lower and upper bounds are optional, the following will do the trick.

    public Expression<Func<PurchaseOrder, bool>> GetDateFilter(DateTime? StartDate, DateTime? EndDate)
    {
        var dtMinDate = (StartDate ?? SqlDateTime.MinValue.Value).Date;
        var dtMaxDate = (EndDate == null || EndDate.Value == SqlDateTime.MaxValue.Value) ? SqlDateTime.MaxValue.Value : EndDate.Value.Date.AddDays(1);
        return x => x.PoDate != null && x.PoDate.Value >= dtMinDate && x.PoDate.Value < dtMaxDate;
    }

Basically, rather than trimming PoDate back to just the Date part, we increment the upper query bound and user < instead of <=