LINQ Join with Multiple Conditions in On Clause

Question

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  amp  amp  t2 Completed    true to the on clause   I can t seem to find any LINQ documentation on how to do this

User · Accepted Answer

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 }

User · Answer

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

User · Answer

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

User · Answer

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

[linq] LINQ Join with Multiple Conditions in On Clause

Examples related to linq

Examples related to join