在 LINQtoEntitiesException 中不支持指定的类型成员“ Date”

在实现以下语句时，我得到了一个例外。

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

例外

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

我知道异常是什么意思，但我不知道怎么摆脱它，有什么帮助吗？

75943 次浏览
小开

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.

小开
最佳答案

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

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

小开

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

小开

For EF6 use DbFunctions.TruncateTime(mydate) instead.

小开

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

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

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


提交答案