LINQ Query Multiple Group and count of latest record - Oracle DB

Question

I tried to divided Linq queries into 3 (total, success, fail) but so far "Total" Linq query is working fine. Please help me to get "Success", "Fail" columns (it has mulitple statuses and we have to check the last column of each transaction and destination) Note: you need to group by ProcessTime, TransactionId, Destination and check last column whether it is success or Fail then apply count (we are using oracle as backend)

LINQ for Total count

 var query = (from filetrans in context.FILE_TRANSACTION
                             join route in context.FILE_ROUTE on filetrans.FILE_TRANID equals route.FILE_TRANID
                             where 
                                filetrans.PROCESS_STRT_TIME >= fromDateFilter && filetrans.PROCESS_STRT_TIME <= toDateFilter 
                             select new { PROCESS_STRT_TIME = DbFunctions.TruncateTime((DateTime)filetrans.PROCESS_STRT_TIME), filetrans.FILE_TRANID, route.DESTINATION }).
                             GroupBy(p => new { p.PROCESS_STRT_TIME, p.FILE_TRANID, p.DESTINATION });
                var result = query.GroupBy(x => x.Key.PROCESS_STRT_TIME).Select(x => new { x.Key, Count = x.Count() }).ToDictionary(a => a.Key, a => a.Count);

Answer 1

Check this solution. If it gives wrong result, then I need more details.

var fileTransQuery =
    from filetrans in context.AFRS_FILE_TRANSACTION
    where accountIds.Contains(filetrans.ACNT_ID) &&
      filetrans.PROCESS_STRT_TIME >= fromDateFilter && filetrans.PROCESS_STRT_TIME <= toDateFilter
    select filetrans;

var routesQuery =
    from filetrans in fileTransQuery
    join route in context.AFRS_FILE_ROUTE on filetrans.FILE_TRANID equals route.FILE_TRANID
    select route;

var lastRouteQuery = 
    from d in routesQuery.GroupBy(route => new { route.FILE_TRANID, route.DESTINATION })
        .Select(g => new 
        {
            g.Key.FILE_TRANID, 
            g.Key.DESTINATION,
            ROUTE_ID = g.Max(x => x.ROUTE_ID)
        })
    from route in routesQuery
        .Where(route => d.FILE_TRANID == route.FILE_TRANID && d.DESTINATION == route.DESTINATION && d.ROUTE_ID == route.ROUTE_ID)
    select route;

var recordsQuery =
    from filetrans in fileTransQuery
    join route in lastRouteQuery on filetrans.FILE_TRANID equals route.FILE_TRANID
    select new { filetrans.PROCESS_STRT_TIME, route.CRNT_ROUTE_FILE_STATUS_ID };

var result = recordsQuery
    .GroupBy(p => DbFunctions.TruncateTime((DateTime)p.PROCESS_STRT_TIME))
    .Select(g => new TrendData
    {
        TotalCount = g.Sum(x => x.CRNT_ROUTE_FILE_STATUS_ID != 7 && x.CRNT_ROUTE_FILE_STATUS_ID != 8 ? 1 : 0)
        SucccessCount = g.Sum(x => x.CRNT_ROUTE_FILE_STATUS_ID == 7 ? 1 : 0),
        FailCount = g.Sum(x => failureStatus.Contains(x.CRNT_ROUTE_FILE_STATUS_ID) ? 1 : 0),
        Date = g.Min(x => x.PROCESS_STRT_TIME)
    })
    .OrderBy(x => x.Date)
    .ToList();