There's nothing wrong with Maciej's response but I think explaining what went wrong in your example might help in the future.
Your LINQ example is very close - here's the proper query:
var query = from staff in db.Staffs
join checkIn in db.CheckIns on staff.StaffID equals checkIn.StaffID
into result
from r in result.DefaultIfEmpty()
where staff.ContractStatusID == 1 && (r == null || r.CheckIn == null)
select new
{
staff.StaffID,
FullName = staff.FirstName + " " + staff.LastName
};
When doing a group join the results list always exists even if empty. Normally these empty results are skipped when iterating over the result set. When you use
DefaultIfEmpty()
you are setting these empty lists to their default in order to always have a pairing with the left table (left outer join). The rows for
db.CheckIns
are reference types though so their default is
null
. This means that
where ... c.CheckIn == null
is causing the problem since you cannot access
null.CheckIn
.
To handle both cases where A) the results are empty and B) the CheckIn value is null, you need to do a short-circuit check on
c == null
. This will catch the case where the result list is empty
before you attempt to access the CheckIn property to check if that is null.
EDIT: To better explain group join, it works like below where each row in the left table is associated with one of these:
resultList = new List<T>(); resultList.Add(result1); resultList.Add(result2);
resultList = null;
resultList = new resultList<T>(); resultList.Add(default(T));