LINQ Lamba连接，带计数

Question

我想要得到没有当前团队的所有团队，以及有4个或更多玩家的所有团队。我尝试编写这个linq lambda查询：

teams = connection.Team 
    .Join(connection.Player,
        t => t.ID,
        p => p.IDTeam,
        (t, p) => new { Team = t, Player = p })
    .Where(tp => tp.Player.IDTeam == tp.Team.ID
        && tp.Team.ID != team.ID
        && tp.Team.IsVisible == true
        && !tp.Team.DeleteDate.HasValue)
    .Select(tp => tp.Team)
    .ToList();

但我不能指望在什么情况下有多少球员有球队。怎么办？对于info，SQL中的查询是什么？感谢您的帮助！

编辑:根据需要编辑类(从DBFirst生成)：

​

Answer 1

基于以上类，您可以扩展Team类并添加Players导航属性。

添加导航属性时，请确保球队和球员表之间的数据库关系已就绪。此外，如果需要，请配置您的DbContext。

public class Team
{
    //...other properties
    public virtual ICollection<Player> Players { get; set; }
}

当您添加导航属性时，实现您的需求将是微不足道的：

connection.Teams
          .Where(t => t.ID != team.ID && t.IsVisible == true && !t.DeleteDate.HasValue && t.Players.Count() >= 4)

Answer 2

尝试GroupBy()：

var teams = (from tp in connection.Team
   join p in connection.Team on tp.Player.IDTeam equals p.Team.ID
   select new { Team = tp, Player = p })
   .Where(tp =>  tp.Team.IsVisible == true && !tp.Team.DeleteDate.HasValue)
   .GroupBy(x => x.Team.ID)
   .Where(x => x.Count >= 4)
   .ToList();

Answer 3

试试这个：

teams = connection.Team 
.Join(connection.Player,
    t => t.ID,
    p => p.IDTeam,
    (t, p) => new { Team = t, Player = p })
.Where(tp => tp.Player.IDTeam == tp.Team.ID
    && tp.Team.ID != team.ID
    && tp.Team.IsVisible == true
    && !tp.Team.DeleteDate.HasValue)
.Select(tp => tp.Team)
.ToList().GroupBy(i=>i.ID).Where(i=>i.Count()>=4);