4

I have tableA and tableB.
I would like to perform left join using lambda expression. This is the equal sql statement:

SELECT *
FROM tableA A
    LEFT JOIN tableB B ON A.userId=B.userId

How can I do that using lambda expression?

Naor
  • 23,465
  • 48
  • 152
  • 268
  • possible duplicate of [How do you perform a left outer join using linq extension methods](http://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods) – Ladislav Mrnka Jul 12 '11 at 16:03

1 Answers1

5

It's usually an error to use an explicit join in LINQ to Entities.

Instead, use the navigation properties:

var q = Context.TableAs.Select(a => new { a.Foo, a.TableB.Bar });

LINQ to Entities will coalesce null references. So if a.TableB is null for some record in TableAs, then a.TableB.Bar will return null instead of giving you a null reference exception. So it behaves like a SQL LEFT JOIN

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • @Naor: Usually, the right thing to do is to add them. You should use explicit `joins` mostly only if you *can't* add them for some reason. – Craig Stuntz Jul 12 '11 at 15:16
  • @Naor: Like I said, mostly, you don't. You haven't yet convinced me that you have a good reason to omit navigations. Navigations are *the right way* to relate entities. – Craig Stuntz Jul 12 '11 at 16:12
  • @Craig Stuntz: What about the desire to learn? How can I do left joins in lambda syntax? – Naor Jul 12 '11 at 16:42
  • @Naor: That's a different question. You asked how to do it with L2E. The *right* way to do it in L2E is to use navigations. You want to do it in L2O, then you can use `Join()` with a `DefaultIfEmpty()` on the "right" sequence. But don't do that for L2E, normally. – Craig Stuntz Jul 12 '11 at 18:04
  • I have inherited a db that joins several tables without using any keys. (Before anyone weighs in with comments about that - I already know and as soon as I can replace the POS I will, but atm I can't.) And I can't create Navigations without them, so I have to do joins in code, and my personal preference is to use Lambda, in C# it makes more sense to me. – Tod May 05 '16 at 08:25