How to specify left join in EF?

Question

My model:

public partial class history_builds
{
    public int ID { get; set; }
    public int build { get; set; }
    public int br { get; set; }
    public int tag { get; set; }
    public string line { get; set; }
    public int rev { get; set; }
    public int user_ID { get; set; }
    public string distrib_path { get; set; }
    public string setup_path { get; set; }
    public System.DateTime build_date { get; set; }
    public string product { get; set; }
}

public partial class history_uploads
{
    public int ID { get; set; }
    public int ID_user { get; set; }
    public string Line { get; set; }
    public int Build { get; set; }
    public string Distrib { get; set; }
    public System.DateTime Time_upload { get; set; }
    public int Status { get; set; }
}

public partial class user
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int ID_group { get; set; }
}

Context:

public DbSet<history_builds> history_builds { get; set; }
public DbSet<history_uploads> history_uploads { get; set; }
public DbSet<user> users { get; set; }

I try to do left join like this Entity framework left join

var items = entities.history_builds
    .Join(
        entities.users.DefaultIfEmpty(),
        hb => hb.user_ID,
        u => u.ID,
        (hb, u) =>
            new {
                hb.distrib_path,
                hb.setup_path,
                hb.build_date,
                hb.build,
                User = (u == null ? String.Empty : u.Name),
                hb.rev
            }
    )
    .Join(entities.history_uploads.DefaultIfEmpty(),
        hb => hb.build,
        hu => hu.Build,
        (hb, hu) =>
            new HistoryBuidItem {
                Revision = hb.rev,
                Build = hb.build,
                DistribPath = hb.distrib_path,
                SetupPath = hb.setup_path,
                BuildDate = hb.build_date,
                User = hb.User,
                IsUpload = (hu == null ? true : false)
            }
    )
    .Where(x => ids.Contains(x.Revision))
    .ToList();

But it doesn't work, EF still emit inner join sql code, what is wrong?

score 15 · Answer 1 · edited May 02 '19 at 08:01

15

You should use GroupJoin for that.

Have a look at the example:

var customers = db.Customer
                  .GroupJoin(db.SpecialCustomer, c => c.ID, g => g.CustomerId, (f, b) => new { f, b })
                  .SelectMany(z => z.b.DefaultIfEmpty(), (z, g) => new { z, g });

edited May 02 '19 at 08:01

kara

3,205
4
20
34

answered Oct 17 '12 at 07:04

Asif Mushtaq

13,010
3
33
42

ChunHao Tang · Accepted Answer · 2012-10-17T08:13:04.420

14

Left Outer Join Example：

from c in table0
join o in table1 on c.sno equals o.sno into ps
from o in ps.DefaultIfEmpty()
select new { c.name, o.number}

It render SQL：

SELECT [t0].[name], [t1].[number] AS [number]
FROM [table0] AS [t0]
LEFT OUTER JOIN [table1] AS [t1] ON ([t0].[sno]) = [t1].[sno]

edited Oct 17 '12 at 08:13

answered Oct 17 '12 at 08:06

ChunHao Tang

554
3
9

score 3 · Answer 3 · answered Nov 06 '17 at 15:47

3

bit simplier version of ChunHao Tang's answer:

from c in table0
from o in table1.Where(x => c.sno == x.sno).DefaultIfEmpty()
select new { c.name, o.number }

answered Nov 06 '17 at 15:47

Tomasz Skomra

569
4
7

I find all of the LINQ join syntax options really cumbersome and unintuitive. But I think I'll start using this one, which seems the better of the bad... :) – Brian Birtle Apr 13 '23 at 05:19

How to specify left join in EF?

3 Answers3