CODEWITHSUNDEEP
c#.netentity-frameworklinq
Funk
Funk

Reputation: 31

Need Help Grouping in C#

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

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

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

Upvotes: 1

Views: 77

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27526

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

Upvotes: 1

Related Questions