c# - 如何在 Linq 中使用分组依据和计数

Question

我在尝试将以下查询从 SQL 转换为 Linq 时遇到问题，尤其是在查询的部分具有计数和分组的情况下:

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))

如有任何建议，我们将不胜感激。

Answer 1

var groups = 来自 ProjectAssociation 中的 pa 让 teamIds = User.Where(u => u.UserID == 4).Select(u => u.TeamID) 其中 teamIds.Contains(pa.TeamID) 按 pa.ProjectID 对 pa 进行分组；

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;

或者更优化:

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;

顺便说一下，我认为通过

select distinct COUNT(TeamID)

你的意思是:

select COUNT(distinct TeamID)