Linq 左连接帮助

Question

有人可以帮我解决这个 linq 查询吗？我基本上希望所有玩家都在查询中返回，无论他们是否在评级表中。如果他们确实有评级显示在查询中。此查询的问题在于，如果法官 x 输入了玩家 a 的评分，然后使用法官 y 进行查询，您将不会在结果中得到玩家 a。我想我明白为什么，但可以弄清楚语法。

 var players =
        from p in dc.Players
        join r in dc.Ratings on p.PlayerId equals r.PlayerId into ps
        from r in ps.DefaultIfEmpty()
        where r.JudgeId == Convert.ToInt32(JudgeId) || r.JudgeId == null
        orderby p.PlayerName
        select new
        {
            PlayerName = p.PlayerName,
            PlayerId = p.PlayerId,
            Passing = r != null ? r.Passing : 0,
            Setting = r != null ? r.Setting : 0,
            Serving = r != null ? r.Serving : 0,
            Blocking = r != null ? r.Blocking : 0,
            Effort = r != null ? r.Effort : 0,
            Quickness = r != null ? r.Quickness : 0,
            Hitting = r != null ? r.Hitting : 0,
            PositionKnowledge = r != null ? r.PositionKnowledge : 0,
            Total = r != null ? Convert.ToString(r.Passing + r.Setting + r.Serving + r.Blocking + r.Effort + r.Quickness + r.Hitting + r.PositionKnowledge): "You have not rated this person",
        };

Answer 1

迈克尔的想法是正确的。我在 LINQPad 中工作，发现在多个条件下连接的语法比使用 && 稍微复杂一些。不过，运营商。

var judgeId = Convert.ToInt32(JudgeId); // Can't do this inside the anonymous type declartion, so do it first
var players =
        from p in dc.Players
        join r in dc.Ratings on new {p.PlayerId, JudgeId = judgeId } equals new {r.PlayerId, r.JudgeId} into ps
        from r in ps.DefaultIfEmpty()
        orderby p.PlayerName
        select new
        {
            PlayerName = p.PlayerName,
            PlayerId = p.PlayerId,
            Passing = r != null ? r.Passing : 0,
            Setting = r != null ? r.Setting : 0,
            Serving = r != null ? r.Serving : 0,
            Blocking = r != null ? r.Blocking : 0,
            Effort = r != null ? r.Effort : 0,
            Quickness = r != null ? r.Quickness : 0,
            Hitting = r != null ? r.Hitting : 0,
            PositionKnowledge = r != null ? r.PositionKnowledge : 0,
            Total = r != null ? Convert.ToString(r.Passing + r.Setting + r.Serving + r.Blocking + r.Effort + r.Quickness + r.Hitting + r.PositionKnowledge): "You have not rated this person",
        };

Answer 2

我相信你只需要将你的 where 子句移到连接中，这样就可以代替：

“给我所有玩家，以及他们拥有的任何评级，然后过滤这些评级，而不是通过判断”，

你会得到

“给我所有玩家，并且评委对他们的任何评分”。

var players =
    from p in dc.Players
    join r in dc.Ratings on p.PlayerId equals r.PlayerId && r.JudgeId == Convert.ToInt32(JudgeId) into ps
    from r in ps.DefaultIfEmpty()
    orderby p.PlayerName
    select new
    {
        PlayerName = p.PlayerName,
        PlayerId = p.PlayerId,
        Passing = r != null ? r.Passing : 0,
        Setting = r != null ? r.Setting : 0,
        Serving = r != null ? r.Serving : 0,
        Blocking = r != null ? r.Blocking : 0,
        Effort = r != null ? r.Effort : 0,
        Quickness = r != null ? r.Quickness : 0,
        Hitting = r != null ? r.Hitting : 0,
        PositionKnowledge = r != null ? r.PositionKnowledge : 0,
        Total = r != null ? Convert.ToString(r.Passing + r.Setting + r.Serving + r.Blocking + r.Effort + r.Quickness + r.Hitting + r.PositionKnowledge): "You have not rated this person",
    };

Answer 3

这两个建议不太有效，并且在 Visual Studio 中生成了编译错误。但他们确实激发了一个想法，而且这似乎是可行的。

  int judgeId = Convert.ToInt32(Session["logged"]); 
        var filtered = from r in dc.Ratings
                       where r.JudgeId == judgeId
                       select r;
        var players = from p in dc.Players join
                      r in filtered on p.PlayerId equals r.PlayerId
                      into ps from r in ps.DefaultIfEmpty() 
                      orderby p.PlayerName 
                      select new { PlayerName = p.PlayerName, PlayerId = p.PlayerId, Passing = r != null ? r.Passing : 0, Setting = r != null ? r.Setting : 0, Serving = r != null ? r.Serving : 0, Blocking = r != null ? r.Blocking : 0, Effort = r != null ? r.Effort : 0, Quickness = r != null ? r.Quickness : 0, Hitting = r != null ? r.Hitting : 0, PositionKnowledge = r != null ? r.PositionKnowledge : 0, Total = r != null ? Convert.ToString(r.Passing + r.Setting + r.Serving + r.Blocking + r.Effort + r.Quickness + r.Hitting + r.PositionKnowledge) : "You have not rated this person", };