List<classObject> group by on the objects in C#

Question

I have a requirement to group a list of class objects based on another list inside the Object.

 class TransactionObject
{
    
    public int ProjectId { get; set; }
    public string uniqueId { get; set; }
    public string OrgNumber { get; set; }
    public string OrgName { get; set; }

    public List<TransactionValue> TransactionValue{ get; set; } = new List<TransactionValue>();
    public class TransactionValue
    {
        public DateTime TrnDate { get; set; }
        public decimal EURTrans { get; set; }
        public decimal LocaTrans { get; set; }
        public decimal BrokeragePercentage { get; set; }


    }
}

Now on this class, I have created a list of objects.

  var TransactionList = new List<TransactionObject>();

I want to get the list of Unique ProjectsIdm OrgName and The sum of EUR Trans, Local Trans, based on a Group by on TrnDate. Example:

ProjectId   OrgName         Trn Date    EUR Trns    Local Trns 
543332      Organization 1  1-Jan-22    100         150
543332      Organization 1  1-Jan-22    150         20

I Need : Sorry MY BAD I edited the correct output i require

ProjectId     OrgName           Trn Date    EUR Trns    Local Trns 
543332        Organization 1    1-Jan-22    250         170

---

What I tried :

List<TransactionObject> result = TransactionList .GroupBy (g => new {
                 g.HoldingName, g.TransactionValues.First().TrntDate  })
                  .Select(g => g.First())
                    .ToList();

I tried this, but it doesn't help me with the Sum of the columns, I am good in Java but new to C# please help me out. I have already crossed my deadline on this object.

Answer 1

I think it could be beneficial to split the operation into 2 stages.

var flattened = TransactionList
            .SelectMany( 
                collectionSelector: o => o.Transactions,
                resultSelector: (fullObject, transaction) => new { fullObject.ProjectId, fullObject.OrgName, Transaction = transaction });

        
var grouped = flattened        
            .GroupBy (t => new {t.ProjectId, t.OrgName, t.Transaction.TrnDate })
            .Select( g => new 
            { 
                g.Key.ProjectId,
                g.Key.OrgName,
                g.Key.TrnDate,
                SumEURTrans = g.Sum( t => t.Transaction.EURTrans), 
                SumLocaTrans = g.Sum( t => t.Transaction.LocaTrans)
            })
            .ToList();


foreach (var t in grouped)
{
    Console.WriteLine($"{t.ProjectId}\t{t.OrgName}\t{t.TrnDate}\t{t.SumEURTrans}\t{t.SumLocaTrans}");
}

This produces

543332  Organization 1  1-Jan-22    250 170
543332  Organization 1  2-Jan-22    450 470
543333  Organization 1  1-Jan-22    250 170

for the example input of

var TransactionList = new [] {
    new TransactionObject
    {
        ProjectId = 543332,
        OrgName = "Organization 1",
        Transactions = new List<TransactionObject.TransactionValue>
        {
            new TransactionObject.TransactionValue
            {
                TrnDate = "1-Jan-22",
                EURTrans = 100,
                LocaTrans = 150
            },
            new TransactionObject.TransactionValue
            {
                TrnDate = "1-Jan-22",
                EURTrans = 150,
                LocaTrans = 20
            }
            ,new TransactionObject.TransactionValue
            {
                TrnDate = "2-Jan-22",
                EURTrans = 200,
                LocaTrans = 250
            },
            new TransactionObject.TransactionValue
            {
                TrnDate = "2-Jan-22",
                EURTrans = 250,
                LocaTrans = 220
            }
        }
    },
    new TransactionObject
    {
        ProjectId = 543333,
        OrgName = "Organization 1",
        Transactions = new List<TransactionObject.TransactionValue>
        {
            new TransactionObject.TransactionValue
            {
                TrnDate = "1-Jan-22",
                EURTrans = 100,
                LocaTrans = 150
            },
            new TransactionObject.TransactionValue
            {
                TrnDate = "1-Jan-22",
                EURTrans = 150,
                LocaTrans = 20
            }
        }
    }
};

This will group by {t.ProjectId, t.OrgName, t.Transaction.TrnDate} across all objects and you need to decide if that's what you want (an example alternative being grouping only within each TransactionObject).

Answer 2

If I understand you right - you can do it this way in one run:

public void GroupTransactions(TransactionObject[] transactionObjects)
{
    var results = transactionObjects
        // build a flat list of values to have both transactionObject and value for every transactionValue
        .SelectMany(obj => obj.TransactionValues.Select(value => (obj, value)))
        .GroupBy(tuple => new {tuple.obj.ProjectId, tuple.obj.OrgName, tuple.value.TrnDate})
        // Get sums for every group. You can use '.Aggregate()' method instead of this custom GetSum but it seems less readable to me.
        .Select(group => (group.Key, sum: GetSum(group.Select(tuple => tuple.value))))
        .ToArray();

    foreach (var result in results)
        Console.WriteLine($"{result.Key.ProjectId} {result.Key.OrgName} {result.Key.TrnDate} {result.sum.euro} {result.sum.local}");
}

private static (decimal euro, decimal local) GetSum(IEnumerable<TransactionObject.TransactionValue> values)
{
    decimal euro = 0, local = 0;
    foreach (var value in values)
    {
        euro += value.EURTrans;
        local += value.LocaTrans;
    }
    return (euro, local);
}