Is it possible to write lambda left join on multiple columns that would generate OR condition instead of AND?
var query = context.table1.GroupJoin( context.table2, x => new { JoinCol1 = x.CustomerCode, JoinCol2 = x.CustomerName}, y => new { JoinCol1 = y.Code, JoinCol2 = y.Name}, (x, y) => new { firstTable = x, secondTable = y }).SelectMany( x => x.OrganizationAddress.DefaultIfEmpty(), (x, y) => { x.firstTable.field1, x.firstTable.field2, y.Field3, y.Field4 } )
This will generate following query
SELECT t1.filed1, t1.field2, t2.field3, t2.field4 FROM table1 LEFT JOIN table2 ON table1.CustomerCode = table2.Code AND table1.CustomerName = table2.Name
I would like to get the same query but instead of AND condition I want it to be OR:
SELECT t1.filed1, t1.field2, t2.field3, t2.field4 FROM table1 LEFT JOIN table2 ON table1.CustomerCode = table2.Code OR table1.CustomerName = table2.Name
Is there a way to achieve this?
EDIT: version – EF 6.2.0
Advertisement
Answer
LINQ join operators (Join
, GroupJoin
) support only equi-joins. All other join types have to be implemented as correlated subqueries.
For the join in question you would simply use the alternative LINQ left outer join pattern – correlated SelectMany
with DefaultIfEmpty()
. Something like this:
var query = context.table1.SelectMany( t1 => context.table2 .Where(t2 => t1.CustomerCode == t2.Code || t1.CustomerName == t2.Name) .DefaultIfEmpty(), (t1, t2) => new { t1.field1, t1.field2, t2.Field3, t3.Field4 });