LINQ to Entity : Multiple join conditions

Question

There are numerous post regarding LINQ and multiple joins. I have however not found any solution to the join I'd like to make.

The SQL equivalent would be something like this:

SELECT * FROM table1 a
LEFT JOIN table2 b ON a.col1 = b.key1 AND
a.col2 = b.key2 AND
b.from_date <= now() AND
b.deleted = 0;

Here's one of the numerous linq queries I've attempted

var query = (from x in context.table1
             join y in context.table2 on new {x.col1, x.col2} equals {b.key1, b.key2} 
             into result
             from result......

How may I add the additonal conditions of the date and deleted flag? If I use .Where conditions, then this is treated as a inner join, not a left join.

score 84 · Accepted Answer · edited Jul 30 '19 at 19:35

84

Another way could be like

var query = (from x in context.table1 
             join y in context.table2 on 
             new  {
                  Key1 = x.col1, 
                  Key2 = x.col2,
                  Key3 = true,
                  Key4 = true
                 }
             equals
             new {
                  Key1 = y.key1, 
                  Key2 =  y.key2,
                  Key3 = y.from_date< DateTime.Now,
                  Key4 = !y.deleted
                 }  
             into result
from r in result.DefaultIfEmpty()
select new  {x.Something, r.Something}

edited Jul 30 '19 at 19:35

Leandro Bardelli

10,561
15
79
116

answered Oct 14 '11 at 09:59

Muhammad Adeel Zahid

17,474
14
90
155

8

I would also like to add a comment for anyone that might stumble upon this answer. The property names must match up on the two objects otherwise the compiler will complain about type interference. But this can be overcome by setting the property name explicitly as shown in the above example. – Max Carroll Jun 14 '18 at 13:46

Jim Wooley · Answer 2 · 2018-10-24T13:26:37.060

18

LINQ supports both the join syntax and the older ANSI-82 WHERE syntax. Using the later, you could do what your looking for on an inner join with

var nowTime = DateTime.Now;
var query = from a in context.table1
            from b in context.table2
            where a.col1 == b.key1
                 && a.col2 == b.key2 
                 && b.from_date < nowTime
                 && b.deleted == false
            select ???;

For an outer join, I prefer a syntax using a hybrid of where and select many. (Realize that the order in the LINQ query does not need to mimic what you would do in SQL and the order is more flexible.)

var nowTime = DateTime.Now;
var query = from b in context.table2
            from a1 in a.Where(a2 => 
                b.key1 = a.col && 
                b.key2 = a.col2 &&
                b.from_date < nowTime &&
                b.deleted == false).DefaultIfEmpty()
            select ???;

edited Oct 24 '18 at 13:26

answered Oct 14 '11 at 18:26

Jim Wooley

10,169
1
25
43

1

The ANSI-82 WHERE syntax is producing a cross join – shrutyzet Oct 18 '18 at 06:43
@shrutyzet is that with EF 6 or EF Core? – Jim Wooley Oct 19 '18 at 13:06
EF 6, maybe you forgot to add .DefaultIfEmpty() – shrutyzet Oct 23 '18 at 10:17
1

I did miss the left outer from the OP. You can use the ANSI-82 syntax for left outer as well as I described in [this blog post](http://www.thinqlinq.com/Post/Title%2FLeft-Outer-Joins-in-LINQ-with-Entity-Framework). – Jim Wooley Oct 24 '18 at 13:17
The 1st linq produces a cross join. Not an inner join. Tested in EF Core 3.1 – Kasun Koswattha May 21 '20 at 16:07
The posted answer from Muhammad Adeel Zahid is correct when I tested on Entity Framwork in standard .net but it cannot be work with core, your suggestion is working but you have to fix the syntax as it in your posted reference https://stackoverflow.com/users/112139/jim-wooley – Ahmad Hindash May 27 '20 at 22:55
With EF Core, it depends on which version you are using as each version of core has changed the resulting query rendering implementation. Make sure to check your generated SQL with each version and confirm that it works as desired when updating. – Jim Wooley Jul 08 '20 at 13:50

score 8 · Answer 3 · answered Sep 30 '17 at 13:53

I had problem with naming of properties in anonymous object:

var subscriptions = context.EmailSubscription.Join(context.EmailQueue,
                    es => new { es.Id, 9 },
                    eq => new { eq.EmailSubscriptionId, eq.EmailTemplateId },
                    (es, eq) => new { es.Id, eq.Id }
                ).ToList();

Compiler was not happy so above answer helps me to figure out what was wrong and here is my working solution. It took me some time to find stupid mistake :) :

var subscriptions = context.EmailSubscription.Join(context.EmailQueue,
                    es => new { EmailSubscriptionId = es.Id, EmailTemplateId  = 9 },
                    eq => new { eq.EmailSubscriptionId, eq.EmailTemplateId },
                    (es, eq) => new { es.Id, eq.Id }
                ).ToList();

This is the preferred syntax for [efcore for joining](https://entityframeworkcore.com/querying-data-joining). In the example above, es and eq are aliases. They are getting piped into a new anonymous object, with arbitrary properties like EmailSubscriptionId assigned to the value refered to by the es.Id for example. — JakeMc, Apr 30 '21 at 16:47

james lewis · Answer 4 · 2011-10-14T09:19:31.527

3

Could you not just filter the 1st result set with a second query?

var query = (from x in context.table1 
             join y in context.table2 on new {x.col1, x.col2} equals {b.key1, b.key2}  
             into result
query = from x in query
        where ...

Would that work?

edited Oct 14 '11 at 09:19

answered Oct 14 '11 at 09:08

james lewis

2,486
3
24
30

score 1 · Answer 5 · answered Jul 30 '19 at 19:54

In addition to @Muhammad Adeel Zahid answer, you could use also some several conditions like:

new
{
Key1 = ppl.PeopleId,
Key2 = true,
Key3 = true
}
equals
new
{
Key1 = y.PeopleId,
Key2 = !y.IsDeleted,
Key3 = (y.RelationshipType == 2 || y.RelationshipType == 4)
}

LINQ to Entity : Multiple join conditions

5 Answers5

Linked