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
Question
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,
};
Accepted Answer
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>
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
