CODEWITHSUNDEEP
c#sqlentity-framework
Jiří Kotásek
Jiří Kotásek

Reputation: 193

Entity Framework - Left outer join on multiple columns with OR condition

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

Upvotes: 3

Views: 2083

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205769

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
    });

Upvotes: 3

Related Questions