C# - Dynamic Linq left outer join on multiple properties

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

What is the problem you are having here? Is it just the syntax you need? Try the [documentation](https://msdn.microsoft.com/en-us/library/bb534297%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396) — slawekwin, Aug 25 '16 at 12:45
Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — uTeisT, Aug 25 '16 at 12:49
@Oktay - from the documentation of dynamic linq it doesn't seem like they have a `GroupJoin` overload — Gilad Green, Aug 25 '16 at 12:53
- slawekwin yes, i need the syntax. @uteist not a duplicate. Your link leads to LINQ and i need dynamic LINQ. — Oktay Myumyunov, Aug 25 '16 at 12:53
@OktayMyumyunov have you find a solution for this? because I have exactly same problem — Asım Gündüz, Dec 09 '17 at 09:12

score 0 · Answer 1 · answered Aug 25 '16 at 12:46

0

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.

answered Aug 25 '16 at 12:46

Joe_DM

985
1
5
12

Thanks! I can't use the statement syntax, because the string i pass in select() is dynamic and comes in the function as parameter. – Oktay Myumyunov Aug 25 '16 at 12:53
you don't need to return an anonymous type, you can do a select new MyObject { First = col1, etc } alternatively, I've not tried it but if the function is a data adapter you can try to select the function with a .Invoke() – Joe_DM Aug 25 '16 at 13:13
there looks like a good example here of .SelectMany with a left join. http://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods – Joe_DM Aug 25 '16 at 13:15

score 0 · Answer 2 · answered Jun 06 '20 at 15:10

0

You can find an extension that facilitates left joins with Dynamic Linq on GitHub: https://github.com/SikeyMael/LinqHelper

answered Jun 06 '20 at 15:10

Paolo Crociati

483
2
9
21

C# - Dynamic Linq left outer join on multiple properties

2 Answers2