为何EF抛出"NotSupportedException:方法"First"只能用作最终查询操作"? [英] Why does EF throw "NotSupportedException: The method 'First' can only be used as a final query operation"

问题描述

int[] ids1 = { 1, 2, 3 };
int[] ids2 = { 1, 5, 6 };

var result = from a in ids1
             where a == ids2.First()
             select a;  
foreach (var item in result) ; //ok


var employees = from c in context.Employees.
                where c.EmployeeID == ids1.First() 
                select c;   
foreach (var item in employees); // NotSupportedException

在Linq-to-Entities查询中尝试调用ids1.First时，出现异常 System.NotSupportedException:方法"First"只能用作最终查询操作.考虑在此实例中使用方法"FirstOrDefault" .

When trying to call ids1.First within Linq-to-Entities query, I get an exception System.NotSupportedException: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.

a)我不明白为什么First只能用作最终查询操作，因为在我们的示例中，First是在IEnumerable<>(ids1.First())上而不是在IQueryable<>上调用的.换句话说，在Linq-to-Objects查询中而不是在Linq-to-Entities查询中调用First?

a) I don't understand why First can only be used as a final query operation, since in our example First is called on IEnumerable<> ( ids1.First() ) and not on IQueryable<>. In other words, First is called within Linq-to-Objects query and not within Linq-to-Entities query?!

b)无论如何，为什么必须将First用作最终查询操作，而不必将FirstOrDefault用作最终查询操作?

b) Anyways, why must First be used as a final query operation, while FirstOrDefault doesn't have to be final query operation?

谢谢

回复:

至于First()和FirstOrDefault()之间的区别-我没有 知道.您尝试过了吗，并且有效吗?

As for the difference between First() and FirstOrDefault() - I don't know. Have you tried it, and does it work?

是的

否，在LINQ to Entities查询中正在调用First().你在哪里 子句将被转换为:Where(c => c.EmployeeID == ids1.First())

No, First() is being called in the LINQ to Entities query. Your where clause will be converted to: Where(c => c.EmployeeID == ids1.First())

a)我现在有点困惑.我意识到ids1.First本质上是在Linq-to-Entities查询中调用的，但事实仍然是First是在IEnumerable<>上调用的，因此First在Linq-to-Objects查询中也是如此，并且此Linq对象查询在Linq-To-entities查询中又被称为–至少我是这样理解的！

a) I'm a bit confused now. I realize ids1.First is essentially called within Linq-to-Entities query, but the fact remains that First is called on IEnumerable<>, and as such First is called within Linq-to-Objects query, and this Linq-to-Object query is in turn called within Linq-To-entities query – at least that's how I understand it?!

还是您暗示First是在IQeryable<>上以某种方式调用的?

Or are you implying that First is somehow called on IQeryable<>?

b)我意识到(c => c.EmployeeID == ids1.First())将被转换为表达式树，但是为什么ids1.First()在转换发生之前没有执行?

b) I realize that (c => c.EmployeeID == ids1.First()) will get converted into an expression tree, but why isn't ids1.First() executed before the conversion happens?

c)无论如何，一旦确实发生了到表达式树的转换，我假定当sql provider收到我们的where子句的表达式树并尝试将其转换为Sql命令时，该Sql provider并没有权力". 执行ids1.First以便取回结果(然后将其放入Sql查询中)，因此是异常?！

c) Anyways, once the conversion into expression tree does happen, I assume when sql provider receives the expression tree of our where clause and tries to convert it into Sql command, this Sql provider doesn't have "the power" to execute ids1.First in order to get the result back ( which it would then put into Sql query ), and hence the exception?!

第二次答复:

a)我仍然感到困惑，为什么在转换成表达式树之前不执行ids1.First?即，带有以下子句

a) I'm still confused why ids1.First isn't executed before the conversion into expression tree happens?! Namelly, with the following clause

Where(c => c.EmployeeID == 2+3) 

表达式2+3在此 Where 子句转换为表达式树之前被执行！而且ids.First也是一种表达方式，所以我期望有类似的行为吗?！

the expression 2+3 gets executed before this Where clause is transformed into expression tree! And ids.First is also an expression of sorts, so I would expect similar behavior?!

b)很抱歉重复您的内容，但这真的使我感到困惑，因为我的假设是- First在Linq-to-Objects查询中被调用，而此Linq-to-Object查询又在Linq-To-entities查询中被调用–是吗?！

b) Sorry for being repetitive, but it's really bugging me whether my assumption - that First is called within Linq-to-Objects query, and this Linq-to-Object query is in turn called within Linq-To-entities query – is correct?!

c)也许我误解了您的帖子，但是您是否暗示即使E包含在Linq-to-Entities查询中，也可以在IEnumerable<> E上调用大多数其他Linq-to-Object运算符?

c) Perhaps I misunderstood your post, but are you implying that most other Linq-to-Object operators can be called on IEnumerable<> E, even if E is contained within Linq-to-Entities query ?

推荐答案

否，在LINQ to Entities查询中调用了First() .您的where子句将转换为:

No, First() is being called in the LINQ to Entities query. Your where clause will be converted to:

Where(c => c.EmployeeID == ids1.First())

该lambda表达式将被转换为表达式树.

That lambda expression will be converted to an expression tree.

当然，在查询中 进行操作非常简单:

Of course it's pretty simple to do that outside the query:

int firstId = ids1.First();
var employees = from c in context.Employees
                where c.EmployeeID == firstId
                select c;

它变得更加简单:

int firstId = ids1.First();
var employees = context.Employees.Where(c => c.EmployeeID == firstId);

关于First()和FirstOrDefault()之间的区别-我不知道.您尝试过了吗，它有效吗?也许是因为First()在空序列上调用时将引发异常，并且由于某种原因该行为可能很难转换.

As for the difference between First() and FirstOrDefault() - I don't know. Have you tried it, and does it work? Perhaps it's because First() will throw an exception when called on an empty sequence, and that behaviour may be hard to translate for some reason.

是的，查询提供者可以可能会看一下表达式树的那一部分并加以解决-但迟早您必须在查询提供者必须多么聪明的基础上画一条线是.它已经做了很多工作，您在这里做工作很容易(按照我上面的示例)-那么为什么不这样做呢?

Yes, the query provider could potentially look at that bit of the expression tree and work it out - but sooner or later you have to draw a line at how smart the query provider has to be. It's doing a lot of work already, and it's pretty easy for you to do the work here (as per my example above) - so why not do so?

请记住，从逻辑上讲，First()是为context.Employees的每个元素执行的-因此，如果存在任何行，则只需要 在空集合上抛出异常-否则First()调用从不逻辑地进行.瞧，这并不是您想像的那么简单 :)在这种情况下，您碰巧知道有 are 个元素，因此您可以在不加任何惩罚的情况下预先呼叫First()-但是查询提供者不能.

Bear in mind that logically, First() is executed for each element of context.Employees - so it would need to only throw an exception on an empty collection if any rows existed - otherwise the First() call is never logically made. See, it's not quite as simple as you might expect :) In this case you happen to know that there are elements, so you can call First() with impunity beforehand - but the query provider can't.

回复第二次编辑...

Reply to second edit...

a)表达式2 + 3是编译时常量.将其更改为x + 2，添加操作 将成为表达式树的一部分.特别是，如果您更改x的值(或示例中的ids1)将更改查询，则您无济于事，无法更改2 + 3的含义.

a) The expression 2+3 is a compile-time constant. Change it to x + 2 and the addition operation will be part of the expression tree. In particular, if you change the value of x (or ids1 in your example) that will change the query - there's nothing you can do to change what 2+3 means.

b)我不清楚您的意思.如果您的意思是，EF查询中包含的表达式树包括对Enumerable.First的调用.

b) It's not clear to me what you mean by that. The expression tree contained within the EF query includes a call to Enumerable.First, if that's what you mean.

c)由查询提供程序决定要支持的表达式树的确切类型-这适用于其他方法调用(例如int.Parse)以及LINQ to Objects方法.

c) It's up to the query provider to decide exactly what expression trees to support - and that goes for other method calls (e.g. int.Parse) as well as LINQ to Objects methods.

这篇关于为何EF抛出"NotSupportedException:方法"First"只能用作最终查询操作"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！