将此查询转换为 Linq to SQL

Question

我需要找到所有没有见过病人的医生。我有表格：医生，病人和DrPatientXref。我有有效的SQL查询，但我无法弄清楚如何使其成为Linq to SQL查询。

select distinct Doctors.FirstName, Doctors.LastName, Doctors.DoctorId
from Doctors, DrPatientXref
where Doctors.DoctorId Not in 
(
    select DrPatientXref.DoctorId
    from DrPatientXref
    where DrPatientXref.PatientId = 23)

这是我的破解（这是痛苦的错误）：

var results = from d in db.Doctors
from x in db.DrPatientXrefs
    where
    (d.DoctorId == x.DoctorId && x.PatientId != patientId)
    select new { 
       d.DoctorId, d.FirstName, d.LastName
    };
    var listDrFullName = new List<DoctorFullName>();
    foreach (var dr in results) {
        DoctorFullName drFullName = new DoctorFullName();
        drFullName.FullName = dr.LastName + ", " + dr.FirstName;
        drFullName.DoctorId = dr.DoctorId;
        listDrFullName.Add(drFullName);
    }
    return listDrFullName;

该解决方案更改变量"结果"。在这里：

var results = db.Doctors.Except(
  (from x in db.DrPatientXrefs
    join d in db.Doctors on x.DoctorId equals d.DoctorId  
    where x.PatientId == patientId // probably not needed...
    select d)
  ).ToList();

Answer 1

在 Linq 中实现此查询和其他类似查询的最直接方法是使用 Linq 的基于集合的操作（Except、Intersect、Union、Distinct）。下面是在查询中使用Except的示例。

var drdrs = db.Doctors.Except(
  (from x in db.DrPatientXrefs
  join d in db.Doctors on d.DoctorId equals x.DoctorId
  where x.PatientId == patientId // probably not needed...
  select d)
).ToList();

我相信这应该可以解决问题（未经测试），并且在此过程中只是您的代码。

Answer 2

试试这个：

var patientId = 23;
var result = db.Doctors.Where(d => !db.DrPatientXref.Any(x => x.DoctorId == d.DoctorId 
       && x.PatientId == patientId))
.Select(d => new { d.FirstName, d.LastName, d.DoctorId } ).ToList();