CODEWITHSUNDEEP
entity-frameworklinq-to-entities
nebula
nebula

Reputation: 4002

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?

Upvotes: 113

Views: 78840

Answers (9)

statler
statler

Reputation: 1381

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

Upvotes: 0

Omkar
Omkar

Reputation: 61

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

Upvotes: 3

Mahdi Shahbazi
Mahdi Shahbazi

Reputation: 1073

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

Upvotes: 10

peter70
peter70

Reputation: 1113

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

Upvotes: 0

M.R.T
M.R.T

Reputation: 815

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

Upvotes: 1

Judo
Judo

Reputation: 5247

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

Upvotes: 106

Slauma
Slauma

Reputation: 177163

You can use the TruncateTime method of the EntityFunctions 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

Upvotes: 241

KingOfHypocrites
KingOfHypocrites

Reputation: 9537

For EF6 use DbFunctions.TruncateTime(mydate) instead.

Upvotes: 40

Adam Robinson
Adam Robinson

Reputation: 185693

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.

Upvotes: 1

Related Questions