LINQ查詢多個組和最新記錄的計數-OracleDB-有解無憂

LINQ 查詢多個組和最新記錄的計數 - Oracle DB

我試圖將 Linq 查詢分為 3 個（總計、成功、失敗），但到目前為止“總計”Linq 查詢作業正常。請幫我獲取“成功”、“失敗”列（它有多種狀態，我們必須檢查每個事務的最后一列和目的地）注意：您需要按 ProcessTime、TransactionId、Destination 分組并檢查最后一列是否是成功還是失敗然后應用計數（我們使用 oracle 作為后端）

總計數的 LINQ

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

uj5u.com熱心網友回復：

檢查此解決方案。如果它給出錯誤的結果，那么我需要更多的細節。

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/321747.html

標籤：C＃实体框架林克链接到 sql

上一篇：如何在DbContext中攔截ModelBuilder實體？

下一篇：錯誤“無法將JSON值轉換為System.String。路徑：$[1].Interests[1].Meta[9].Content|LineNumber：0|BytePositionInLine：100