Subtraction of repeating values from Datagrid

Question

I have the following Datagrids as shown in the picture below.

I have the sales of 3 similar Items bearing the same ItemCode in Sales Datagrid whose sum amounts to 4.

The current stock of that particular Item is 20.

When I click the validate button, I'm using the following query to subtract the Sales from Current Stock and hence updating the Transaction History Datagrid.

Query :

var changes = (from dr1 in dtItemSales.AsEnumerable()
               from dr2 in dtItemStock.AsEnumerable()
               where dr1.Field<string>("ItemCode") == dr2.Field<string>("ItemCode")
                       select new
                       {
                           Name = dr2.Field<string>("ItemCode"),
                           Remarks = dr2.Field<int>("Qty") - dr1.Field<int>("Quantity")
                       }).Distinct().ToList();


//Updating the Transaction History DataGrid.
dgTransactionHistory.ItemsSource = changes;

My problem here is that when I am subtracting the Sales from Current Stock I am getting 2 records bearing the same ItemCode with 2 different values 19 and 18.

I should be getting only 1 record with the value 16 in this particular case. (20 - 2 - 1 - 1)

Any help regarding this query would be much appreciated. Thank you.

Answer 1

I should be getting only 1 record with the value 16 in this particular case. (20 - 2 - 1 - 1)

First try to get the sum of Quantity from dtItemSales with particular ItemCode that matches to dtItemStock item code.

And then subtract the above sum with dtItemStocks Qty column like

So your final query look like

var changes = (from dr1 in dtItemSales.AsEnumerable()
               from dr2 in dtItemStock.AsEnumerable()
               where dr1.Field<string>("ItemCode") == dr2.Field<string>("ItemCode")
               let sum = dtItemSales.AsEnumerable().Where(x => x.Field<string>("ItemCode") == dr2.Field<string>("ItemCode")).Sum(dr => dr.Field<int>("Quantity"))
               select new
               {
                   Name = dr2.Field<string>("ItemCode"),
                   Remarks = dr2.Field<int>("Qty") - sum
               }).GroupBy(x => x.Name).Select(x => x.First()).ToList();

Output from Debugger: