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