Multiple outer join using Linq with 2 joins to the same table/object. Got the SQL, need the Linq to Entity

c#, entity-framework, linq, linq-to-entities, sql

Solution

I hope I understood your example correctly. Here's another simple example, that should give what you need:

    private class User
    {
        public int UserId;
        public string Name;
        public int GroupId;
        public int CollectionId;
    }

    public class Group
    {
        public int GroupId;
        public string Name;
    }

    public class Collection
    {
        public int CollectionId;
        public string Name;
    }

    static void Main()
    {
        var groups = new[] { 
            new Group { GroupId = 1, Name = "Members" },
            new Group { GroupId = 2, Name = "Administrators" } 
        };
        var collections = new[] { 
            new Collection { CollectionId = 1, Name = "Teenagers" },
            new Collection { CollectionId = 2, Name = "Seniors" } 
        };
        var users = new[] { 
            new User { UserId = 1, Name = "Ivan", GroupId = 1, CollectionId = 1 },
            new User { UserId = 2, Name = "Peter", GroupId = 1, CollectionId = 2 },
            new User { UserId = 3, Name = "Stan", GroupId = 2, CollectionId = 1 },
            new User { UserId = 4, Name = "Dan", GroupId = 2, CollectionId = 2 },
            new User { UserId = 5, Name = "Vlad", GroupId = 5, CollectionId = 2 },
            new User { UserId = 6, Name = "Greg", GroupId = 2, CollectionId = 4 },
            new User { UserId = 6, Name = "Arni", GroupId = 3, CollectionId = 3 },
        };

        var results = from u in users
                      join g in groups on u.GroupId equals g.GroupId into ug
                      from g in ug.DefaultIfEmpty()
                      join c in collections on u.CollectionId equals c.CollectionId into uc
                      from c in uc.DefaultIfEmpty()
                      select new { 
                          UserName = u.Name, 
                          GroupName = g != null ? g.Name : "<No group>",
                          CollectionName = c != null ? c.Name : "<No collection>"
                      };
    }

It produces two join over one table to get data from other two tables. Here's the output:

Ivan    Members         Teenagers
Peter   Members         Seniors
Stan    Administrators  Teenagers
Dan     Administrators  Seniors
Vlad    <No group>      Seniors
Greg    Administrators  <No collection>
Arni    <No group>      <No collection>

Problem

I am trying to reproduce the following SQL query in Linq and need some help please. ``` 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 ``` I am trying to link to the same table twice, once for a client fullname and the other for an Advisor fullname. The basic sql I want to produce in linq is ``` 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 ``` So table one is joined to table2 and table2 has 2 foreign keys (t3Id1 and t3Id2) to table3 to different fields (id1 and id2). This is what I have tried following some guidance, but it's not returning anything! What's going wrong? ``` var results3 = from doc in DataContext.Documents from caa in DataContext.CAAs .Where(c => c.AccountRef == doc.ReykerAccountRef) .DefaultIfEmpty() from cpd in DataContext.hmsProfileDatas .Where(pdc => pdc.ReykerClientID == caa.ClientId) .DefaultIfEmpty() from apd in DataContext.hmsProfileDatas .Where(pda => 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, }; ```

Original source