title: How to perform "complex" join using Linq
body: <p>I need to join two objects (tables) A and B. For any A there can be zero to many B's. The query needs the return one row per A. </p>
  
  <p>The B's I want to order before the join to be able to select the needed row from B's following a certain condition. Say B has a column Type. If there is a Type 1 then that's the B I need, if not: Type 2 must be selected, etc. </p>
  
  <p>Now I think about it, I am not sure how I would to this even in T-sql. 
  I think something like this: </p>
  
  <pre><code>SELECT A.*
  FROM   A LEFT JOIN (
    SELECT * FROM B AS B1 WHERE B1.Type = (SELECT TOP 1 B2.Type FROM B AS B2  
                                           WHERE B2.JoinID = B1.JoinID
                                           ORDER BY B2.Type )
  ) AS B ON B.JoinID = A.JoinID
  </code></pre>
  
  <p>[edit]</p>
  
  <p>With the answer of sgtz I've tried to make it work. If have to make an additional step because the field I want to order by is not present. I add this field in step 1, in step 2 I make a selection of the keys and join everything in step 3, but there I receive an error "The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'GroupJoin'." on join "join a in adressen1 on new { b.TopRelatieID..."</p>
  
  <pre><code>    var adressen1 = from a in db.Adres
                      select new
                      {
                          RelatieAdres = a,
                          Sortering = (int)(a.AdresType.Code == codeVestAdres ?
                                              1 : a.AdresType.Code == codePostAdres ? 
                                                 2 : (100 + (int)a.AdresType.Code.ToCharArray()[0]))
                      };
  
      var adressen2 = from b in adressen1
                      group b by new { RelatieID = b.RelatieAdres.RelatieID } into p
                      let TopAdresType = p.Min(at =&gt; at.Sortering)
                      select new { TopRelatieID = p.Key.RelatieID, TopAdresType };
  
      var q = from k in db.Klants
              join b in adressen2 on k.RelatieID equals b.TopRelatieID into b_join
              from b in b_join.DefaultIfEmpty()
              join a in adressen1 on new { b.TopRelatieID, b.TopAdresType } equals new { a.RelatieAdres.RelatieID, a.Sortering } into a_join
              from a in a_join.DefaultIfEmpty()
  </code></pre>