C# List of properties. Need to group them based on 2 conditions

Question

I have a List of database data in which 2 properties are important to group on. I stripped out the non-important properties and created a List of the data in which it needs to be sorted.

Not sure if some method with algorithm with linq statement should be done, and then how would it be best to store the results?

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

Data:

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

Thus from the data, I need to group ABC and 5 together as there are 3 of those , then group together ABC and 6 , there are also 3 of those by chance. Then in the data 2 other groups are seen DEF and 5 - 2 of those and DEF and 6 , there are 2 of those.

So if I did a foreach loop, a linq statement - How do I keep them from getting messy with grouping them into what type of collection ? Hashset of Tuples ? Some new List with a new property that is an identifier "key" . Performance is ideal

Thoughts?

Additions: update:

I'm having trouble putting that data in the list into a new list

var paymentGroups = new List<PaymentGroups>();



paymentGroups = paymentVouchers.GroupBy(x => new { x.PayFromBankAccountId, x.PaymentType })
.Select(x => new
{
    x.Key.PayFromBankAccountId,
    x.Key.PaymentType,
    //x.Sum(PaymentType)
    Sum = x.Sum(TotalPaymentAmount),
    paymentGroups.TotalCount = x.Key.Count 
});


paymentGroups.Dump();

}

public class PaymentGroups {

public string PayFromBankAccountId { get; set; }
public int PaymentType { get; set; }
public decimal TotalAmounts { get; set; }
public int TotalCount {get; set; }

}

l33t · Accepted Answer · 2020-12-04T00:08:30.963

2

Use ValueTuple for grouping. It's hard to compete with its performance characteristics. Note that LINQ is not ideal for performance.

List<(string Group, int Total, int Amount)> data = paymentVouchers
    .GroupBy(v => (v.PayFromBankAccountId, v.PaymentType))
    .Select(g => (g.Key, Vouchers: g.ToList()))
    .Select(t => (Group: $"{t.Key.PayFromBankAccountId}|{t.Key.PaymentType}", Total: t.Vouchers.Count, Amount: t.Vouchers.Sum(x => x.PaymentType)))
    .ToList();

edited Dec 04 '20 at 00:08

answered Dec 03 '20 at 23:42

l33t

18,692
16
103
180

I added to my question. I have a List that I would like to use to store the groups with total count, total amounts and the other values . How can I achieve this ? So var Groups - say that was set to be that new List PaymentGroups that I would like to hydrate with the group results? – Jeremy Miller Dec 03 '20 at 23:48
I believe you are looking for `g.ToList()`. It's a bit hidden. Please try the new code above. It's a good start. – l33t Dec 04 '20 at 00:09
I see , so then I could have my new List replace this on the fly List "data" ... – Jeremy Miller Dec 04 '20 at 00:32

Afshin Mobayen Khiabani · Answer 2 · 2020-12-03T23:58:36.970

1

You can use 2 properties on group by like this:

paymentVouchers.GroupBy(x => new { x.PayFromBankAccountId, x.PaymentType }).Select(x => new {
  x.Key.PayFromBankAccountId,
  x.Key.PaymentType,
  x.Sum(/*Something*/)
  // REST
})

EDIT:

paymentGroups = paymentVouchers.GroupBy(x => new { x.PayFromBankAccountId, x.PaymentType })
.Select(x => new 
{
    x.Key.PayFromBankAccountId,
    x.Key.PaymentType,
    TotalAmounts = x.Sum(TotalPaymentAmount),
    TotalCount = x.Count() 
});

This will return a list grouped by 2 keys and their value and sum of TotalPaymentAmount for each group and count of each group.

If you want to put in your defined class then:

paymentGroups = paymentVouchers.GroupBy(x => new { x.PayFromBankAccountId, x.PaymentType })
.Select(x => new PaymentGroups
{
    PayFromBankAccountId = x.Key.PayFromBankAccountId,
    PaymentType = x.Key.PaymentType,
    TotalAmounts = x.Sum(TotalPaymentAmount),
    TotalCount = x.Count() 
});

edited Dec 03 '20 at 23:58

answered Dec 03 '20 at 23:07

Afshin Mobayen Khiabani

1,079
10
21

That does group them, thank you for that. However - how could I then store them into some collection in which I would then know the number count of each , and then yes that 3rd property will end up showing an $ amount in which i can that know the total for each grouping. Currently that Linq statement spits out the distinct groups if i set it to var blah = your code - but how can i count how many there are and then place that in some collection ? – Jeremy Miller Dec 03 '20 at 23:28
Oh, I see a Sum ... hmm so if I created some new list to hold thing values ... – Jeremy Miller Dec 03 '20 at 23:29
If you want the count inside the collection. You can just change the x.Sum(/*something*/) with Count = x.Count() – Afshin Mobayen Khiabani Dec 03 '20 at 23:31
Or for sum of the collection you can do "Sum = x.Sum(TotalPaymentAmount)" – Afshin Mobayen Khiabani Dec 03 '20 at 23:36
I added to the question a new class with properties , and I'm trying to understand how to pump the data into that list - notice i'm using your statement, but having trouble with Sum and matching on the different types and the anonymous types etc.. – Jeremy Miller Dec 03 '20 at 23:43
Checked the edited answer. @JeremyMiller – Afshin Mobayen Khiabani Dec 03 '20 at 23:53
Last one is what you need I think. @JeremyMiller – Afshin Mobayen Khiabani Dec 03 '20 at 23:57
It doesn't like this TotalAmounts = x.Sum(TotalPaymentAmount), - i tried adding it to the new { ... as x.... but it doesn't like it – Jeremy Miller Dec 04 '20 at 00:02
If this solved your problem can you mark me as accepted answer? – Afshin Mobayen Khiabani Dec 04 '20 at 00:07
CS0266 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?) – Jeremy Miller Dec 04 '20 at 00:18
I plan to mark your answer as correct - but having a few issues , that error above and that Sum(TotalPaymentAmount) it is not liking thx again – Jeremy Miller Dec 04 '20 at 00:20
Ok , I added a ToList() that fixed that first error , but how do I do a Sum of the TotalPaymentAmount ? It doesn't like it.. I added to the new part , added x to both places .. still it doesn't like it – Jeremy Miller Dec 04 '20 at 00:24

C# List of properties. Need to group them based on 2 conditions

2 Answers2