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?

@Pikoh I know it's caused by `rs.ServiceID`, but I did not find a way to convert this into linq. — Willy, Mar 15 '17 at 08:16

score 2 · Accepted Answer · answered Mar 15 '17 at 08:20

2

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

answered Mar 15 '17 at 08:20

Ori Nachum

588
3
19

Why not use the nice navigation properties? – Ivan Stoev Mar 15 '17 at 08:39

jitender · Answer 2 · 2017-03-15T08:32:35.643

0

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

where s.Where(ts=>ts.TreatmentID == r.RegistrationID).All(ts => ts.ServiceID  != rs.ServiceID )

edited Mar 15 '17 at 08:32

answered Mar 15 '17 at 08:26

jitender

10,238
1
18
44

Filtering child collection with subquery

2 Answers2