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?

ChunHao Tang · Accepted Answer · 2012-10-17 08:13:04Z

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]

kara · Accepted Answer · 2019-05-02 08:01:53Z

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 });

Tomasz Skomra · Accepted Answer · 2017-11-06 15:47:00Z

5

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 6, 2017 at 15:47

Tomasz Skomra

6696 silver badges7 bronze badges

1 Comment

Brian Birtle Over a year ago

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... :)

Collectives™ on Stack Overflow

How to specify left join in EF?

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related