GroupBy condition with max amount needed to be added to Linq

Question

I have been able to get a grouping based on 2 columns , but I need to group by the 3rd condition when the Amount is not greater than amount specified

Here I built up test data

Results in Linqpad look like this:

Group PayFromBankAccountId PaymentType TotalAmounts  TotalCount
---------------------------------------------------------------
  3       ABC                5             600           3
  3       ABC                6             600           3
  2       DEF                5             300           2
  2       DEF                6             300           2

Problem is I don't want it to have any Group with a Number over 500 in TotalAmounts

Classes

public class PaymentGroups
{
    public Int32 Group {get; set;}
    public string PayFromBankAccountId { get; set; }
    public int PaymentType { get; set; }
    public decimal TotalAmounts { get; set; }
    public int TotalCount { get; set; }
}

public class PaymentVouchers
{
    public string PayFromBankAccountId { get; set; }
    public int PaymentType { get; set; }
    public decimal TotalPaymentAmount { get; set; }
}


var paymentVouchers = new List<PaymentVouchers>()
{
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 5, TotalPaymentAmount = 100},
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 5, TotalPaymentAmount = 200},
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 5, TotalPaymentAmount = 300},
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 6, TotalPaymentAmount = 100},
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 6, TotalPaymentAmount = 200},
new PaymentVouchers { PayFromBankAccountId = "ABC", PaymentType = 6, TotalPaymentAmount = 300},
new PaymentVouchers { PayFromBankAccountId = "DEF", PaymentType = 5, TotalPaymentAmount = 100},
new PaymentVouchers { PayFromBankAccountId = "DEF", PaymentType = 5, TotalPaymentAmount = 200},
new PaymentVouchers { PayFromBankAccountId = "DEF", PaymentType = 6, TotalPaymentAmount = 100},
new PaymentVouchers { PayFromBankAccountId = "DEF", PaymentType = 6, TotalPaymentAmount = 200},
};

var paymentGroups = new List<PaymentGroups>();

paymentGroups = paymentVouchers
.GroupBy(x => new { 
                    x.PayFromBankAccountId, 
                    x.PaymentType, 
                    // Sum of TotalPaymentAmount <= 500    Can this go here
        })
.Select(x => new PaymentGroups
{
    PayFromBankAccountId = x.Key.PayFromBankAccountId,
    PaymentType = x.Key.PaymentType,
    TotalAmounts = x.Sum(z => z.TotalPaymentAmount), 
    TotalCount = x.Count(),
    Group = x.Count() // unsure ??
}).ToList();

paymentGroups.Dump("final");

So I am wanting to have new groups with the max counts of 500 , so maybe the Group column in final table needs to have group integer value to know - Maybe this will also need to have some other ID as well ?

What is it i'm trying to do? Group by an additional column of a Sum of Amounts so that they can't exceed 500 thus in the linq code notice where i put "// Sum of TotalPaymentAmount <= 500 Can this go here" Can that be done?

You ask for "Another GroupBy" but it looks like what you really want is simply to filter the groups you already have? `.Where(e => e.TotalAmounts < 500)` (before your final `.ToList()`) — McAden, Dec 04 '20 at 23:25
Could you include the desirable output of the specific input you gave as an example? — Theodor Zoulias, Dec 05 '20 at 01:41

score 2 · Answer 1 · answered Dec 04 '20 at 23:34

2

You could create a filter instead, that filters based on Sum of TotalPaymentAmount in each group. For example,

paymentGroups = paymentVouchers
.GroupBy(x => new { 
                    x.PayFromBankAccountId, 
                    x.PaymentType, 
        })
.Where(x=>x.Sum(z=>z.TotalPaymentAmount) <=500) // Add Filtering here
.Select(x => new PaymentGroups
{
    PayFromBankAccountId = x.Key.PayFromBankAccountId,
    PaymentType = x.Key.PaymentType,
    TotalAmounts = x.Sum(z => z.TotalPaymentAmount), 
    TotalCount = x.Count(),
    Group = x.Count() 
}).ToList();

answered Dec 04 '20 at 23:34

Anu Viswan

17,797
2
22
51

Ok , i see. What if I still want to have this remaining ones show up in the results - but as long as they are under 500 . So example of now this query returns only the BankAccounts for DEF , thus 2 records - and that is because they both total 300 , but what if i wanted the other records in there as well so ABC 5 100 , then ABC 5 200 and ABC 5 300 - ideally i want to group the 100 and 200 together , but the 300 will have to be by itself – Jeremy Miller Dec 04 '20 at 23:42

GroupBy condition with max amount needed to be added to Linq

1 Answers1