Why does EF throw "NotSupportedException: The method 'First' can only be used as a final query operation"

Question

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

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) 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) Anyways, why must First be used as a final query operation, while FirstOrDefault doesn't have to be final query operation?

Thank you

REPLY:

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

Yes it works

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) 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?!

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

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) 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?!

SECOND REPLY:

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) 

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

Answer 1

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

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

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;

which becomes more simply:

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

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.

EDIT: 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?

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.

EDIT: Reply to second edit...

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