LINQ to Entity : Multiple join conditions

Question

There are numerous post regarding LINQ and multiple joins. I have however not found any solution to the join I'd like to make.

The SQL equivalent would be something like this:

SELECT * FROM table1 a LEFT JOIN table2 b ON a.col1 = b.key1 AND a.col2 = b.key2 AND b.from_date <= now() AND b.deleted = 0; 

Here's one of the numerous linq queries I've attempted

var query = (from x in context.table1              join y in context.table2 on new {x.col1, x.col2} equals {b.key1, b.key2}               into result              from result...... 

How may I add the additonal conditions of the date and deleted flag? If I use .Where conditions, then this is treated as a inner join, not a left join.

Answer 1

Another way could be like

var query = (from x in context.table1               join y in context.table2 on               new  {                   Key1 = x.col1,                    Key2 = x.col2,                   Key3 = true,                   Key4 = true                  }              equals              new {                   Key1 = y.key1,                    Key2 =  y.key2,                   Key3 = y.from_date< DateTime.Now,                   Key4 = !y.deleted                  }                into result from r in result.DefaultIfEmpty() select new  {x.Something, r.Something}