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

.net c# entity-framework linq

Question

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.

1
25
5/5/2015 2:57:49 PM

Popular Answer

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.

24
5/29/2018 12:04:57 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow