C#: Left outer joins with LINQ

I always considered Left Outer Join in LINQ to be complex until today when I had to use it in my application. I googled and the first result gave a very nice explanation. The only difference between ordinary joins (inner joins) and left joins in LINQ is the use of “join into” and “DefaultIfEmpty()” expressions.

Consider this very simple query (Assuming a scenario that not all the TimesheetLines are associated with a Job)

Select TL.EntryDate, TL.Hours, J.JobName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo

A LINQ query using inner join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo
    where tl.ResourceNo == resourceNo

    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

And a LINQ query performing left join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo into tl_j
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

Notice that the only difference is the use of “into” with the join statement followed by reselecting the result using “DefaultIfEmpty()” expression. And here’s the generated SQL for the above LINQ expression.

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job]
FROM [dbo].[TimeSheetLine] AS [t0]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
WHERE [t0].[ResourceNo] = @p0

Another LINQ version which is more compact is:

var lines =
    from tl in db.TimeSheetLines
    from j in db.Jobs.Where(j=>j.JobNo == tl.JobNo).DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

Similarly, this concept can be expanded for multiple left joins. Assuming that a TimeSheetLine will either have a JobNo or an IndirectCode, consider this SQL query:

Select TL.EntryDate, TL.Hours, J.JobName, I.IndirectName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo
Left Join Indirects I on TL.IndirectCode=I.IndirectCode

The equivalent LINQ query is:

var lines =
    from tl in db.TimeSheetLines
    join j in db.Jobs      on tl.JobNo        equals j.JobNo         into tl_j
    join i in db.Indirects on tl.IndirectCode equals i.IndirectCode  into tl_i
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    from i in tl_i.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName,
        Indirect = i.IndirectName,
    };

And the generated SQL is:

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job], [t2].[IndirectName] As [Indirect]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
LEFT OUTER JOIN [dbo].[Indirects] AS [t2] ON [t0].[IndirectCode] = [t2].[IndirectCode]
WHERE [t0].[ResourceNo] = @p0

That’s all, left outer joins in LINQ are as easy as in T-SQL. Happy joining.

Update:
Notice that this post describes the approach to perform a Left Outer Join in LINQ To SQL as well as Entity Framework (version 4). The same is not true for Entity Framework version 3.5 since it does not support the DefaultIfEmpty keyword. To perform Left Outer Joins with Entity Framework 3.5, we need to create appropriate relationships (e.g 0..1 to 0..Many) in our Entity Model and they will be automatically translated into TSQL’s Left Join clause.

About these ads

35 Responses to “C#: Left outer joins with LINQ”

  1. micaleel Says:

    Cool. This came in really handy.

  2. danny rosales Says:

    My child table will not display the foreign key(in intellisense). Can you tell me why???

    Thanks,

    Danny Rosales

  3. Syed Mehroz Alam Says:

    @danny

    Are you using LINQ To SQL or Entity Framework? If you are using EF3.5, you cannot use this approach since EF3.5 does not support DefaultIfEmpty(). Do note that to have an outer join in EF, you need to set up the appropriate relation multiplicity and EF will handle it automatically with a Left outer join.

    Hope that makes sense.

  4. Aashish Khandelwal Says:

    Amazing work. i was trying to perform the same task since a month.Now i m done with task.

    Thanxxxx a lot

  5. A. Villamor Says:

    Hi,

    I’m into LINQ to DataSets in C#.
    Your examples are very very clear. However, I have one thing that I don’t understand yet.

    From your sample:

    var lines =
    from tl in db.TimeSheetLines
    join j in db.Jobs on tl.JobNo equals j.JobNo into tl_j
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    select new
    {
    EntryDate = tl.EntryDate,
    Hours = tl.Hours,
    Job = j.JobName
    };

    If I do this kind of pattern in a LINQ to Dataset concept, I can’t seem to access the j.jobname. What could I be missing?

  6. Syed Mehroz Alam Says:

    Hi A. Villamor,

    The syntax should be same for LINQ To Datasets as well. Can you post your query?

  7. Rama Ranjan Jena Says:

    hi
    I have written a linq query after seeing your example.
    I am getting a error for the DefaultIfEmpty method.
    What will be the problem.

    Query is

    var roleQuery =
    //from s in
    // (
    from r in
    (
    from cs in ctx.Roles
    where cs.MaxAllowed != null && cs.RolesType == “MedOp”
    select new { cs.Id, cs.MaxAllowed, cs.Name }
    )
    join contsdy in
    (
    from cs in ctx.Contact_Studies
    where cs.Studies.Id == studyID && cs.ContType == “MedOp”
    group cs by cs.Roles.Id into g
    select new { RoleId = g.Key, RoleCount = g.Count() }
    )
    on r.Id equals contsdy.RoleId into Res
    from contsdy in Res.DefaultIfEmpty()

    select new
    {
    Name = r.Name,
    ID = r.Id,
    Max = r.MaxAllowed,
    Row = contsdy.RoleCount

    };

    Error:- LINQ to Entities does not recognize the method ‘System.Collections.Generic.IEnumerable`1[f__AnonymousType5`2[System.Int32,System.Int32]] DefaultIfEmpty[f__AnonymousType5`2](System.Collections.Generic.IEnumerable`1[f__AnonymousType5`2[System.Int32,System.Int32]])’ method, and this method cannot be translated into a store expression.

  8. Syed Mehroz Alam Says:

    Rama,

    It looks you are using Entity Framework 3.5. Unfortunately, EF v1 does not support DefaultIfEmpty and you need to shape your query using the navigational properties.

    Good luck!

  9. Rama Ranjan Jena Says:

    hi, Syed
    iam unable find any solution ..
    can u provide me some example

  10. Syed Mehroz Alam Says:

    Rama,

    Can you post your problem at MSDN forum for Entity Framework, and get the advice of expert community members there?

  11. Darren Says:

    Hi,

    great help thanks!

    Just one question though. I have to join on the same table 4 times (to get parent values).

    However the values in the subseqent joins are all the same.
    I’ve obviously missed out something stupid but, can you see what I have done wrong?

    from t in TblLocations
    join p1locs in TblLocations on t.LngLocationParent equals p1locs.LngID into join1
    join p2locs in TblLocations on t.LngLocationParent equals p2locs.LngID into join2
    join p3locs in TblLocations on t.LngLocationParent equals p3locs.LngID into join3
    join p4locs in TblLocations on t.LngLocationParent equals p4locs.LngID into join4

    where t.StrLocationKeyword.StartsWith(“RG270rp”)
    where t.LngLocationType >= 3 //restrict to sub country level
    where t.FltCenterPointXMeters != null //ok only (osgb field only populated for gb)
    orderby t.StrLocationName

    from p1locs in join1.DefaultIfEmpty()
    from p2locs in join2.DefaultIfEmpty()
    from p3locs in join3.DefaultIfEmpty()
    from p4locs in join4.DefaultIfEmpty()

    select p2locs

  12. Darren Says:

    Actually think I sorted it:

    from t in TblLocations
    join p1locs in TblLocations on t.LngLocationParent equals p1locs.LngID into join1
    from p1locs in join1.DefaultIfEmpty()
    join p2locs in TblLocations on p1locs.LngLocationParent equals p2locs.LngID into join2
    from p2locs in join2.DefaultIfEmpty()
    join p3locs in TblLocations on p2locs.LngLocationParent equals p3locs.LngID into join3
    from p3locs in join3.DefaultIfEmpty()
    join p4locs in TblLocations on p3locs.LngLocationParent equals p4locs.LngID into join4
    from p4locs in join4.DefaultIfEmpty()

    where t.StrLocationKeyword.StartsWith(“RG270rp”)
    where t.LngLocationType >= 3 //restrict to sub country level
    where t.FltCenterPointXMeters != null //ok only (osgb field only populated for gb)
    orderby t.StrLocationName

    select p2locs

  13. Udayan Says:

    This came so handy, specially the one with the lambda operator. This solved a lot of my problems

  14. Rinaldi Says:

    Hello. I wondering how to configure the model in entity framework 3.5 to be able to do left joins.

    Thank you very much.

  15. Matthew Graybosch Says:

    This post came in handy this morning. Thanks.

  16. Phani Says:

    When this Linq GroupJoin transmitted as LEFT OUTER JOIN with one join condition, but If I use two join conditions, then it transmits INNER JOIN condition, did you experience it?
    Please let me know, if any findings..

  17. Linquisitor Says:

    var query = from fs in DSR.Tables["FuelSales"].AsEnumerable()
    join fsdept in DSR.Tables["Department"].AsEnumerable()
    on fs.Field(“FuelSales_ID”) equals fsdept.Field(“FuelSales_ID”)
    join fscat in DSR.Tables["Category"].AsEnumerable()
    on fsdept.Field(“Department_ID”) equals fscat.Field(“Department_ID”)
    join fsfitm in DSR.Tables["FuelItem"].AsEnumerable()
    on fscat.Field(“category_ID”) equals fsfitm.Field(“category_ID”)
    join cot in DSR.Tables["commodities"].AsEnumerable()
    on fsfitm.Field(“extID”) equals cot.Field(“productid”) into lppquery
    where fsdept.Field(“FuelSales_ID”) != null
    from lpq in lppquery.DefaultIfEmpty()
    select new
    {
    fuelExtID = fsfitm.Field(“extID”)
    ,
    retail = fsfitm.Field(“retail”)
    ,
    retailAmt = fsfitm.Field(“retailAmt”)
    ,
    closingPumpGal = fsfitm.Field(“closingPumpGal”)
    ,
    closingPumpRetail = fsfitm.Field(“ClosingPumpRetail”)
    ,
    commid = cot.Field(“commodityid”)
    };

    in the above query I get an error in the last item of the select, saying “cot” in the current context.. what is wrong with this?

  18. Blake Rogers Says:

    Very nice post. Thank you for taking the time to write it.

  19. ricardo Says:

    Great post ! Thank you for your explanation.

    Regards,
    ricardo.

  20. Joining DataTables in LINQ | SoftArtisans, Blogged Joining DataTables in LINQ | The Cathedral and the Bizarre Says:

    [...] next problem was changing the kind of JOIN that LINQ performed. For that issue, I found a solution here. Now my method looked like [...]

  21. Pratz Says:

    Well explained! Came really handy when needed. Thanks and keep it up!

  22. TBossAZ Says:

    Can someone explain how you can do an outer join on one table with multiple conditions (two or more fields)?

  23. Shalu Says:

    Thanks. It helped a lot!

  24. Shahan Says:

    Hi,

    My Query is something like this

    var myEvents = from ca in context.CallActivity
    join s in context.Services on ca.ServiceID equals s.ServiceID into ca_s
    join u in context.Users on ca.UserId equals u.UserID into ca_u
    join p in context.ServParams on
    new { ca.ServiceID, “BadLostTime”} equals new { p.ServiceID, p.ParName} into ca_u

    from s in ca_s.DefaultIfEmpty()
    from u in ca_u.DefaultIfEmpty()
    from p in ca_p.DefaultIfEmpty()

    select new { p.ParName, ca.ServiceID };

    But i m getting the following error

    Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

    It is because I want to match the value BadLostTime with column p.ParName.

    The query need to simulate

    FROM (.AGENT_CALLACTIVITY ca )
    LEFT OUTER JOIN .AGENT_SERVICES s on (ca.ServiceId = s.ServiceId)
    LEFT OUTER JOIN .AGENT_USERS u on (ca.UserId = u.UserId)
    LEFT OUTER JOIN .AGENT_SERVPARAMS p on (ca.ServiceId = p.ServiceId AND p.parName = ‘BadLostTime’)

    Can you tell me where am I making the mistake please

  25. Shahan Says:

    ok fixed it. Thanks great post

    string badLostTime = “BadLostTime”;

    var myEvents = from ca in context.CallActivity
    join s in context.Services on ca.ServiceID equals s.ServiceID into ca_s
    join u in context.Users on ca.UserId equals u.UserID into ca_u
    join p in context.ServParams on new { ServiceID = ca.ServiceID, ParData = badLostTime } equals new { ServiceID = (int?)p.ServiceID, ParData = p.ParData } into ca_p

    from s in ca_s.DefaultIfEmpty()
    from u in ca_u.DefaultIfEmpty()
    from p in ca_p.DefaultIfEmpty()

  26. BoB Says:

    thanks, you save me some hours.

  27. Christiaan Knaap Says:

    Thanks, very useful!

  28. Anonymous Says:

    I every time spent my half an hour to read this blog’s content all the time along with a mug of coffee.

  29. Teeth Whitening at home baking Soda Says:

    I don’t understand all the numbers. Shall I use lemon, perxide, oil, or what? What is the best? Thanks for your info but waht a little a lot more.

  30. http://www.xtrememagazine.com Says:

    I enjoy what you guys tend to be up too. This sort of clever work and coverage!
    Keep up the terrific works guys I’ve included you guys to our blogroll.

  31. Dunwan Says:

    +1

  32. sex tips Says:

    It’s very easy to find out any matter on web as compared to books, as
    I found this article at this site.

  33. Insurance Group 16 Says:

    I was excited to discover this page. I want to to thank you for ones time
    for this wonderful read!! I definitely loved every little bit of it and I
    have you bookmarked to see new information in your website.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 55 other followers

%d bloggers like this: