将 SQL 查询转换为 LINQ 查询

Question

我对LINQ

很陌生，我做过很多不成功的尝试将SQL查询转换为LINQ。请帮我解决一些问题。对此的确切 LINQ 是什么..提前谢谢。

只是整个查询的一部分

  select distinct p.IdPatient,p.IdDoc
  from patd p (NOLOCK)
     left outer join StatusChange sc (NOLOCK)
        on sc.IdPatient = p.IdPatient
           and sc.IdClinicNumber = 23430 
           and sc.IdStatus = 'A'
           and sc.DateStatusChange > GetDate()
     join TrtTyp t ON p.IdTreatmentType = t.IdTreatmentType 
           and t.TypeModality IN ('H','P')
  Where 
      p.IdType IN ('P','E','M')
      and (IsNull(p.IsInactive,0) in (1,0) or sc.IdStatusChange is not null)
      and Not Exists(
         Select 1
         From   Expire e (NOLOCK)
         Where  e.IdPatient = p.IdPatient
      )
      and p.IdClinicNumber = 23430

Answer 1

首先，

你们都需要以更规范的形式重写查询，实际上不需要连接

select distinct
    p.IdPatient, p.IdDoc
from patd as p
where 
    p.IdClinicNumber = 23430 and
    p.IdType in ('P','E','M') and
    p.IdTreatmentType in
    (
         select tt.IdTreatmentType
         from TrtTyp as tt
         where tt.TypeModality in ('H','P')
    ) and
    (
        isnull(p.IsInactive, 0) in (1,0) or
        p.IdPatient in 
        (
            select sc.IdPatient
            from StatusChange as sc
            where
                sc.IdClinicNumber = p.IdClinicNumber and
                sc.IdStatus = 'A' and
                sc.DateStatusChange > GetDate()
        )
    ) and
    p.IdPatient not in
    (
        select e.IdPatient
        from expire as e
    )

现在，您可以编写 LINQ。请记住，我没有您的数据来测试它

var query = 
from p in patds
where
    p.IdClinicNumber == 23430 &&
    (new char[] { 'P', 'E', 'M' }).Contains(p.IdType) &&
    (
        from t in TrtTyps
        where (new char[] { 'H','P' }).Contains(t.TypeModality)
        select t.IdTreatmentType
    ).Contains(p.IdTreatmentType) &&
    (
        (new int[] { 1, 0 }).Contains(p.IsInactive ?? 0) ||
        (
            from sc in StatusChanges
            where
                sc.IdClinicNumber == p.IdClinicNumber &&
                sc.IdStatus == 'A' &&
                sc.DateStatusChange > DateTime.Now
            select sc.IdPatient
        ).Contains(p.IdPatient)
    ) &&
    !(
        from e in Expires
        select e.IdPatient
    ).Contains(p.IdPatient)
select new {p.IdPatient, p.IdDoc};