CODEWITHSUNDEEP
linq
Sketcher
Sketcher

Reputation: 25

Issue with Group by clause in linq to sql

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 .

Here is the sql query

select count(USERID), d.DEPTNAME from USERS u
join department d on u.DEPTID = d.DEPTID
group by u.DEPTID, d.DEPTNAME

Upvotes: 0

Views: 39

Answers (1)

Jeff Mercado
Jeff Mercado

Reputation: 134601

A more direct translation would be like this:

var query =
    from u in db.Users
    join d in db.Departments on u.DeptId equals d.DeptId
    group d by new { d.DeptId, d.DeptName } into g
    select new
    {
        g.Key.DeptName,
        Count = g.Count(),
    };

Though I think it would be better off written like this:

// looks like we're counting users in each department
var query =
    from d in db.Departments
    select new
    {
        d.DeptName,
        Count = db.Users.Count(u => u.DeptId == d.DeptId),
    };

Upvotes: 1

Related Questions