title: Multiple outer join using Linq with 2 joins to the same table/object. Got the SQL, need the Linq to Entity
body: <p>I am trying to reproduce the following SQL query in Linq and need some help please.</p>
  
  <pre class="lang-sql prettyprint-override"><code>select
     dbo.Documents.DocId, 
     dbo.Documents.ReykerAccountRef, 
     dbo.Documents.ReykerClientId DocClientID,
     CAAs.ClientId CAAClientIDCheck,
     ClientData.FullName ClientFullName, 
     CAAs.IFAId,  
     AdvisorData.FullName AdvisorFullName
  from dbo.Documents
  left join 
    dbo.CAAs on dbo.Documents.ReykerAccountRef = dbo.CAAs.AccountRef
  left join 
    dbo.hmsProfileDatas AS ClientData
  on 
    dbo.CAAs.ClientId = ClientData.ReykerClientID
  left join 
    dbo.hmsProfileDatas AS AdvisorData
  on 
    dbo.CAAs.IFAId = AdvisorData.ReykerClientID
  </code></pre>
  
  <p>I am trying to link to the same table twice, once for a client fullname and the other for an Advisor fullname.</p>
  
  <p>The basic sql I want to produce in linq is</p>
  
  <pre class="lang-sql prettyprint-override"><code>select table1.*,table2.*,a.Fullname, b.Fullname
  from table1
  left join
     table2 on table1.t2Id = table2.Id
  left join
     table3 AS a 
  on 
     table2.t3Id1 = table3.id1
  left join
     table3 AS b 
  on 
     table2.t3Id2 = table3.id2
  </code></pre>
  
  <p>So table one is joined to table2 and table2 has 2 foreign keys (t3Id1 and t3Id2) to table3 to different fields (id1 and id2).    </p>
  
  <p>This is what I have tried following some guidance, but it's not returning anything! What's going wrong?</p>
  
  <pre class="lang-c# prettyprint-override"><code>        var results3 = from doc in DataContext.Documents
                         from caa
                             in DataContext.CAAs
                             .Where(c =&gt; c.AccountRef == doc.ReykerAccountRef)
                             .DefaultIfEmpty()
                         from cpd
                             in DataContext.hmsProfileDatas
                             .Where(pdc =&gt; pdc.ReykerClientID == caa.ClientId)
                             .DefaultIfEmpty()
                         from apd
                             in DataContext.hmsProfileDatas
                             .Where(pda =&gt; pda.ReykerClientID == caa.IFAId)
                             .DefaultIfEmpty()
                         select new DocumentInList()
                                    {
                                        DocId = doc.DocId,
                                        DocTitle = doc.DocTitle,
                                        ReykerDocumentRef = doc.ReykerDocumentRef,
                                        ReykerAccountRef = doc.ReykerAccountRef,
                                        ClientFullName = cpd.FullName,
                                        AdvisorFullName = apd.FullName,
                                        DocTypeId = doc.DocTypeId,
                                        DocTypes = doc.DocTypes,
                                        DocDate = doc.DocDate,
                                        BlobDocName = doc.BlobDocName,
                                        UploadDate = doc.UploadDate,
                                    };
  </code></pre>