OR'd Linq outer join on multiple columns

Question

I have written a Linq query to do an outer join on multiple columns. My problem is that I want to OR the columns. In other words, I need the join condition:

on Bar.Id equals Foo.BarId1 or Bar.Id equals Foo.BarId2

Some examples I have seen on this site have used anonymous types to create AND conditions, but I can't figure out how to construct an OR using anonymous type or if it is even possible. I can find no example that matches.

My query:

    var data = (from f in Foos
                join b in Bars on f.BarId1 equals b.Id into tb
                  from xb in tb.DefaultIfEmpty()
                join b in Bars on f.BarId2 equals b.Id into tb2
                  from xb2 in tb2.DefaultIfEmpty()
                select new { Foo = f, Bar1 = xb, Bar2 = xb2 });

This works, but I have a feeling it isn't the best solution

EDIT

I spoke too soon. It doesn't work: when Foo.BarId1 points to a valid Bar, and Foo.BarId2 does not, it actually returns the same Bar for Bar1 and Bar2. I was expecting Bar1 to be valid and Bar2 to be null in this case.

EDIT

I'm getting closer. This is my latest query:

var data = from f in Foos
            from b in Bars.Where(x => x.Id == p.BarId1 || x.Id == p.BarId2).DefaultIfEmpty()
            select new { Foo = p, Bars = b };

I was expecting it to return:

Foo Bar    
f1  b1      
    b2

What I get is:

Foo Bar
f1  b1
f1  b2

EDIT

I've finally found the query that returns exactly what I want:

var data = from f in Foos
       select new
       {
           Foo = f,
           Bar1 = Bars.FirstOrDefault(x => x.Id == f.Bar1Id),
           Bar2 = Bars.FirstOrDefault(x => x.Id == f.Bar2Id)
       };

Would still like to know of any improvements I can make to this.

FINAL EDIT

I have gone back to the original query:

    var data = (from f in Foos
                join b in Bars on f.BarId1 equals b.Id into tb
                  from xb in tb.DefaultIfEmpty()
                join b in Bars on f.BarId2 equals b.Id into tb2
                  from xb2 in tb2.DefaultIfEmpty()
                select new { Foo = f, Bar1 = xb, Bar2 = xb2 });

It turns out that this was in fact working, but an error in my Linqpad test made me think it wasn't.

It is more efficient than the previous one too - in SQL Profiler I can see that it generates 1 SQL select, compared to the previous, which generates 3*n selects (does 2 Bar selects for every Foo select)

Hmm... have you hit the wall of LINQ limitations here? It looks like there not much you can do using just the `equals` clause. — code4life, Sep 27 '12 at 13:28
You haven't got (or can't create) navigation properties like `Foo.Bar1` and `Foo.Bar2`? — Gert Arnold, Sep 27 '12 at 22:22
@Gert Arnold. Plus this way I have more control over the generated query. — Kev, Sep 28 '12 at 08:47
Yes, you can have nullable (i.e. non-required) nav properties. They would generate outer joins in SQL, so `new { f, f.Bar1, f.Bar2 }` would be all you need. — Gert Arnold, Sep 28 '12 at 09:35
@Gert Arnold See my final edit. I am happy with this solution, but thanks for your input - it is a good alternative. Submit an answer and Ill accept it. — Kev, Sep 28 '12 at 14:51

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

As in the follwing link, LINQ only supports supports equal. If you need any other kind of join , use a cross-join and where:

Linq - left join on multiple (OR) conditions

edited May 23 '17 at 10:24

Community

1
1

answered Sep 27 '12 at 13:07

specificityy

580
1
5
24

That is what I'm doing, look at my queries. – Kev Sep 27 '12 at 13:10
Oh ok, but what is it that you need this for: `join db in Bars on 1 equals 1 into bs` why not make just one `join` like `var data = from f in Foos from b in Bars where f.Id == b.BarId1 || f.Id == b.BarId2` Also, I suggest you take a look at this tool: [link](http://www.linqpad.net).. it really comes in handy when testing Linq queries.. – specificityy Sep 27 '12 at 13:54
That is a fair point. I've edited my question. But, the returned structure is the same. – Kev Sep 27 '12 at 14:09
Well assuming that you have 2 tables: Foos Id BarId1 BarId2 10 1 NULL 20 NULL 2 Bars Id Name 1 'one' 2 'two' the result you're getting is the correct one. – specificityy Sep 27 '12 at 14:37

score 0 · Answer 2 · answered Sep 27 '12 at 13:48

LINQ also supports the ANSI-82 syntax by putting the join in the Where clause. See if the following works for you:

var data = from f in Foos
           from b in Bars
           where f.Id == b.BarId1 || f.Id == b.BarId2
           select new { Foo = p, Bars = bx };

I suspect this will only give you an inner join not an outer join, You may need to add in another where clause for !Contains to evaluate the outer condition based on your requirements.

score 0 · Accepted Answer · answered Sep 28 '12 at 15:01

It can be very simple if you are willing to introduce navigation properties Foo.Bar1 and Foo.Bar2. Then you can just do:

from f in Foos
select new { f, f.Bar1, f.Bar2 }

This will create outer joins in SQL. (Which may be a reason not to like these navigation properties, because it is easy to forget that they cause outer joins, without them you're always urged to take control yourself).

OR'd Linq outer join on multiple columns

3 Answers3