I need to perform a left outer join between two tables using a primary key and an "in" clause.
Desired SQL
SELECT F.Id, B.Id
FROM Foo F
LEFT OUTER JOIN Bar B ON B.Id = F.Id and B.Name in ('Spam', 'Eggs')
LINQ Query
from f in dataContext.GetTable<Foo>
join b in dataContext.GetTable<Bar> on f.Id equals b.Id into b1
from b in b1.DefaultIfEmpty()
where new [] { "Spam", "Eggs" }.Contains( b.Name )
select new { FooId = f.Id, BarId = b.Id }
LINQ-generated SQL*
SELECT F.Id, B.Id
FROM Foo F
LEFT OUTER JOIN Bar B ON B.Id = F.Id
where B.Name in ('Spam', 'Eggs')
The generated SQL returns no rows, which probably has something to do with the records being filtered out from the join before the where clause is applied. Changing the LINQ query to something like this works, but is messy:
LINQ Query #2
from f in dataContext.GetTable<Foo>
join b1 in dataContext.GetTable<Bar> on new { f.Id, Name = "Spam" } equals new { b1.Id, b1.Name } into b1j
from b1 in b1j.DefaultIfEmpty()
join b2 in dataContext.GetTable<Bar> on new { f.Id, Name = "Eggs" } equals new { b2.Id, b2.Name } into b2j
from b2 in b2j.DefaultIfEmpty()
select new { FooId = f.Id, BarId = b1.Id != null ? b1.Id : b2.Id }
The normal composite key solution of new { ... } equals new { ... } isn't ideal, but it works. Is there a better way?
* I wrote that SQL in LINQPad based on a cleaned-up version of a larger query, so it doesn't have the aliasing and other stuff LINQ does to the SQL. The end result is the same, though.