Linq Group Join And Where Statement On Property Of The Joined Table

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

Solution 1:

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

Solution 2:

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()
selectnew
{
     id= Table1.ID
    ,test = Table1.Test
    ,test2 = Table2.Test
}