Partition By Logic in Code to calculate value of a DataTable Column-CodePudding

I'm using the following SQL for calculating the value of a column named weight within a view. I need to move this calculation logic to code.

CASE
WHEN SUM(BaseVal) OVER (PARTITION BY TEMPS.MandateCode) = 0 THEN 0
ELSE (BaseVal / (SUM(BaseVal) OVER (PARTITION BY TEMPS.MandateCode))) END AS [Weight]

Is iterating over each and grouping by MandateCode a good idea

 var datatableenum = datatable.AsEnumerable();
            foreach(var item in datatableenum)
            {
  List<DataTable> result = datatable.AsEnumerable()
             .GroupBy(row => row.Field<int>("MandateCode"))
             .Select(g => g.CopyToDataTable())
             .ToList();
}

CodePudding user response：

I'm going to say "no" because as you have it, it will perform the group operation for every mandate code, for each row then copy then to list, which adds up to a huge amount of burnt resources.. I would make a dictionary of mandatecode=>sum first and then use it when iterating the table

var d = datatable.AsEnumerable()
  .GroupBy(
    row => row.Field<int>("MandateCode"),
    row => row.Field<double>("BaseVal")
  ).ToDictionary(g => g.Key, g => g.Sum());

Note I've no idea what type BaseVal is; you need to adjust this. If it's an integer remember that you'll be doing a calc of small_int/big_int eg 12/6152, which is always 0 so cast one of the operandi to eg double so the result will be like 0.1234

Then use the dictionary on each row

foreach(var item in datatableenum)
{
    int sumbv = d[item.Field<int>("MandateCode"));
    item["Weight"] = sumbv == 0 ? 0 : item.Field<double>("BaseVal") / sumbv; 
}