Where clause causing cannot compare elements error

Question

I have two tables, Doctors and DoctorShifts, The relation between two table is one(Doctor) to many(DoctorShifts).

In the DoctorShifts I saved the days of week for each doctor that work on.

What I need is to display the list of the Doctors that work today, I tried the following expression but it does not work :

IQueryable<Doctor> TodayDoctorsQuery = _context.Doctors.Where(s => s.DoctorShifts.Where(s2 => s2.DayOfTheWeek == _todayNumber) != null);

The error is :

Cannot compare elements of type 'System.Collections.Generic.IEnumerable`1[[MedCare_Software.EDM.DoctorShift, MedCare-Software, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]'. Only primitive types, enumeration types and entity types are supported."}

How can I get the list of doctor that works today?

Answer 1

The inner where clause returns a IEnumerable<DoctorShift> which can't be compared to null.

Anyway, you're better off using Any as in:

var TodayDoctorsQuery = _context.Doctors.Where(
    s=> s.DoctorShifts.Any(s2 => s2.DayOfTheWeek == _todayNumber));

and make sure that DayOfTheWeek and _todayNumber are the same type.