How to generate IS NULL and IS NOT NULL in Entity Framework 6 in LEFT JOIN

Question

This is the scenario. I have two tables in a 1-to-M relation;

For illustration purposes lets define them as

MyHeaderTable (headerID, col1, col2)
MyDetailTable (lineID, headerID, statusID, col3, col4)

Note the child rows are optional (header record may exist without corresponding lines, hence the LEFT JOIN.

I am interested in the following resultset, using T-SQL :

SELECT MyHeaderTable h
    LEFT JOIN MyDetailTable c ON h.headerID = c.headerID
    WHERE c.lineID IS NULL  -- no corresponding children
    OR (c.lineID is NOT NULL AND c.statusID != 2) -- children rows must have status NOT 2

The question is how do i write the above T-SQL in EF6 linq ?

My attempt appears below but i am having trouble generating the linq query with IS NULL and IS NOT NULL :

var query = from h in ctx.MyHeaderTable
            join c in ctx.MyDetailTable on h.headerID equals c.headerID into joinedTbl
            from j in joinedTbl.DefaultIfEmpty() //LEFT JOIN
            where j.lineID is null
            || (j.lineID != null && j.statusID !=2)
            select;
var results = query.ToList();

*Note the specific version of EF6, i know EF has evolved and not interested in what earlier versions did.

Answer 1

EF automatically creates the proper query when checking the entity itself for null.

Your query should be:

var query = from h in ctx.MyHeaderTable
            join c in ctx.MyDetailTable on h.headerID equals c.headerID into joinedTbl
            from j in joinedTbl.DefaultIfEmpty() //LEFT JOIN
            where j == null || (j != null && j.statusID != 2)
            select;