LINQ to Entity : Multiple join conditions
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();
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 ???;