C# - Dynamic Linq left outer join on multiple properties

c# dynamic-linq linq sql

Question

i want to do left outer join in Dynamic Linq, but i can't get the syntax right. In SQL it would look like this:

SELECT col1, col2, col3 from tableA as a
LEFT OUTER JOIN tableB as b on a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 = 1

In dynamic linq i tried this:

dbContext.tableA
.GroupJoin(tableB, col1 == tableA.col1 && col2 == tableA.col2 && col3 == 1)
.Select('new(col1, col2, col3)');

The third join parameter (column) is just hard coded, because it doesn't come from tableB. What is the correct linq code?

EDIT: It's not a duplicate question. I am looking for syntax that works with dynamic LINQ, not with normal linq

1
2
8/26/2016 9:40:49 AM

Popular Answer

Usually for joins and especially for left joins I use statement syntax.

Not tested, but would look something like this:

var result = from a in tableA
             from b in tableB.Where(x => x.col1 == a.col1 && x.col2 == a.col2 && a.col3 == 1).DefaultIfEmpty()
             select new { a.col1, a.col2, b.col3 };

By doing a .DefaultIfEmpty() on the join from tableB, it will treat this as a left join. If you leave out the .DefaultIfEmpty() then it will behave like an inner join.

0
8/25/2016 12:46:14 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow