The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities. but is supported by linq-to-sql

Question

A project just switched from linq-to-sql to linq-to-entities, and now I get the error

The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities.

for this line:

var a = db.Table.Single(d => d.Date == dates[0]);

(Fixing it in this particular case is easy, as in

var firstDate = dates[0];
var a = db.Table.Single(d => d.Date == firstDate);

)

But why does this work in linq-to-sql but not in linq-to-entities? Did they make linq-to-entities worse than linq-to-sql? What am I missing?

Answer 1

That's because L2E just tries to translate your query to a sql command. So, any additional things (methods like .ToString(), and other things which can't be translated to SQL) leads to that exception.

However, L2S like linq to objects implements IEnumerable. So, the goal of them is different: L2E to translate linq queries to sql commands, L2O to work with in-memory IEnumerable objects, and L2S to model and work with a database.

Now, if want to be able to use your L2S queries in your EF project(using L2E), you should first convert your data retrieved from you DbContext to IEnumerable:

var a = db.Table.AsEnumerable().Single(d => d.Date == dates[0]); 
// or any other methods...