CODEWITHSUNDEEP
c#entity-frameworklinq
Joel Dean
Joel Dean

Reputation: 2454

LINQ to Entities does not recognize the method

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.

Upvotes: 3

Views: 2487

Answers (1)

ChaseMedallion
ChaseMedallion

Reputation: 21794

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:

  1. 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

  2. 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.

Upvotes: 2

Related Questions