Accepted answer

Apparently the question has been answered before, because of the new framework, the error message is different, and because of how I got to the answer, thinking that projections where interfering with how the indexer was working.

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

All I had to do was add an AsEnumerable() which converts the result into C# objects, then the select (preserving the logic I originally had) would then add the index to the iterations.

var cities = context.Countries
.Where(s => query.Name == s.Name)
.AsEnumerable()    //  where the magic happens
.Select(s => new CityDTO
    new CityDTO()
        name =,
        stateName = s.stateName
        population = s.population


Actually that LINQ query simply can be translated into indexing row with dbfunction row_number() over () (at least for postgreSQL) but for now (Feb, 2021) still not supported.

Related Articles