用子查询过滤儿童收集

Question

我在SQL中有一个查询，如下代码所示：

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)

现在，我必须将其转换为LINQ语法，因为我将EF用作数据访问。转换最后一行时我会遇到问题：

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

和我的linq语法：

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
);

如何解决此问题？

Answer 1

为什么不使用linq查询式语法？

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
...

取自：LINQ查询示例

Answer 2

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 )