LINQ To Entities doesn't recognize array index

Question

I have 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 this, compiller get 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 all 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 can't get any answer for that behaviour in my mind. Can anyone explain this paradox?

Darin Dimitrov · Accepted Answer · 2011-12-02T09:13:21.707

There's no magic: expression trees are translated into SQL queries which is what relational databases understand. You could do almost anything in an 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 the generated SQL query to be?

That's the case with array indexers. They cannot be translated into SQL queries.

This being said, in your case, the following might be a little 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 a SQL IN clause. This avoids sending multiple SQL queries to the database as you do in your example on each iteration.

Thanks...But can't he translate simply the value of that index into SQL? — Chuck Norris, Dec 02 '11 at 09:10
@mesiesta, no it can't translate the index. As to why, well, you will have to ask Microsoft engineers why they didn't implement it. As far as why `.Contains` works, well, same reason: Microsoft engineers implemented it. — Darin Dimitrov, Dec 02 '11 at 09:17

score 1 · Answer 2 · answered Dec 02 '11 at 09:11

This question was asked by another user so it must be a school assignment.

Basically I gave this same answer to the other user.

It cannot be mapped to an SQL type or function.

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

The following bit of code will do everything you need it to.

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;  
}

Is this tha same answer that is in this topic? http://stackoverflow.com/questions/8353948/the-linq-expression-node-type-arrayindex-is-not-supported-in-linq-to-entities — Chuck Norris, Dec 02 '11 at 09:13

LINQ To Entities doesn't recognize array index

2 Answers2

Linked