Left join where right is null

Question

I'm trying to left join in c# to find only values from Foo, that aren't in Bar. In SQL I would normally do this:

SELECT * FROM FOO f
LEFT JOIN BAR b ON f.ID = b.ID
WHERE b.ID IS NULL

With linq I normally do something like this:

var matches = Foos.Join(
    Bars,
    foo => foo.ID,
    bar => bar.ID,
    (foo, bar) => foo
).Select(x => x.ID);

and then:

var noMatch = Foos.Where(x => !matches.Contains(x.ID));

Now to the question: Is there a way to achieve this within the .Join() function?

I think your `linq join` is wrong; it needs to be `GroupJoin`. See [this answer](https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods) which deals with the join. You can then select appropriate null values. — Peter Smith, Jan 22 '20 at 16:21
Not sure but this seems to work `context.Foos.Where(foo=> !foo.Bars.Any()).ToList()`. I am not sure if EF evaluates `Any` part on the client-side or not. That's why I am not putting it as an answer — ilkerkaran, Jan 22 '20 at 16:29

score 3 · Answer 1 · answered Jan 22 '20 at 16:22

3

You can do a left join as I show below:

var query= from f in Foos
           join b in Bars on f.Id equals b.Id into gb
           from sb in gb.DefaulfIfEmpty()
           where sb.ID==null
           select {f, sb};

answered Jan 22 '20 at 16:22

ocuenca

38,548
11
89
102

What's the lambda equivalent? – Peter Smith Jan 22 '20 at 16:25
Using `GroupJoin` as you quoted above, but IMHO query syntax describe better what you are trying to achieve and it's close to sql syntax – ocuenca Jan 22 '20 at 16:27
3

I absolutely agree; the lambda syntax is quite tortuous. – Peter Smith Jan 22 '20 at 16:28
2

In some cases I would prefer to use better method syntax, because at the end query syntax is just a syntactic sugar and the queries are translated to method syntax, but yes depending of context ;) – ocuenca Jan 22 '20 at 16:37

sam · Accepted Answer · 2020-01-22T17:12:22.317

2

Does something like this work:

Foos.
.GroupJoin(
    Bars,
    f => f.Id,
    b => b.Id,
    (f, lj) => new { f, lj })
    .SelectMany(t => t.lj.DefaultIfEmpty(),
            (t, b) => new { 
                foo = t.f,
                bar = b
                })
    .Where(a => a.bar.Any() == false)
    .ToList()

edited Jan 22 '20 at 17:12

answered Jan 22 '20 at 16:30

sam

1,937
1
8
14

I think this is missing the `WHERE b.ID IS NULL` condition – Alan Cheung Jan 22 '20 at 16:40
@AlanCheung Does't t.lj.DefaultIfEmpty() account for this? – sam Jan 22 '20 at 16:42
Sorta, it puts an empty list in place where the list `lj` would otherwise be null but does not remove them from the results. It ends up creating an the anonymous object below with bar = empty list – Alan Cheung Jan 22 '20 at 16:50
1

sorry, I was reading your first question incorrectly. Yes, where condition is missing. – sam Jan 22 '20 at 17:10
What if I want the following condition instead: `SELECT * FROM FOO f LEFT JOIN BAR b1 ON f.ID = b1.ID AND b1.CODE = 'A' LEFT JOIN BAR b2 ON f.ID = b2.ID AND b1.CODE = 'B' AND b1.ID IS NULL` (Linq-2-Sql approach) ? – Alexander Sep 10 '21 at 08:07
Hello @sam for some reason a.bar is an object and not a list can you tell me why ? did i mad any mistake ? – Hicham Bouchilkhi Nov 02 '22 at 16:02

score 0 · Answer 3 · answered Jan 22 '20 at 16:38

0

Query syntax version has already been answered, but I personally prefer the method syntax better:

var result = Foos
  .GroupJoin(
    Bars,
    f => f.ID,
    b => b.ID,
    (fos, bas) => new { Foo = fos, Bar = bas })
  .SelectMany(
    sum => sum.Bar.DefaultIfEmpty(),
    (sum, flatBar) => new 
    {
      Foo = sum.Foo,
      Bar = flatBar
    })
  .Where(anon => !anon.Bar.Any());

answered Jan 22 '20 at 16:38

Alan Cheung

244
1
7

When I follow your syntaxe it doesn't really work for me because anon.Bar bar is an object and not a list is there somthing that i miss ? – Hicham Bouchilkhi Nov 02 '22 at 16:23

Left join where right is null

3 Answers3