124

I'm trying to implement a query in LINQ that uses a left outer join with multiple conditions in the ON clause.

I'll use the example of the following two tables Project (ProjectID, ProjectName) and Task (TaskID, ProjectID, TaskName, Completed). I want to see the full list of all projects with their respective tasks, but only those tasks that are completed.

I cannot use a filter for Completed == true because that will filter out any projects that do not have completed tasks. Instead I want to add Completed == true to the ON clause of the join so that the full list of projects will be shown, but only completed tasks will be shown. Projects with no completed tasks will show a single row with a null value for Task.

Here's the foundation of the query.

from t1 in Projects
join t2 in Tasks
on new { t1.ProjectID} equals new { t2.ProjectID } into j1
from j2 in j1.DefaultIfEmpty()
select new { t1.ProjectName, t2.TaskName }

How do I add && t2.Completed == true to the on clause?

I can't seem to find any LINQ documentation on how to do this.

Nalaka526
  • 11,278
  • 21
  • 82
  • 116
Kuyenda
  • 4,529
  • 11
  • 46
  • 64

3 Answers3

184

You just need to name the anonymous property the same on both sides

on new { t1.ProjectID, SecondProperty = true } equals 
   new { t2.ProjectID, SecondProperty = t2.Completed } into j1

Based on the comments of @svick, here is another implementation that might make more sense:

from t1 in Projects
from t2 in Tasks.Where(x => t1.ProjectID == x.ProjectID && x.Completed == true)
                .DefaultIfEmpty()
select new { t1.ProjectName, t2.TaskName }
Vikrant
  • 4,920
  • 17
  • 48
  • 72
Aducci
  • 26,101
  • 8
  • 63
  • 67
  • 5
    That seems like a non obvious way to do that. I'm not sure I would understand what it's suppose to do. – svick Oct 05 '11 at 16:52
  • 1
    @svick - Using anonymous types allow you to join on multiple criteria. You just need to make sure the property names match on both types. Not sure where the confusion is coming from? – Aducci Oct 05 '11 at 16:58
  • 2
    The confusion is that it really makes more sense as two equalities joined by `and`, not one equality of some “weird” object. And to prove my point, your code is wrong. For it work, you would have to have `true` on the left side and `t2.Complete` on the right. – svick Oct 05 '11 at 17:01
  • 2
    Thanks Aducci. I had to swap sides in the query to get the context right, but that worked. This problem is simplified, and in my real world problem it's not just SecondProperty is true or false, SecondProperty is an integer and I use `AND SecondProperty IN (123, 456)`. I'll be moving on to that challenge and any help you could give would be greatly appreciated. – Kuyenda Oct 05 '11 at 17:03
  • @svick - Good catch, I switched the order of the *t2.Completed* and the *true* value. I added another solution that might be less weird for you. – Aducci Oct 05 '11 at 17:08
  • 1
    @Kuyenda - Look at my second solution: you can change *x.Completed == true* to *listOfIntergers.Contains(x.Completed)* – Aducci Oct 05 '11 at 17:10
  • Yup, just noticing this. Working it right now. Thanks so much! – Kuyenda Oct 05 '11 at 17:33
  • @Aducci - The latter solution is still a left outer join by virtue of `DefaultIfEmpty` correct? My example was set up to be easily grouped because I join into j1, and then after `from j2 in j1.DefaultIfEmpty()` I added `group j2 by`. How would I group your latter example? Thanks again! – Kuyenda Oct 05 '11 at 18:17
  • @Kuyenda Here you can see this example: https://stackoverflow.com/questions/71495439/linq-join-with-in-condition-in-on-clause – TotPeRo Mar 16 '22 at 10:39
63

Here you go with: 

from b in _dbContext.Burden 
join bl in _dbContext.BurdenLookups on
new { Organization_Type = b.Organization_Type_ID, Cost_Type = b.Cost_Type_ID } equals
new { Organization_Type = bl.Organization_Type_ID, Cost_Type = bl.Cost_Type_ID }
Nalan Madheswaran
  • 10,136
  • 1
  • 57
  • 42
0

You can't do it like that. The join clause (and the Join() extension method) supports only equijoins. That's also the reason, why it uses equals and not ==. And even if you could do something like that, it wouldn't work, because join is an inner join, not outer join.

svick
  • 236,525
  • 50
  • 385
  • 514