LINQ Join several conditions in the On section.

Question

I am trying to implement a query in LINQ that uses a left outer join with multiple conditions in an ON clause.

I will use the example of the following two tables Project (ProjectID, ProjectName) and Task (TaskID, ProjectID, TaskName, Completed). I want to see a complete list of all projects with their respective tasks, but only those tasks that have been completed.

I cannot use the filter for Completed == true , because it will filter out any projects that do not have completed tasks. Instead, I want to add Completed == true to the ON clause of the connection so that a complete list of projects is displayed, but only completed tasks will be shown. Projects without completed tasks will show one line with a zero value for the task.

Here is the basis of the request.

 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 to add && t2.Completed == true to the on clause?

I can not find any LINQ documentation on how to do this.

Answer 1

You just need to call 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 @svick's comments, here is another implementation that might make 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 } 

Answer 2

Here you can:

 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 } 

Answer 3

You cannot do that. The join clause (and the Join() extension method) only supports equijoins. This is also the reason why it uses equals rather than == . And even if you can do something like this, it won’t work, because join is an internal join, not an external join.