CODEWITHSUNDEEP
entity-frameworklinq
Gianpiero
Gianpiero

Reputation: 3547

How to Include a count(*) as additional result field of a query in LINQ

Given a table of players (users) with several fields. One of this is their rating with respect other players.

I'd like to implement via LINQ following SQL query:

SELECT p.*,
    (select COUNT(*) from Users where (Rating > p.Rating)) as Rank
FROM Users as p
ORDER BY p.Rating DESC

In other words, last field (RANK) should give the rank of each user with respect the others:

Id  Username  ... Rating     Rank
43  player41  ... 1002,333  0
99  player97  ... 1002      1
202 player200 ... 1002      1
53  player51  ... 1000,667  2
168 player166 ... 1000,667  2
56  player54  ... 1000      3
32  player30  ... 999,342   4

This attempt does not work:

var q = from u in Users
        orderby u.Rating descending
        group u by u.Id into g
        select new 
        { 
            MyKey = g.Key, 
            User = g.First(), 
            cnt = Users.Count(uu => uu.Rating > g.First().Rating) + 1 
        };

Just for your knowledge, note that the table Users is mapped to a EF entity named User with a 'NotMapped' int? field named Rank where I'd like to manually copy the rank:

class User {
    ...
    [NotMapped]
    public virtual int? Rank { get; internal set; }
}

Upvotes: 1

Views: 366

Answers (2)

MarkFl
MarkFl

Reputation: 368

You'll want something like:

var rankedUsers = db.Users
    .Select(user => new
    {
        User = user,
        Rank = db.Users.Count(innerQueryUser => innerQueryUser.Rating > user.Rating)
    })
    .ToList();

Which will give you a list of users and their Rank as an anonymous type. You'll then be able to do something like:

List<User> users = rankedUsers.Select(rankedUser=>
{
    rankedUser.User.Rank = rankedUser.Rank;
    return rankedUser.User;
})
.ToList();

Upvotes: 2

Sunil
Sunil

Reputation: 3424

Try this:

 var q = (from u in Users
         select new 
        {
           UserId = u.Id,
           UserObj = u,
           Rank = (from u1 in Users where u1.Rating>u.Rating select u1).Count()
        }).ToList();

Upvotes: 2

Related Questions