CMedina CMedina - 5 months ago 18
Vb.net Question

Exclude values to sum column grouped LinQ

I have my DataTable:

╔═══════════════╦═══════════════╦═══════════════╗
║ CodTipoMoneda ║ idTipoCuenta ║ Saldo ║
╠═══════════════╬═══════════════╬═══════════════╣
║ 002 ║ 10 ║ 854 ║
║ 003 ║ 21 ║ 500 ║
║ 002 ║ 10 ║ 500 ║
║ 002 ║ 22 ║ 680 ║
║ 003 ║ 20 ║ 130 ║
║ 002 ║ 30 ║ 10 ║
║ 003 ║ 12 ║ 2 ║
╚═══════════════╩═══════════════╩═══════════════╝


I need sum the
Saldo
column group by
CodTipoMoneda
, I have this code (It's Working!).

Dim query = From row In oDT.AsEnumerable()
Group row By Moneda = New With {
Key .CodTipoMoneda = row.Field(Of String)("CodTipoMoneda")
} Into NameGroup = Group
Select New With {
.TipoMoneda = Moneda.CodTipoMoneda,
.Saldo = NameGroup.Sum(Function(r) r.Field(Of Decimal)("Saldo"))
}


but also I would like to add
where
condition to exclude some values from
idTipoCuenta
, for example exclude values
10 and 20
from
idTipoCuenta
.

╔═══════════════╦═══════════════╦═══════════════╗
║ CodTipoMoneda ║ idTipoCuenta ║ Saldo ║
╠═══════════════╬═══════════════╬═══════════════╣
║ 002 ║ 10 ║ 854 ║ (not sum)
║ 003 ║ 21 ║ 500 ║
║ 002 ║ 10 ║ 500 ║ (not sum)
║ 002 ║ 22 ║ 680 ║
║ 003 ║ 20 ║ 130 ║ (not sum)
║ 002 ║ 30 ║ 10 ║
║ 003 ║ 12 ║ 2 ║
╚═══════════════╩═══════════════╩═══════════════╝


I do not know much about, any help is apreciated!

Answer

Use the Linq Where function:

Dim query = From row In oDT.AsEnumerable()
            Group row By Moneda = 
            New With 
            {
                Key .CodTipoMoneda = row.Field(Of String)("CodTipoMoneda")
            } Into NameGroup = Group
                   Select New With 
                   {
                         .TipoMoneda = Moneda.CodTipoMoneda,
                         .Saldo = NameGroup.Where(Function(n) n.Field(Of Decimal)("Saldo") <> 10)
                                           .Sum(Function(r) r.Field(Of Decimal)("Saldo"))
                   }

My VB is a little rusty so feel free to adjust accordingly.

Comments