Reputation: 61773
I have two tables:
tblBadge
-------
ID
Name
tblBadgeUsers
-------
BadgeID
UserID
A user can have many badges, all the relationships are set up properly in the SQL database.
I'm trying to return a list of all the badges a user has, along with the total number of those badges. This is about as far as I can get, I'm getting pretty confused. I want to return the tblBadge data along with an extra column showing the total of that badge awarded.
This doesn't work but is my attempt:
var q = db.tblBadgeUsers
.Where(c => c.UserID == UserID)
.GroupBy(c => c.BadgeID)
.Select(c => new { BadgeCount = c.Count(), Record = c.tblBadge });
Upvotes: 2
Views: 171
Reputation: 1504122
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
}
}
Upvotes: 3