code View

// EXAMPLE 1
// last SERVICE
var qLastSStart = db.d_status
             .AsNoTracking().AsEnumerable()
             .Where(w => w.date_started < date_to1 && w.domain == "SERVICE" 
                                                   && (w.deleted == null || !(bool)w.deleted))
             .GroupBy(g => g.id_person)
             .Select(s => new
             {
                 id_person = s.Key,
                 dati = s.MaxBy(m => m.date_started)
             }).ToList(); 



// EXAMPLE 2
// Last data for last status
var st = db.d_status
             .AsNoTracking()
             .Where(W => W.date_started <= date_to1 && W.domain == "TER_COT" 
                                                    && (w.deleted == null || !(bool)w.deleted))
             .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();



#EXAMPLE 3

//Last start for each patient before End of period date
  var qLastSStart = db.d_status
                      .AsNoTracking()
                      .Where(w => w.date_started < date_to1 && w.domain == "TARV" 
                                                            && w.status == "TARV_SI")
                      .GroupBy(g => g.id_person)
                      .Select(s => new
                      {
                         id_person = s.Key,
                         LastSStart = s.Max(m => m.date_started)
                      });

#EXAMPLE 4 (on d_events)

//las appointments
var _dataLastApp = db.d_events
                     .AsNoTracking()
                     .Where(w => w.domain == "APPOINTMENT" && w.event_status == "APE-002"                      
                            && w.event_type == "VISIT"         
                            && w.date_apt_exe != null
                            && (w.deleted == null || !(bool)w.deleted))
                     .GroupBy(o => new { o.id_person })
                     .Select(s => new
                      {
                        idAnagrafica = s.Key.id_person,
                        DataApt = s.Max(g => g.date_apt),
                      }).ToList();