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 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)
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?
How to write left join sql on entity framework How to write left join sql on entity framework Reviewed by Edward Legaspi on Thursday, March 03, 2011 Rating: 5

1 comment:

Anonymous said...

Thanks for this post, it helped me out! :)

Powered by Blogger.