Linq group join and where statement on property of the joined table

Question

So I believe I have found out that group join is a left outer join and that is what I need. But I need to check if the joined tables property is null. But I haven't got it working yet.

So I basically need the equivalent of this query in Linq Entity Framework

SELECT
    id, test, test2 
FROM Table1 
LEFT OUTER JOIN Table2 ON 
    table1.id = table2.id 
WHERE table2.example = NULL;

I have tried to do this with lambda but without any success yet. I can't seem to get the hold of the table2 property example for the where statement.

Possible duplicate of [Linq to Sql: Multiple left outer joins](http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins) — Tony_KiloPapaMikeGolf, Dec 04 '16 at 22:05

score 1 · Answer 1 · answered Dec 05 '16 at 06:23

You can flow this example using LINQ Extension Method (GroupJoin):

    Table1.GroupJoin(Table2,
                    x => x.ID,
                    y => y.ID,
                    (tbl1, tbl2) => new {Table1=tbl1, Table2 =tbl2.DefaultIfEmpty()})
                    .SelectMany(
                    tbl => tbl.Table2.Where(t2 => t2.example == null).Select(x => new
                    {
                        id= tbl.Table1.ID,
                        test = tbl.Table1.Test,
                        test2 = tbl.Table2.Test
                    }))ToList();

Tony_KiloPapaMikeGolf · Answer 2 · 2016-12-04T22:25:09.020

You might want to check out: http://www.sqltolinq.com/

Linqer is a SQL to LINQ converter tool. It helps you to learn LINQ and convert your existing SQL statements.

Not every SQL statement can be converted to LINQ, but Linqer covers many different types of SQL expressions.

Lets assume you have Table1 and Table2 in an EF dbcontext.

from Table1 in context
from Table2 in context
    .Where(t2=> t2.ID == Table1.ID && t2.example == null).DefaultIfEmpty()
select new
{
     id= Table1.ID
    ,test = Table1.Test
    ,test2 = Table2.Test
}

Linq group join and where statement on property of the joined table

2 Answers2

Linked