LINQ to Entity : Multiple join conditions

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 .Wh开发者_开发技巧ere conditions, then this is treated as a inner join, not a left join.

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}

LINQ supports both the join syntax and the older ANSI-82 WHERE syntax. Using the later, you could do what your looking for on an inner join with

var nowTime = DateTime.Now;
var query = from a in context.table1
            from b in context.table2
            where a.col1 == b.key1
                 && a.col2 == b.key2 
                 && b.from_date < nowTime
                 && b.deleted == false
            select ???;

For an outer join, I prefer a syntax using a hybrid of where and select many. (Realize that the order in the LINQ query does not need to mimic what you would do in SQL and the order is more flexible.)

var nowTime = DateTime.Now;
var query = from b in context.table2
            from a1 in a.Where(a2 => 
                b.key1 = a.col && 
                b.key2 = a.col2 &&
                b.from_date < nowTime &&
                b.deleted == false).DefaultIfEmpty()
            select ???;

I had problem with naming of properties in anonymous object:

var subscriptions = context.EmailSubscription.Join(context.EmailQueue,
                    es => new { es.Id, 9 },
                    eq => new { eq.EmailSubscriptionId, eq.EmailTemplateId },
                    (es, eq) => new { es.Id, eq.Id }
                ).ToList();

Compiler was not happy so above answer helps me to figure out what was wrong and here is my working solution. It took me some time to find stupid mistake :) :

var subscriptions = context.EmailSubscription.Join(context.EmailQueue,
                    es => new { EmailSubscriptionId = es.Id, EmailTemplateId  = 9 },
                    eq => new { eq.EmailSubscriptionId, eq.EmailTemplateId },
                    (es, eq) => new { es.Id, eq.Id }
                ).ToList();

Could you not just filter the 1st result set with a second query?

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
query = from x in query
        where ...

Would that work?

In addition to @Muhammad Adeel Zahid answer, you could use also some several conditions like:

new
{
Key1 = ppl.PeopleId,
Key2 = true,
Key3 = true
}
equals
new
{
Key1 = y.PeopleId,
Key2 = !y.IsDeleted,
Key3 = (y.RelationshipType == 2 || y.RelationshipType == 4)
}