Linq How To Return Last Date And Difference Between First And Last Count

I have a table with the following column and sample data: acteename updated_at count (count is not sorted in db) dev-52 2/7/2020 5:56:43 PM 1 dev-52 2

Solution 1:

One can apply the filter in very beginning itself. Use group by with into and then finally select what you need. Since its creating a anonymous object hence it would be better to start with 'var`.

Edited: Fetch the data in memory and then apply group by to avoid any problem in mapping with database side grouping.

var list =  (from p in _DBcontext.Events
            where p.acteename == item.AppName select p).ToList();

var fList = (from p in list 
            group p by p.acteename
            into gList
            selectnew
            {
               acteenamex = gList.Key,
               lastUpdated_at = gList.OrderBy(x => x.updated_at).Last().updated_at,
               diff = gList.OrderBy(x => x.updated_at).Last().count - 
                                      gList.OrderBy(x => x.updated_at).First().count
            }).ToList();

Solution 2:

You can filter the list by the AppGuid first, then order it, and then convert that to list. Now, use this list in a new variable to get the records you need from it.

var gList = _DBcontext.AppProcessCrashEvents
        .Where(x => x.Actee == item.AppGuid)
        .OrderByDescending(p => p.UpdatedAt)
        .ToList();

var result = newEvents
{
    Actee = gList[0].Actee,
    UpdatedAt = gList[0].UpdatedAt,
    CrashCount = gList[0].CrashCount - gList[gList.Count - 1].CrashCount
};

I hope this is what are you looking for.