CODEWITHSUNDEEP
c#entity-frameworklinq-query-syntax
Willy
Willy

Reputation: 1729

Filtering child collection with subquery

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?

Upvotes: 1

Views: 221

Answers (2)

jitender
jitender

Reputation: 10429

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 )

Upvotes: 0

Ori Nachum
Ori Nachum

Reputation: 598

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

Upvotes: 2

Related Questions