I have the following code to group payment amount by Invoice Number and

• If Type 1 or 2 I want to Display the sum(Amount) as Interest,
• If Type 3 or 4 I want to Display the sum(Amount) as Principal

I tried the following code to Group by InvoiceNumber as shown below but i get InvalidOperationException: The LINQ expression GroupByShaperExpression' exception.

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

Exception Error

Rest Service FAILED: URL: url with status code InternalServerError. Error Content: System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: new { InvoiceNumber = (i.InvoiceNumber) }, ElementSelector:(EntityShaperExpression: EntityType: PaymentCodingLine ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .Where(pp => pp.InvoiceLineNav.TypeID == __TypeKey_2 || pp.InvoiceLineNav.TypeID == __TypeKey_3)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at..

I want to accomplish

 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

I want to group by invoice Number and Sum the value of Amount field for Type = 1&2 and display as Interest and 3&4 as Princepal

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

CodePudding user response：

There are several problems:

• EF Core cannot access navigation properties after GroupBy
• Even if EF Core could translate this query, it would be ineffective.

Consider to rewrite the query in the following way:

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