How to Perform a LINQ to SQL Left Outer Join with Multiple Join Conditions?

LINQ to SQL: Left Outer Join with Multiple Join Conditions

In LINQ to SQL, translating SQL queries involving left outer joins with multiple join conditions can be challenging. This article addresses a scenario where a SQL query with a left outer join and an additional join condition needs to be converted to LINQ.

SQL Query

SELECT f.value
FROM period as p 
LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17
WHERE p.companyid = 100

LINQ Translation
The typical LINQ implementation of a left outer join using DefaultIfEmpty() can be straightforward, but incorporating the additional join condition requires careful consideration.

The following initial attempt, while syntactically correct, does not yield the desired result:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.DefaultIfEmpty()
where p.companyid == 100 && fgi.otherid == 17
select f.value

To achieve the desired behavior, the additional join condition must be introduced before calling DefaultIfEmpty(). This is accomplished using either extension method syntax or a subquery:

Extension Method Syntax

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

Subquery

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in (from f in fg
             where f.otherid == 17
             select f).DefaultIfEmpty()
where p.companyid == 100
select f.value

The above is the detailed content of How to Perform a LINQ to SQL Left Outer Join with Multiple Join Conditions?. For more information, please follow other related articles on the PHP Chinese website!