Filtering child collection with subquery

Question

I have a query in sql, as shown in the code below:

select *
from Registration r
    inner join RegistrationService rs on rs.RegistrationID = r.RegistrationID
    inner join Service s on s.ServiceID = rs.ServiceID
where cast(RegistrationDate as DATE) between @startDate and @endDate
    and s.ByDoctor = 'false'
    and rs.ServiceID not in (select ServiceID from TreatmentService ts where ts.TreatmentID = r.RegistrationID)

Now I have to convert this into linq syntax because I'm using EF as my data access. I'm getting a problem when converting the last line:

rs.ServiceID not in (select ServiceID from TreatmentService ts where ts.TreatmentID = r.RegistrationID)

and my linq syntax:

var query = context.Registrations.Where(r =>
    DbFunctions.TruncateTime(r.RegistrationDate) == DbFunctions.TruncateTime(DateTime.Today)
    &&
    r.RegistrationServices.Any(rs => rs.Service.ByDoctor == false)
    &&
    !(context.TreatmentServices.Select(ts => ts.ServiceID).Where(ts => ts.TreatmentID == r.RegistrationID)).Contains(rs.ServiceID) <-- here is the problem
);

How to solve this?

Answer 1

Why not use the linq query-like syntax?

from r 
in context.Registration
join rs in context.RegistrationService on rs.RegistrationID = r.RegistrationID
join s in context.Service on  s.ServiceID = rs.ServiceID
...

Taken from: LINQ query examples