在C#中需要幫助分組

我有以下代碼可以按發票編號和

• 如果輸入 1 或 2 我想顯示sum(Amount)為Interest，
• 如果輸入 3 或 4 我想顯示sum(Amount)為Principal

我嘗試將以下代碼分組，InvoiceNumber如下所示，但出現InvalidOperationException: The LINQ expression GroupByShaperExpression'例外。

           var payments = await _dbContext.ProductPayments.AsNoTracking()         
               .Where(pp => pp.PaymentID == request.PaymentID)
               .ToListAsync();

            var ProductPaymentIDs = ProductPayments.Select(pp => pp.ID).ToList();

            var Details =  Context.PaymentCodingLines.AsNoTracking()        
               .Include(cl => cl.ProductPaymentNav)
               .Where(cl => ProductPaymentIDs.Contains(cl.ProductPaymentID))       
               .GroupBy(g => new
                {                        
                    InvoiceNumber = g.InvoiceLineNav.InvoiceNav.InvoiceNumber                       
                })
                .Select(s => new DetailDTO
                {
                    InterestPaid = s.Where(pp => pp.InvoiceLineNav.TypeID == 1|| pp.InvoiceLineNav.TypeID == 2).Sum(a => a.Amount),
                    PrincipalPaid = s.Where(pp => pp.InvoiceLineNav.TypeID == 3 || pp.InvoiceLineNav.TypeID == 4).Sum(a => a.Amount)
                })
                .ToList();

例外錯誤

休息服務失敗：URL：帶有狀態代碼 InternalServerError 的 url。錯誤內容：System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: new { InvoiceNumber = (i.InvoiceNumber) }, ElementSelector:(EntityShaperExpression: EntityType: PaymentCodingLine ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: FalseWhere) pp => pp.InvoiceLineNav.TypeID == __TypeKey_2 || pp.InvoiceLineNav.TypeID == __TypeKey_3)' 無法翻譯。以可翻譯的形式重寫查詢，或通過插入對 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的呼叫顯式切換到客戶端評估。有關詳細資訊，請參閱https://go.microsoft.com/fwlink/?linkid=2101038。在..

我想完成

 Type       Amount    InvoiceNumber
 1                 $100       123
 2                 $50        123
 3                 $100       123
 4                 $1200      123
 1                 $100       124
 1                 $300       124
 3                 $100       124
 3                 $300       124
 4                 $100       124

我想按發票編號分組并匯總型別 = 1&2 的金額欄位的值，并顯示為利息，將 3&4 顯示為 Princepal

InvoiceNumber       Interest   Princepal
123                 $150        $1300
124                 $400        $500

uj5u.com熱心網友回復：

有幾個問題：

• EF Core 無法訪問導航屬性 GroupBy
• 即使 EF Core 可以翻譯此查詢，它也將無效。

考慮以下列方式重寫查詢：

var ProductPaymentIDs = ProductPayments.Select(pp => pp.ID).ToList();

var query =
    from cl in Context.PaymentCodingLines
    where ProductPaymentIDs.Contains(cl.ProductPaymentID)
    group new { cl.InvoiceLineNav.TypeID, cl.Amount } by g.InvoiceLineNav.InvoiceNav.InvoiceNumber into g
    select new DetailDTO
    {
        InvoiceNumber = g.Key,
        InterestPaid = g.Sum(x => x.TypeID == 1 || x.TypeID == 2 ? x.Amount : 0),
        PrincipalPaid = g.Sum(x => x.TypeID == 3 || x.TypeID == 4 ? x.Amount : 0)
    };

var Details = query.ToList();

標籤：C＃ 。网 实体框架 林克

上一篇：如何在Xamarin中獲取父母控制的所有孩子？

下一篇：將DataGridView記錄匯出到Excel