Calculate a total for each record in a list based on filtered list

user_13371707 0
Viewed 71

I have the following sample list:

Index No Path A B C Amount
1 1000 1000 a b c 700
2 1001 1000.1001 a b c 100
3 1001 1000.1001 a b d 200

I need to iterate over the list, and for each record, in the list I need to calculate a value and store it in a new column based on specific conditions:

  • filter records where Path contains record's No
  • filter record with same values in columns A, B, and C
  • calculate a sum of Amount and save it as TotalAmount

To give you an example:

Index No Path A B C Amount TotalAmount
1 1000 1000 a b c 700 800
2 1001 1000.1001 a b c 100 100
3 1001 1000.1001 a b d 200 200

For the first record, I need to find all records in the list which Path contains the No of the record (1000) and with the same values in columns A, B and C. So In this example for the first record, we take records with index = 1 and index = 2, calculate a sum of amount and return it in the column TotalAmount.

I had such idea for this:

foreach (record in List)
{
    var totalAmount = List
        .Where(e => 
            e.Path.Contains(record.No) && 
            e.A == record.A && 
            e.B == record.B && 
            e.C == record.C)
        .Sum(e => e.Amount)
}

However, it doesn't return what I want and I don't know how to save it back to the list after such calculations.

linq c#
3 Answers

Update your result to the current item running in the loop

list.ForEach(record=>{
{
 var totalAmount = list.Where(e => e.Path.Contains(record.No) && e.A == record.A && e.B 
 == record.B 
  && e.C == record.C).Sum(e => e.Amount);

 record.TotalAmount = totalAmount;
});
user_71604820

If you're not getting the proper result from the Sum operation, it's likely due to the Contains method returning records that you don't want (because 10000 contains 1000, for example). One way to handle that would be to add periods when checking the beginning, middle, and end of the value.

The other issue is as a previous answer described - you need to set the TotalAmount property of the record (assuming it has one):

foreach (var record in list)
{
    record.TotalAmount = list
        .Where(e => 
            (e.Path.StartsWith($"{record.No}.") ||
            e.Path.Contains($".{record.No}.") ||
            e.Path.EndsWith($".{record.No}")) && 
            e.A == record.A && 
            e.B == record.B && 
            e.C == record.C)
        .Sum(e => e.Amount)
}
user_20526550

First, rather than putting the TotalAmount on your record type, I recommend creating a new type to represent the result you're looking for.

public record Source(int Index, string No, string Path, string A, string B, string C, double Amount);
public record Totals(int Index, string No, string Path, string A, string B, string C, double Amount, double TotalAmount);

Next, I would create an intermediate representation of your data that's easier to reason with. It sounds like the combination of (A, B, C) has a meaning, and it sounds like your Path is really kind of a collection of the parent elements to the current record.

    var intermediates = sources.Select(
        source => new
        {
            source,
            pathComponents = source.Path.Split('.').ToHashSet(),
            abc = (source.A, source.B, source.C)
        });

Then let's group those values by the combined abc value, so we can quickly look up the items that belong to the same group as a given entry.

    var byAbc = intermediates.ToLookup(e => e.abc);

Finally, calculate the totals:

    var totals = 
        from intermediate in intermediates
        let totalAmount = byAbc[intermediate.abc]
            .Where(e => e.pathComponents.Contains(intermediate.source.No))
            .Sum(e => e.source.Amount)
        let source = intermediate.source
        select new Totals(
                source.Index,
                source.No,
                source.Path,
                source.A,
                source.B,
                source.C,
                source.Amount,
                totalAmount);

Here are a few of the benefits to this approach:

  1. By breaking down the problem into individual steps with immutable behavior:
    1. it's possible to step through the code and visually inspect (or log) the results from each line of code.
    2. it's possible to jump back to a previous step in the debugger and walk through the code again without changing the program's behavior.
    3. the results of the individual steps can be put into named variables that help the reader understand intent.
    4. the individual steps can easily be refactored out to separate methods or classes.
  2. By representing the path as a collection of individual pieces which can be compared using equality checks, we avoid bugs in the Contains logic. For example, if a record had a No of 100, all of the paths in the above example would match it if you used a simple string.Contains check, even though none of the paths actually includes 100 as a component of their path.
  3. By using data structures like Lookup and HashSet, we avoid high asymptotic complexity, which means that this scales well to really large data sets.
  4. By using a different type for the input and output, you prevent the introduction of bugs in your application where someone needs to use the TotalAmount property, but is given a list where that property has not yet been populated.

Here's a LINQPad script putting the whole thing together.

Result:

LINQPad Output

user_1209550
Related