Help with Linq left join

Can someone help me out with this linq query, I basically want all the players to be returned in the query regardless if they are in the ratings table. If they do have ratings show in the query. The problem with this query is that if judge x entered a rating for player a then querying with judge y you will not get player a in the results. I think i understand why but can figure out the syntax.

 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,
      开发者_C百科      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",
        };

---

Michael has the right idea. I was working in LINQPad and found that the syntax to join on multiple conditions is a little more complex than using the && operator, though.

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",
        };

---

I believe you just need to move your where clause into the join, so that instead of this:

"Give me all players, and any ratings they have, then filter those ratings not by judge a"

you get

"Give me all players, and any ratings they have by judge a".

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",
    };

---

Those two suggestions didn't quite work and generated compile errors in visual studio. But they did spark an idea and this is what seems to work.

  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", };