Linq to SQL | Get all records where a foreign key does not exist

advertisements

I have 2 tables:

Schools
-------
pk_school_id,
title

and

Business_Hours
--------------
pk_id,
fk_school_id

I want pk_school_id and title from School Table for all pk_school_id that does not exist as fk_school_id in Business_Hours table.

var Schools = (from b in Db.Language_School_Business_Hours
    join s in Db.Language_Schools on b.fk_school_id equals s.pk_school_id into lrs
    from lr in lrs.DefaultIfEmpty()
    select new
    {
       LeftID = b.fk_school_id,
       RightId = ((b.fk_school_id == lr.pk_school_id) ? lr.pk_school_id : 0)
    });


try this to achieve your goal, without join, just take elements that are not contained in Business_Hours table

   var Schools = Db.Language_Schools
   .Where(s => !Db.Language_School_Business_Hours
   .Select(b => b.fk_school_id).ToList().Contains(s.pk_school_id))
   .Select(x => new
    {
        x.pk_school_id,
        x.school_title
   });