score:3

Accepted answer

Given that a badge only really has a badge ID and a user, it sounds like you just need to get the badge ID and the count of that badge for the use - which means just getting the key for the group:

var q = db.tblBadgeUsers
    .Where(c => c.UserID == UserID)
    .GroupBy(c => c.BadgeID)
    .Select(c => new { BadgeCount = c.Count(), BadgeId = c.Key });

If there's more information on each badge, you might want to do:

var q = db.tblBadgeUsers
    .Where(c => c.UserID == UserID)
    .GroupBy(c => c.BadgeID)
    .Select(c => new { BadgeCount = c.Count(), BadgeId = c.Key, Badges = c });

Then you could do:

foreach (var badgeType in q)
{
    Console.WriteLine("{0}: {1}", badgeType.BadgeId, badgeType.BadgeCount);
    foreach (var badge in q.Badges)
    {
        // Deal with the badge information
    }
}

Related Articles