code View

        DateTime date5 = date_to.AddDays(-1825); //  5 years old before date_to


        //Trovo i pazienti con verifiche urgenti
        var q_uvu = db.d_events
             .AsNoTracking()
             .Where(x => x.event_type.ToLower() == "app_verify" && x.date_apt_exe == null)
             .Select(s => new { s.id_person, idApt = (string.IsNullOrEmpty(s.id_event) ? "" : "!") }).Distinct();


        //trovo data dell'ultima misurazione CViral
        var qryEsami = db.d_events_att
                        .Where(w => w.domain == "A_EXAMS_VLOAD" && w.date_event != null && w.vt2 != null)
                        .Select(s => new
                        {
                            s.id_person,
                            s.date_event,
                            PCR = s.vt2
                        }).ToList()
                        .GroupBy(g => new { g.id_person })
                        .Select(s => new
                        {
                            dati = s.MaxBy(w => w.date_event)
                        })
                        .Select(s => new
                        {
                            s.dati.id_person,
                            s.dati.date_event,
                            s.dati.PCR
                        }).ToList();

        var qryP = (from P in db.d_person
                        //  from qe in qryEsami.Where(w => w.id_person == P.id_person).DefaultIfEmpty()
                    from q in q_uvu.Where(w => w.id_person == P.id_person).DefaultIfEmpty()
                    where
                         (P.date_stopped == null || P.date_stopped > date_to1)
                         && P.pe_dob > date5
                    select new
                    {
                        P.id_person,
                        P.id_dream,
                        P.pe_surname,
                        P.pe_name,
                        P.pe_sex,
                        P.pe_dob,
                        P.date_started,
                        q.idApt
                        //  qe.PCR

                    }).ToList();

        var st = db.d_status
                    .AsNoTracking()
                    .Where(W => W.date_started <= date_to1 && W.domain == "TER_COT")
                    .OrderByDescending(o => o.date_started)
                    .ThenByDescending(o => o.id_status)
                        .ToList()
                        .GroupBy(g => g.id_person)
                        .Select(s => new
                        {
                            ida = s.Key,
                            status = s.MaxBy(m => m.date_started),
                        }).ToList()
                        .Select(s => new
                        {
                            s.ida,
                            CURRENT_STATUS = s.status.status,
                            ID_PERSON = s.status.id_person,
                            CURRENT_STATUS_ID = s.status.id_status,
                            DATE_STARTED = s.status.date_started
                        }).ToList();

        var qry = (from P in qryP
                   from s in st.Where(W => W.ID_PERSON == P.id_person)
                   from es in qryEsami.Where(W => W.id_person == P.id_person).DefaultIfEmpty()
                   where
                        s.CURRENT_STATUS == "YES"
                   select new
                   {
                       P.id_person,
                       P.idApt,
                       P.id_dream,
                       P.pe_surname,
                       P.pe_name,
                       P.pe_sex,
                       P.pe_dob,
                       P.date_started,
                       age = (P.pe_dob.HasValue ? (DateTime.Today.Date.Year - P.pe_dob.Value.Date.Year).ToString() : ""),
                       tercotdate = s.DATE_STARTED,
                       PCRvalue = (es != null ? es.PCR : "")
                   });


        dt = qry.ToDataTable();
        dt.TableName = "lst_data";