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
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.