Linq to SQL group and count top 25

Question

I have two tables in my database who looks like this:

User:

userid(int)
username(varchar)

Score:

scoreid(int)
result(bit)
attackinguserid(int) (same as User.userid)

They also contain other information but that's not important right now. What i want is to get the top 25 users who has score = true and display them like this:

Username: Won:
Peter     28
Mike      25
Kim       20

And so on... The code I have been trying is this:

var winners = (from s in dc.Scores
                       from u in dc.Users
                       where (s.result) && (s.attackinguserid == u.userid)
                       group u by s
                       into groups
                       select new
                                  {
                                      Username= groups.Key,
                                      Won= groups.Count()
                                  }
                      ).OrderByDescending(x => x.Seire).Distinct().Take(25);

        gvBestPlayers.DataSource = winners; //gvBestPlayers = gridview
        gvBestPlayers.DataBind();

But this gives me error on the DataBind(). The error: NotSupportedException was unhandled by user code. The member 'System.Web.UI.Page.Title' has no supported translation to SQL.

Answer 1

Solved it my self. Here is the working code:

var winners = (from s in dc.Scores 
               from u in dc.Users 
               where (s.result) && (s.attackinguserid == u.userid) 
               group s by u 
               into groups 
               select new 
               { 
                   Username= groups.Key.username, 
                   Won= groups.Count() 
               }).OrderByDescending(x => x.Seire).Distinct().Take(25).ToList(); 

gvBestPlayers.DataSource = winners; 
gvBestPlayers.DataBind();

Answer 2

try this..

var a = (from s in dc.Scores
                     join u in dc.Users on s.attackinguserid equals u.userid
                     let count = dc.Scores.Where(id => id.attackinguserid == u.userid ).Count()
                     where s.result == true
                     select new { Username = u.username, Won = count }).ToList().Distinct();