code View

         DateTime nowminus365 = DateTime.Now.AddDays(-365);

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

        //find last CViral prescription
        var qryPreOld = from eatt in db.d_events_att.Where(w => w.domain == "A_EXAMS_PRESC")
                        where
                            //eatt.vb13 == true
                            (!string.IsNullOrEmpty(eatt.vt7) && eatt.vt7.Substring(12, 1) == "1")
                        group eatt by new { eatt.id_person, eatt.date_event } into g
                        select new
                        {
                            g.Key.id_person,
                            MaxDiData = g.Max(p => p.date_event)
                        };

        //find last CViral prescription
        var qryPre = db.d_events_att.AsNoTracking().Where(w => w.domain == "A_EXAMS_PRESC" && (!string.IsNullOrEmpty(w.vt7) && w.vt7.Substring(12, 1) == "1") && w.date_event.HasValue)
                     .Select(s => new
                     {
                         s.id_person,
                         s.date_event
                     }).ToList()
                        .GroupBy(g => new { g.id_person })
                        .Select(s => new
                        {
                            dati = s.MaxBy(w => w.date_event)
                        })
                        .Select(s => new
                        {
                            id_person = s.dati.id_person,
                            MaxdiData = s.dati.date_event
                        }).AsQueryable().ToList();

        var qry = (from p in db.d_person
                   from q in q_uvu.Where(w => p.id_person == w.id_person).DefaultIfEmpty()
                   where
                        p.date_stopped == null
                   select new
                   {
                       p.id_person,
                       p.id_dream,
                       p.pe_surname,
                       p.pe_name,
                       p.pe_sex,
                       p.pe_dob,
                       q.idApt,
                       p.pe_note
                   }).AsEnumerable().Select(s => new
                   {
                       s.id_person,
                       s.idApt,
                       s.id_dream,
                       s.pe_surname,
                       s.pe_name,
                       s.pe_sex,
                       dob = (s.pe_dob.HasValue) ? s.pe_dob.Value.ToShortDateString() : "",
                       s.pe_note
                   }).Distinct().ToList();

        var qry2 = (from p in qry
                   from c in qryPre.Where(w => p.id_person == w.id_person)
                   where
                     c.MaxdiData < nowminus365
                   select new
                   {
                       p.id_person,
                       p.idApt,
                       p.id_dream,
                       p.pe_surname,
                       p.pe_name,
                       p.pe_sex,
                       p.dob,
                       c.MaxdiData,
                       p.pe_note
                   }).Distinct().ToList(); ;

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