LINQ to SQL - Left Outer Join with multiple join conditions?

Question

I have the following SQL, which I am trying to translate to LINQ: 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 I have seen the typical implementation of the left outer join (ie. `into x from y in x.DefaultIfEmpty()` etc.) but am unsure how to introduce the other join condition (`AND f.otherid = 17`) Why is the `AND f.otherid = 17` condition part of the JOIN instead of in the WHERE clause? Because `f` may not exist for some rows and I still want these rows to be included. If the condition is applied in the WHERE clause, after the JOIN - then I don't get the behaviour I want. Unfortunately this: 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 seems to be equivalent to this: SELECT f.value FROM period as p LEFT OUTER JOIN facts AS f ON p.id = f.periodid WHERE p.companyid = 100 AND f.otherid = 17 which is not quite what I'm after.

Answer 1

Hi,

"I have seen the typical implementation of the left outer join (ie. `into x from y in x.DefaultIfEmpty()` etc.) but am unsure how to introduce the other join condition (`AND f.otherid = 17`)"

You are correct about the DefaultIfEmpty() for left join in LINQ. The 'f.otherid = 17' can also be written in the WHERE clause in SQL and will produce the same result. I suggest using the where clause in LINQ to accomplish the same behavior. However, I'm not sure why you are doing a left join and then filtering the results to only show when the otherid = 17 as this results in an inner join

For example,

create table period (id int, companyid int);

create table fact(factId int, periodId int, otherId int, value varchar(100));

insert into period values(1, 100);

insert into fact values(1, 1, 17, "im selected"), (1, null, 17, "im not selected");

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

Only selects one row. Please use this online editor to validate -> https://www.jdoodle.com/execute-sql-online/

"Because `f` may not exist for some rows and I still want these rows to be included."

There are not included because you are filtering the rows to only include ones that have an otherId = 17. If there isn't a match on periodId then the columns for fact for that row will be NULL and your AND clause is filtering the NULLs out.