How to Write Left Join Sql on Entity Framework
In every limitation there is always a work around. For this specific limitation on entity framework 3.5 not having a left join. This is what...
https://www.czetsuyatech.com/2011/03/c-left-join-sql-ef.html
In every limitation there is always a work around. For this specific limitation on entity framework 3.5 not having a left join. This is what I came up:
For example you want to:
SQL:
select *
from contact a
left join country c on a.contactid=c.contactid
where contactid=x
Solution 1: Successive queries (not sure how this works)
Solution 2: Inner join like
See how it works?
For example you want to:
SQL:
select *
from contact a
left join country c on a.contactid=c.contactid
where contactid=x
Solution 1: Successive queries (not sure how this works)
var query = (from a in Context.Contact join b in Context.Country on a.ContactId equals b.ContactId where a.ContactId == x select b); (from a in Context.Contact join b in Context.Country on a.ContactId equals b.ContactId where a.ContactId == x select a);
Solution 2: Inner join like
//First create a new model class; class CustomContact { public int ContactId; public string ContactName; public int CountryId; } var query = (from a in Context.Contact let l1 = (from gc in Context.Country where gc.CountryId == a.Country.CountryId select new { gc.CountryId }).FirstOrDefault() where a.ContactId == x select new CustomContact { ContactId = a.ContactId, ContactName = a.ContactName, CountryId = l1.CountryId }Solution 3: http://msdn.microsoft.com/en-us/library/bb397895.aspx
var query = from person in people join pet in pets on person equals pet.Owner into gj from subpet in gj.DefaultIfEmpty() select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };
See how it works?
1 comment
Thanks for this post, it helped me out! :)
Post a Comment