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

user_787793 0
Viewed 75791

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?

linq-to-entities entity-framework
9 Answers

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);
user_86179180

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 <=

user_6186600
Related