LINQ to Entities does not recognize the method 'System.Linq.IQueryable`

26
votes

I want to run this LINQ simple code to have record number in LINQ but result is beneath error

var model = _db2.Persons.Select(
    (x, index) => new 
    {
        rn = index + 1,
        col1 = x.Id
    }).ToList();

Error:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[<>f__AnonymousType22 [System.Int32,System.Int32]] Select[Person,<>f__AnonymousType22](System.Linq.IQueryable1 [MvcApplication27.Models.Person], System.Linq.Expressions.Expression1[System.Func3 [MvcApplication27.Models.Person,System.Int32,<>f__AnonymousType2`2 [System.Int32,System.Int32]]])' method, and this method cannot be translated into a store expression.

2
c#.netlinqentity-framework
looks like Persons is not IQueryable, try using Cast or OfType (of course if they are present) first. - King King
No, this is because of index to find record number. This is Code First and i have no problem when i remove index in lambda! - ehsan
As a note of your title System.Linq.IQueryable is the return type, not the method name. The stuff you see in the square brackets ([<>f__AnonymousType22 [System.Int32,System.Int32]]) is the stuff that would be in the <int, f__AnonymousType22<>> if you where to type it out. The actual function is after that section Select[Person,<>f_AnonymousType22](...). - Scott Chamberlain

2 Answers

25
votes

The problem is that LINQ to Entities doesn't understand how to convert that Select overload (the one that gives you the index) into a SQL query. You can fix this by first selecting the portion from the DB you need (to avoid selecting every column unnecessarily), then doing AsEnumerable() to take it as an IEnumerable<T> instead of an IQueryable<T>, and then doing the Select purely in C# (in short, IQueryable<T>s are converted to SQL, while IEnumerable<T>s are run in code).

var model = _db2.Persons.Select(x => x.Id).AsEnumerable().Select(
    (id, index) => new
    {
        rn = index + 1,
        col1 = id
    }).ToList();

Note that the query as you have it appears to be unordered, so the id/index pairings can change each time you call this. If you expected consistency, you should order by something (e.g. _db2.Persons.OrderBy(...)).

Edit

Adding comment from Scott:

As a nice reference here is the list of all Linq statements built in to the framework and a listing if it is compatible or not.

2
votes

You could just select the Id and after it create your own anonymous object using linq to objects, for sample:

var model = _db2.Persons.Select(x => x.Id)
                        .ToList() // return int[]
                        .Select((id, index) => new
                                {
                                    rn = index + 1,
                                    col1 = id
                                 }) // return anonymous[] (with rn and col1)
                         .AsEnumerable(); // get an IEnumerable (readonly collection)

Problably this is happen because Entity Framework does not support this kind of query using linq as linq could do in memory, so, in this case, you could select just you need (id in your case) and execute it, using ToList() method to concretize your query and after that you will have a list on memory, so, you can use linq to objects and use the supported method as you want.