select TeamName, [Description], COUNT(u.UserId)
from Team t
left outer join [User] u on u.TeamId=t.TeamId
group by TeamName, Description, UserId
and here i have so far but cant able to do that.please help
var countUser = (from t in db.Teams
join u in db.Users on u.TeamId equals t.TeamId
group TeamName, Description, UserId by select
new
{
u.UserId
}).Count();
This should do it:
Teams.Join(Users.DefaultIfEmpty().
t => t.TeamId,
u => u.TeamId,
(t, u) => new { t.TeamName, t.Description, UserId = u == null ? null:(int?)u.UserId })
.GroupBy(x => x)
.Select(g => new { g.Key.TeamName, g.Key.Description, Count = g.Count() });
RePierre I'm going to steal part of your answer (+1) because I think I understand what OP is talking about, though the question text does not convey it.
You could do something like this:
// Model class for View
public class UsersPerTeamCount
{
public string TeamName { get; set; }
public string Description { get; set; }
public int Count { get; set; }
}
// ...
public ActionResult PlayersPerTeam()
{
var model = from t in db.Teams
join u in db.Users on t.TeamId equals u.TeamId into joinedRecords
select new UsersPerTeamCount()
{
Name = t.TeamName,
Description = t.Description,
PlayerCount = joinedRecords.Count()
};
return View(model);
}
As far as in OPs comment "please try to write as like..." that's just a difference in syntax, it doesn't really matter which way you write it - either fluent vs query syntax (at least i think it's called query syntax)
