How to implement this sql in entity framework?

Question

The SQL is:

select 
   a.Id, a.Name,a. ParentId,
   sum(a.departmentCount),
   sum(a.userCount) 
from
   (select 
       d.*,
       0 as departmentCount,
       count(u.Id) as userCount
    from 
       dbo.DepartmentSet d
    left join 
       dbo.UserSet u on d.Id = u.DepartmentId
    where 
       d.ParentId is null
    group by 
       d.Id, d.Name, d.ParentId 

    union all

    select 
       d.*,
       count(d2.Id) as departmentCount,
       0 as userCount
    from 
       dbo.DepartmentSet d
    left join 
       dbo.DepartmentSet d2 on d.Id = d2.ParentId
    where 
       d.ParentId is null
    group by 
       d.Id, d.Name, d.ParentId) a
group by 
    a.Id, a.Name, a.ParentId

Answer 1

Here is an article that shows how to do subqueries which should get your code going

how to do subquery in LINQ