LINQ to Entities does not recognize the method

Question

This query I wrote is failing and I am not sure why.

What I'm doing is getting a list of user domain objects, projecting them to a view model while also calculating their ranking as the data will be shown on a leaderboard. This was how I thought of doing the query.

 var users = Context.Users.Select(user => new
        {
            Points = user.UserPoints.Sum(p => p.Point.Value),
            User = user
        })
        .Where(user => user.Points != 0 || user.User.UserId == userId)
        .OrderByDescending(user => user.Points)
        .Select((model, rank) => new UserScoreModel
        {
            Points = model.Points,
            Country = model.User.Country,
            FacebookId = model.User.FacebookUserId,
            Name = model.User.FirstName + " " + model.User.LastName,
            Position = rank + 1,
            UserId = model.User.UserId,
        });

        return await users.FirstOrDefaultAsync(u => u.UserId == userId);

The exception message

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[WakeSocial.BusinessProcess.Core.Domain.UserScoreModel] Select[<>f__AnonymousType0`2,UserScoreModel](System.Linq.IQueryable`1[<>f__AnonymousType0`2[System.Int32,WakeSocial.BusinessProcess.Core.Domain.User]], System.Linq.Expressions.Expression`1[System.Func`3[<>f__AnonymousType0`2[System.Int32,WakeSocial.BusinessProcess.Core.Domain.User],System.Int32,WakeSocial.BusinessProcess.Core.Domain.UserScoreModel]])' method, and this method cannot be translated into a store expression.

How is it failing? Is it throwing an exception? What is the exception message if so? — Yacoub Massad, Jan 30 '16 at 21:51
Exception message should include unsupported method name, so yeah, post exception also — Sergey Litvinov, Jan 30 '16 at 21:52
Select is failing, because it expects two objects, but gets only one. Where and OrderByDescending both return an IQueryable/IOrderable, why are you providing select with an IQueryable? — DevilSuichiro, Jan 31 '16 at 06:57
@DevilSuichiro I'm using it because I need the index that's available with select projections like how it's being done here. http://stackoverflow.com/questions/20953645/implementing-rank-over-sql-clause-in-c-sharp-linq — Joel Dean, Jan 31 '16 at 21:18
How are you referencing `u.UserId` while the anonymous type that you select in the end does not contain a property called `UserId`? — Yacoub Massad, Jan 31 '16 at 21:32
@YacoubMassad oh I added the user id to it. But the error still persists — Joel Dean, Jan 31 '16 at 22:09
The problem is that entity framework does not support the overload of `Select` that has both the item and the index. Is it an option for you to materialize the whole list of users to memory and from there obtain the rank? — Yacoub Massad, Jan 31 '16 at 22:10
@YacoubMassad Yes I can do that I just thought the query would be more taxing due to the fact all users would have to be returned. Say I have 50,000 users in my database I wanted to get the current rank of user from the database side without having to load all those users in memory. — Joel Dean, Jan 31 '16 at 22:13
You can select an anonymous type first, then use `AsEnumerable` and then select `UserScoreModel`s using this `Select((model, rank) => ...)` overload. — Gert Arnold, Jan 31 '16 at 22:20
@GertArnold, but wouldn't that force him to get all the objects to memory first? — Yacoub Massad, Jan 31 '16 at 22:24
So there's no way to do something similar to SQL Rank via LINQ without loading in memory first? Ok. — Joel Dean, Jan 31 '16 at 22:26
You could get the user's points and only *count* the number of users with higher numbers of points. — Gert Arnold, Jan 31 '16 at 22:33

score 2 · Answer 1 · edited Sep 11 '18 at 07:41

Unfortunately, EF does not know how to translate the version of Select which takes a lambda with two parameters (the value and the rank).

For your query two possible options are:

If the row set is very small small, you could skip specifying Position in the query, read all UserScoreModels into memory (use ToListAsync), and calculate a value for Position in memory

If the row set is large, you could do something like:

    var userPoints = Context.Users.Select(user => new
    {
        Points = user.UserPoints.Sum(p => p.Point.Value),
        User = user
    })
    .Where(user => user.Points != 0 || user.User.UserId == userId);

    var users = userPoints.OrderByDescending(user => user.Points)
    .Select(model => new UserScoreModel
    {
        Points = model.Points,
        Country = model.User.Country,
        FacebookId = model.User.FacebookUserId,
        Name = model.User.FirstName + " " + model.User.LastName,
        Position = 1 + userPoints.Count(up => up.Points < model.Points),
        UserId = model.User.UserId,
    });

Note that this isn't EXACTLY the same as I've written it, because two users with a tied point total won't be arbitrarily assigned different ranks. You could rewrite the logic to break ties on userId or some other measure if you want. This query might not be as nice and clean as you were hoping, but since you are ultimately selecting only one row by userId it hopefully won't be too bad. You could also split out the rank-finding and selection of base info into two separate queries, which might speed things up because each would be simpler.

LINQ to Entities does not recognize the method

1 Answers1