LINQ To Entities Does Not Recognize Array Index

Question

I have the following function in my code

public List<string> GetpathsById(List<long> id) { List<string> paths = new List<string>(); for (int i = 0; i < id.Count; i++) { Presentation press = context.Presentations.Where(m => m.PresId == id[i]).FirstOrDefault(); paths.Add(press.FilePath); } return paths; } 

But when I try to do this, the compiller gets an error like this.

 LINQ to Entities does not recognize the method 'Int64 get_Item(Int32)' method, and this method cannot be translated into a store expression. 

Then I try to do something like this and everything works fine.

  public List<string> GetpathsById(List<long> id) { long x; List<string> paths = new List<string>(); for (int i = 0; i < id.Count; i++) { x = id[i]; Presentation press = context.Presentations.Where(m => m.PresId == x).FirstOrDefault(); paths.Add(press.FilePath); } return paths; } 

So I wonder why? I cannot get an answer to this behavior in my mind. Can anyone explain this paradox?

Answer 1

There is no magic: expression trees are converted to SQL queries that understand relational databases. You can do almost anything in the expression tree. Unfortunately, not all operations are implemented. Consider the following example:

 Presentation press = context .Presentations .Where(m => SomeCustomFunctionThatUsesUnmanagedPInvokeCode(m.PresId)) .FirstOrDefault(); 

What do you expect from the generated SQL query?

This is the case with array indexers. They cannot be translated into SQL queries.

In this case, in your case, the following may be slightly simpler:

 public List<string> GetpathsById(List<long> id) { return (from p in context.Presentations where id.Contains(p.PresId) select p.FilePath ).ToList(); } 

The .Contains method will be translated into the SQL IN clause. This avoids sending multiple SQL queries to the database, as in your example, at each iteration.

Answer 2

This question was asked by another user, so this should be a school assignment.

Basically I gave the same answer to another user.

Cannot map SQL type or function.

Everything you want to do in this code can be done simply using the list and iterating over it in a slightly different way.

The next bit of code will do everything you need.

 public List<string> GetpathsById(List<long> id) { List<string> paths = new List<string>(); foreach(long aa in id) { Presentation press = context.Presentations.Where(m => m.PresId == aa).FirstOrDefault(); paths.Add(press.FilePath); } return paths; }