帮助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

Michael的想法是对的。我在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", };