Linq a SQL – Tener y Agrupar por

Tengo esta consulta a continuación funcionando bien. Sin embargo, quiero implementarlo usando Linq.

select u.ID, u.NAME from Task t join BuildingUser bu ON bu.ID_BUILDING = t.ID_BUILDING join [User] u ON u.ID = bu.ID_USER where t.ID IN (2,9) AND u.ID != t.ID_USER group by u.id, u.name having count(bu.ID_BUILDING) = (SELECT COUNT(t2.ID_BUILDING) FROM Task t2 WHERE t2.ID IN (2,9)) 

No sé cómo agrupar y usar la cláusula Having al mismo time.

Puedes probar algo como esto:

 var ids = new[] { 2, 9 }; var results = from t in db.Tasks join bu in db.BuildingUsers on t.ID_BUILDING equals bu.ID_BUILDING group bu by bu.ID_BUILDING into bg join u in db.Users on bg.Key equals u.ID where ids.Contains(t.ID) && u.ID != t.ID_USER group u by new { u.ID, u.NAME } into g where bg.Count() == db.Tasks.Count(t2 => ids.Contains(t2.ID)) select g.Key; 

O si tiene las properties de navigation configuradas correctamente, puede intentar esto:

 var ids = new[] { 2, 9 }; var results = from t in db.Tasks.Where(x => ids.Contains(x.ID)) from u in t.BuildingUsers.SelectMany(bu => bu.Users) .Where(x => x.ID != t.ID_USER) group u by new { u.ID, u.NAME } into g where t.BuildingUsers.Count() == db.Tasks.Count(x => ids.Contains(x.ID)) select g.Key;