blocks|key|217098|text|LINQ+to+Entities无法将大多数SQL方法(包括您使用的强制转换)转换为.NET，因为没有等效的SQL语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217099|解决方案是在LINQ语句外部使用Date方法，然后传入一个值。看起来好像是Convert.ToDateTime(rule.data).Date导致了这个错误。|217100|在DateTime属性上调用Date也不能转换为SQL，因此解决方法是比较.Year+.Month和.Day属性，因为它们只是整数。|217101|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);|code-block|syntax|javascript|217102|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code>var ruleDate = Convert.ToDateTime(rule.data).Date;
return jobdescriptions.Where(j =&gt; j.Deadline.Year == ruleDate.Year 
 &amp;&amp; j.Deadline.Month == ruleDate.Month 
 &amp;&amp; j.Deadline.Day == ruleDate.Day);
</code></pre>

blocks|key|2869060|text|您可以使用的方法来实现Date属性到SQL的正确转换：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2869061|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);|code-block|syntax|javascript|2869062|更新：EntityFunctions在EF6中已弃用，请使用DbFunctions.TruncateTime|BOLD|2869063|entityMap^0|B|4|0|0|0|3|3|F|U|O|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|S|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|T|8|@$9|U|A|V|B|M]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|N|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|O|$]]

You can use the <a href="http://msdn.microsoft.com/en-us/library/dd395596.aspx">TruncateTime</a> method of the <a href="http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.aspx">EntityFunctions</a> to achieve a correct translations of the <code>Date</code> property into SQL:

<pre><code>using System.Data.Objects; // you need this namespace for EntityFunctions

// ...

DateTime ruleData = Convert.ToDateTime(rule.data).Date;
return jobdescriptions
 .Where(j =&gt; EntityFunctions.TruncateTime(j.JobDeadline) == ruleData);
</code></pre>

 
Update: <code>EntityFunctions</code>is deprecated in EF6, Use <code>DbFunctions.TruncateTime</code>

blocks|key|220456|text|对于EF6，请使用DbFunctions.TruncateTime(mydate)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|220457|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For EF6 use DbFunctions.TruncateTime(mydate) instead.

blocks|key|217124|text|ef6中的"EntityFunctions.TruncateTime“或"DbFunctions.TruncateTime”正常工作，但在大数据中存在一些性能问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217125|我认为最好的方法是这样做：|217126|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+);|code-block|syntax|javascript|217127|它比使用日期的某些部分更好。因为查询在大数据中运行得更快。|217128|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>DateTime ruleDate = Convert.ToDateTime(rule.data);

DateTime startDate = SearchDate.Date;

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

return jobdescriptions.Where(j.Deadline &gt;= startDate 
 &amp;&amp; j.Deadline &lt; endDate );
</code></pre>

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

blocks|key|3083064|text|需要包括using+System.Data.Entity;。即使使用ProjectTo<>也能很好地工作|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3083065|var+ruleDate+=+rule.data.Date;
return+jobdescriptions.Where(j+=>+DbFunctions.TruncateTime(j.Deadline)+==+ruleDate);|code-block|syntax|javascript|3083066|entityMap^0|4|P|Y|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Need to include <code>using System.Data.Entity;</code>. Works well even with <code>ProjectTo&lt;&gt;</code>

<pre><code>var ruleDate = rule.data.Date;
return jobdescriptions.Where(j =&gt; DbFunctions.TruncateTime(j.Deadline) == ruleDate);
</code></pre>

blocks|key|2869013|text|这意味着LINQ+to+SQL不知道如何将Date属性转换为SQL表达式。这是因为DateTime结构的Date属性在SQL中没有类似的属性。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2869014|entityMap^0|L|4|15|8|1G|4|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|2869151|text|：这对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2869152|DateTime+dt+=+DateTime.Now.Date;
var+ord+=+db.Orders.Where
++++++(p+=>+p.UserID+==+User+&&+p.ValidityExpiry+<=+dt);|code-block|syntax|javascript|2869153|来源：Asp.net+Forums|offset|length|2869154|entityMap|0|LINK|mutability|MUTABLE|url|https://forums.asp.net/post/4934580.aspx^0|0|0|3|E|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

It worked for me.

<pre><code>DateTime dt = DateTime.Now.Date;
var ord = db.Orders.Where
 (p =&gt; p.UserID == User &amp;&amp; p.ValidityExpiry &lt;= dt);
</code></pre>

Source: <a href="https://forums.asp.net/post/4934580.aspx" rel="nofollow noreferrer">Asp.net Forums</a>

blocks|key|217166|text|我也有同样的问题，但我使用的是DateTime-Ranges。我的解决方案是将开始时间(具有任意日期)设置为00:00:00，将结束时间设置为23:59:59，这样我就不必再将DateTime转换为Date，而是保持为DateTime。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|217167|如果您只有一个DateTime，您还可以将开始时间(具有任意日期)设置为00:00:00，将结束时间设置为23:59:59，然后您可以像搜索时间跨度一样进行搜索。|217168|var+from+=+this.setStartTime(yourDateTime);
var+to+=+this.setEndTime(yourDateTime);

yourFilter+=+yourFilter.And(f+=>+f.YourDateTime.Value+>=+from+&&+f.YourDateTime.Value+<=+to);|code-block|syntax|javascript|217169|你也可以用DateTime-Range来做这件事：|217170|var+from+=+this.setStartTime(yourStartDateTime);
var+to+=+this.setEndTime(yourEndDateTime);

yourFilter+=+yourFilter.And(f+=>+f.YourDateTime.Value+>=+from+&&+f.YourDateTime.Value+<=+to);|217171|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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.

<pre><code>var from = this.setStartTime(yourDateTime);
var to = this.setEndTime(yourDateTime);

yourFilter = yourFilter.And(f =&gt; f.YourDateTime.Value &gt;= from &amp;&amp; f.YourDateTime.Value &lt;= to);
</code></pre>

Your can do it also with DateTime-Range:

<pre><code>var from = this.setStartTime(yourStartDateTime);
var to = this.setEndTime(yourEndDateTime);

yourFilter = yourFilter.And(f =&gt; f.YourDateTime.Value &gt;= from &amp;&amp; f.YourDateTime.Value &lt;= to);
</code></pre>

blocks|key|2869259|text|正如许多人在这里指出的那样，使用TruncateTime函数很慢。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2869260|如果可以的话，最简单的选择就是使用EF+Core。它可以做到这一点。如果不能，那么truncate的一个更好的替代方案是根本不更改查询字段，而是修改边界。如果你正在做一个普通的“介于”类型的查询，其中下限和上界是可选的，下面的代码就可以解决这个问题。|2869261|++++public+Expression<Func<PurchaseOrder,+bool>>+GetDateFilter(DateTime?+StartDate,+DateTime?+EndDate)
++++{
++++++++var+dtMinDate+=+(StartDate+??+SqlDateTime.MinValue.Value).Date;
++++++++var+dtMaxDate+=+(EndDate+==+null+%7C%7C+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;
++++}|code-block|syntax|javascript|2869262|基本上，我们增加了查询上限和PoDate+<而不是<=，而不是将user修剪回仅日期部分|2869263|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code> public Expression&lt;Func&lt;PurchaseOrder, bool&gt;&gt; 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 =&gt; x.PoDate != null &amp;&amp; x.PoDate.Value &gt;= dtMinDate &amp;&amp; x.PoDate.Value &lt; dtMaxDate;
 }
</code></pre>

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

I got a exception while implementing the following statements.

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

Exception

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

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

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

大数据

在实现以下语句时，我得到了一个异常。 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 retu

问在LINQ to Entities异常中不支持指定的类型成员'Date‘
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在LINQ to Entities异常中不支持指定的类型成员'Date‘EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在LINQ to Entities异常中不支持指定的类型成员'Date‘
EN