How to sum field results

Epicor ERP 10
,
1

Hi,
I am getting the on hand qty for a part and lot number from each partbin the part might exist in. I would like to add those numbers up to get a total on hand qty for the lot. I think I need to do a foreach loop but I’m not sure what goes next.

var PartBinsTable = Db.PartBin.Where(x=>
                    x.Company == Session.CompanyID &&
                    x.PartNum == NewRecord.Character01 &&
                    x.LotNum == NewRecord.Character02).Select(x=>new{x.OnhandQty});
                    
                    foreach(var PartBinRow in PartBinsTable)
2

In sql you’d want to use group by and sum. In Linq, syntax isn’t as straightforward. I usually have to google it:

3

From this part, you already have a list of all the OnHandQty values for your Part and Lot. (Each one is stored as an object with a single property … with a single-value result, you can just return the field you want: Select(x => x.OnHandQty) for a simpler result set.)
You can add a Sum to the end of your query to get a single decimal value (at least, I think OnHandQty is a decimal type).

It looks like this:

.Sum(obj => obj.OnHandQty);

Or if you change your Select to just pick the field itself, you can just sum the list of numbers:

.Sum();

Another option: The Sum function has the capability to Select the value to sum, so instead of Select then Sum, your whole query could look like this:

decimal thisOnHandQty =
  Db.PartBin
    .Where(x=>
      x.Company == Session.CompanyID &&
      x.PartNum == NewRecord.Character01 &&
      x.LotNum == NewRecord.Character02)
    .Sum(x => x.OnhandQty);
1 Like