Tech-Today

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 Implement A Lazydatamodel With Size In Primefaces
This solution is ideal if you have a list of entities or a named queries that return a list of entities. For example let's say we have an entity user and we want to return all the users with role=x. @NamedQueries({ @NamedQuery(name = "User.listUsersByRoles",...

- Executing A Linq Query With A Bridge Table Like Aspnet_users And Aspnet_roles
Using model first approach, we add aspnet_Users, aspnet_Roles and aspnet_UsersInRoles in the edmx file. But why is aspnet_UsersInRoles missing? It's because aspnet_Users has one-to-many relationship to aspnet_Roles. To get the role of the user, we...

- Passing An Array Of Objects To Mssql Stored Procedure
Oftentimes you need to pass an array of objects (could be ids, types, etc) in an mssql stored procedure that you either need to insert into a table or use as filter. The following codes will explain the latter: Pass an array of integers: ALTER PROCEDURE...

- Linq Dynamic And Or Statements
You usually need this on search form where you have to add a condition if a field is filled or not. This is how I do it: public static List Search(SearchViewModel param) { IQueryable matches = EntityMdl.SellOutMobiles; if (!string.IsNullOrEmpty(param.Client))...

- Using In Like Operator In Entity Framework
Entity Framework is a great advancement to sql unfortunately some old keywords are not supported. But reading the documentation and google you will find a lot of work arounds. Like for example in the case of IN operator. Old select * from table where...

Tech-Today