Click here to Skip to main content
15,992,983 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to make this query in linq to sql. Please help. I am new to linq and having problem to with the group by clause. PFA the image is showing the query from the sql server Here is the sql query


SQL
select count(USERID), d.DEPTNAME from USERS u
join department d on u.DEPTID = d.DEPTID
group by u.DEPTID, d.DEPTNAME
Posted
Updated 14-Feb-15 22:03pm
v2

1 solution

Try this:
1)
C#
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)
C#
var qry2 = from u in users group u by u.deptid into grp
    select new { Deptid = grp.Key, UsersCount = grp.Count() };


or
3)
C#
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:

C#
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();
	
}

// Define other methods and classes here
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
 
Share this answer
 
v3
Comments
Thomas Daniels 15-Feb-15 6:59am    
+5!
Maciej Los 15-Feb-15 7:00am    
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900