how to sum previous record with linq and vb.net

Question

I have this table in sql and use vb.net:

Table1s {name, money}

I want get date The following form:

name | money | Sum
aaa  | 10    | 10
bbb  |  5    | 15
ccc  | 15    | 30
ddd  | 15    | 45

Sum Total = Sum (Money)

Code used:

Dim MonySum As Integer = 0
(From a In Db.Table1s.ToList
 Select New With {a.Name, a.Money, .Sum = (MonySum = MonySum + a.Money)}).ToList()

but it's not working.

Answer 1

Although I initially thought, it shouldn't go (simply, because your sum is based on records before it), there is a way to go around it, by simply using a function that returns your current Sum and increases a variable through each new selection.

However, I still think, that this should only be calculated once the order defined, because if your user would like to order by names or by money, the sums will be different as well.

But here is a way you can do it if you want to (I simulated the data so you might need to do a small refactoring):

Module Module1

    Public Class Money
        Public Property Name As String
        Public Property Money As Decimal
    End Class

    Function IncreaseMe(ByRef currentSum As Decimal, value As Decimal) As Decimal
        currentSum += value
        Return currentSum
    End Function

    Sub Main()
        Dim listMoney As New List(Of Money)
        Dim total As Decimal = 0

        listMoney.Add(New Money With {.Money = 10, .Name = "aaa"})
        listMoney.Add(New Money With {.Money = 5, .Name = "bbb"})
        listMoney.Add(New Money With {.Money = 15, .Name = "ccc"})
        listMoney.Add(New Money With {.Money = 15, .Name = "ddd"})

        Dim query = From moneyItem In listMoney
                    Select New With {.Name = moneyItem.Name, .Money = moneyItem.Money, .Sum = IncreaseMe(total, moneyItem.Money)}

        For Each queryItem In query
            Console.WriteLine("{0}{3}{1}{3}{2}", queryItem.Name, queryItem.Money, queryItem.Sum, vbTab)
        Next
        Console.WriteLine("Total money: {0}", total)
        Console.ReadLine()
    End Sub

End Module

In C# it's even a little easier, there you can simply increase the local variable and the result is then added to the sum:

static void Main(string[] args)
{
    IList<MoneyTable> money = new List<MoneyTable>();
    money.Add(new MoneyTable { Money = 10, Name = "aaa" });
    money.Add(new MoneyTable { Money = 5, Name = "bbb" });
    money.Add(new MoneyTable { Money = 15, Name = "ccc" });
    money.Add(new MoneyTable { Money = 15, Name = "ddd" });

    decimal totalSum = 0;
    var query = from m in money
                select new { Name = m.Name, Money = m.Money, Sum = (totalSum = totalSum + m.Money) };

    Console.WriteLine("{0}\t{1}\t{2}", "Name", "Money", "Sum");
    foreach (var item in query)
    {
        Console.WriteLine("{0}\t{1}\t{2}", item.Name, item.Money, item.Sum);
    }
    Console.ReadLine();
}

Answer 2

This has the same approach as the answer from Icepickle, but uses the foreach statement from linq.

var list = new List<TestObject>
                       {
                           new TestObject { Field1 = 1, Field2 = 10, Stringfield = "Object1" },
                           new TestObject { Field1 = 1, Field2 = 10, Stringfield = "Object2" },
                           new TestObject { Field1 = 1, Field2 = 10, Stringfield = "Object3" },
                           new TestObject { Field1 = 1, Field2 = 10, Stringfield = "Object4" },
                           new TestObject { Field1 = 1, Field2 = 10, Stringfield = "Object5" }
                       };

        var subtotal = 0m;
        list.ForEach(x => x.SubTotalField = subtotal += x.Field1);

        Console.WriteLine("Object\tF1\tF2\tTotal");

        foreach (var testObject in list)
        {
            Console.WriteLine("{0}\t{1}\t{2}\t{3}", testObject.Stringfield, testObject.Field1, testObject.Field2, testObject.SubTotalField);
        }