Try this:
1)
var qry1 = from d in department
select new {
deptname = d.deptname,
ucount = (from u in users where u.deptid == d.deptid select u).Count()
};
or
2)
var qry2 = from u in users group u by u.deptid into grp
select new { Deptid = grp.Key, UsersCount = grp.Count() };
or
3)
var qry3 = from u in usrs
join d in depts on u.DeptId equals d.DeptId
group d by d.DeptName into grp
select new {
DeptName = grp.Key,
UsersCount = grp.Count()
};
For further information, please seee:
101 LINQ Samples[
^]
[EDIT]
A complete
LinqPad[
^] example:
void Main()
{
List<Department> depts = new List<Department>
{
new Department(1, "A"),
new Department(2, "B"),
new Department(3, "C")
};
List<User> usrs = new List<User>
{
new User(1), new User(1),
new User(2), new User(2),
new User(2), new User(1),
new User(1), new User(3),
new User(3), new User(3),
new User(3), new User(3),
new User(3), new User(3),
new User(3), new User(3),
new User(3)
};
var qry1 = from d in depts
select new {
DeptName = d.DeptName,
UsersCount = (from u in usrs where u.DeptId == d.DeptId select u).Count()
};
qry1.Dump();
var qry2 = from u in usrs group u by u.DeptId into grp
select new { DeptId = grp.Key, UsersCount = grp.Count()};
qry2.Dump();
var qry3 = from u in usrs
join d in depts on u.DeptId equals d.DeptId
group d by d.DeptName into grp
select new {
DeptName = grp.Key,
UsersCount = grp.Count()
};
qry3.Dump();
}
class Department
{
private int did =0;
private string dna = string.Empty;
public Department(int _did, string _dna)
{
did = _did;
dna = _dna;
}
public int DeptId
{
get{return did;}
set{did = value;}
}
public string DeptName
{
get{return dna;}
set{dna = value;}
}
}
class User
{
private int did = 0;
public User(int _did)
{
did = _did;
}
public int DeptId
{
get{return did;}
set{did = value;}
}
}
Result of
qry1
DeptName UsersCount
A 4
B 3
C 10
and
qry2
DeptId UsersCount
1 4
2 3
3 10
and
qry3
DeptName UsersCount
A 4
B 3
C 10