How to use group by and having count in Linq

Question

I am having trouble trying to convert the following query from SQL to Linq, in particular with the having count and group by parts of the query:

select ProjectID
from ProjectAssociation

where TeamID in ( select TeamID 
                  from [User]
                  where UserID in (4))
group by ProjectID
having COUNT(TeamID) = (select distinct COUNT(TeamID)
                        from [User]
                        where UserID in (4))

Any advice on how to do so would be much appreciated.

Answer 1

var groups = from pa in ProjectAssociation let teamIds = User.Where(u => u.UserID == 4).Select(u => u.TeamID) where teamIds.Contains(pa.TeamID) group pa by pa.ProjectID;

var result = from g in groups
   let count = User.Where(u => u.UserID == 4).Select(u => u.TeamID).Distinct().Count()
   where g.Count() == count
   select g.Key;

Or maybe more optimal:

var teamIds = User.Where(u => u.UserID == 4).Select(u => u.TeamID).AsEnumerable();
var groups = ProjectAssociation.Where(pa => teamIds.Contains(pa.TeamID)
   .GroupBy(pa => pa.ProjectID);
var result = from g in groups
       let count = teamIds.Distinct().Count()
       where g.Count() == count
       select g.Key;

By the way, i think that by

select distinct COUNT(TeamID)

you meant:

select COUNT(distinct TeamID)

Answer 2

There is a tool (cheap) that will convert queries like this for you. It's called Linqer. I own a copy and have found that it's able to convert event the most complex of queries. The URL is http://www.sqltolinq.com/

It's not free, but it's pretty inexpensive and has a 30 day trial.