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.

Leandro Bardelli · Accepted Answer · 2019-07-30 19:35:42Z

94

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, 2019 at 19:35

Leandro Bardelli

11.7k16 gold badges88 silver badges130 bronze badges

answered Oct 14, 2011 at 9:59

Muhammad Adeel Zahid

17.8k16 gold badges95 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Carroll Over a year ago

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.

Jim Wooley · Accepted Answer · 2018-10-24 13:26:37Z

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 ???;

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.
The 1st linq produces a cross join. Not an inner join. Tested in EF Core 3.1

tonco · Accepted Answer · 2017-09-30 13:53:19Z

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

james lewis · Accepted Answer · 2011-10-14 09:19:31Z

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?

Leandro Bardelli · Accepted Answer · 2019-07-30 19:54:54Z

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

Collectives™ on Stack Overflow

LINQ to Entity : Multiple join conditions

5 Answers 5

1 Comment

7 Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

7 Comments

2 Comments

Comments

Comments

Linked

Related