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?

1 Comments

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

    ReplyDelete

Post a Comment

Post a Comment

Previous Post Next Post
NERV Open Source

Building production Spring Boot systems?

Explore NERV — open-source Java libraries for audit trails, persistence, exception handling, and reliable event-driven architecture.

Explore NERV on GitHub →