Linq Left Join where right is null

Question

relatively new to linq, coming from SQL. So, I'm trying to figure out left joins for the following:

SELECT * from MASTER m
LEFT JOIN CHILD C
    ON m.ID=C.MASTER_ID
WHERE C.MASTER_ID is null

So, normally this would return all of the records from Master that do not have a child. I've discovered the .DefualtIfEmpty() but that doesn't eliminate the master records that have children.

I started along the lines of:

var recs=from m in MASTER
         from c in child
             .where (mapping=>mapping.MasterId == m.Id)
             .DefaultIfEmpty()
         select new { MasterId = m.Id};

But that's as far as I got and got stuck. I'm assuming the .DefaultIfEmpty() isn't what I'm looking for. Note: The master table has a few million rows in it. The children are close to the same count. I only mention because it won't be efficient to pull back all of the records, etc. Ideally, the SQL generated will look like the SQL I posted.

Thanks All.

Answer 1

This will give you all master records without children.

var recs = MASTER.Where(x => !child.Any(y => m.Id == c.MasterId));

the !Any will generate a not Exists SQL statement which will be translated into a anti semi join in the execution plan which is the best you can get for this type of check.