[linq] LINQ Join with Multiple Conditions in On Clause

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.

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.

This works fine for 2 tables. I have 3 tables and on clause has to link 2 conditions from 3 tables. My code:

from p in _dbContext.Products join pv in _dbContext.ProductVariants on p.ProduktId equals pv.ProduktId join jpr in leftJoinQuery on new { VariantId = pv.Vid, ProductId = p.ProduktId } equals new { VariantId = jpr.Prices.VariantID, ProductId = jpr.Prices.ProduktID } into lj

But its showing error at this point: join pv in _dbContext.ProductVariants on p.ProduktId equals pv.ProduktId

Error: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.

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 }