convert sql queries to linq queries

Question

I am very new to LINQ ,I have made alot of unsuccessful attempts to convert a SQL query to LINQ.. Please help me out with some solution.What is the exact LINQ for this .. Thanks in advance.

// Just a part of the entire query

  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

With big queries it is often a better option to create a stored procedure or view and address that in EF. Especially when specific syntax as `NOLOCK` is vital you can't even use linq. — Gert Arnold, Nov 21 '12 at 13:11

Roman Pekar · Answer 1 · 2012-11-21T13:33:45.300

First of you all need to rewrite your query in more canonical form, you actually don't need joins

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
    )

Now you can write your LINQ. Keep in mind that I don't have your data to test it

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

Thanks alot Roman , will follow Ur instruction in future, and the query given fits the requirements except for few modifications which I am able to manage. Regards.. — Shash Gowda, Nov 22 '12 at 07:28

convert sql queries to linq queries

1 Answers1