Sum the Quantity which holds the same ID c#-CodePudding

There is an ASP.NET MVC application with an Order Data table and the table contains the order part ID and the Order Qty.

So somehow the previous developer set the Order Qty as string type.

Now for a report, I want to get a sum of the Qty according to the same Part Id's and show it, but it won't work.

Can you help me with this?

foreach(var item in rData) {
  try {
    if (db.OrderTable.Any(u => u.PartNo_Id == item.Id)) {

      item.TotalOrderQty = db.OrderTable.Where(x => x.PartNo_Id == item.Id).Sum(x => x.OrderQty);
    }

  } catch (Exception ex) {

    throw ex;
  }

}

the error is cannot implicitly convert type 'string' to 'long' on the (x=>x.OrderQty)

CodePudding user response：

If your db object is an OR-Mapper to a real database you are making a lot of queries which could be optimized, cause for every item in your list you ask at first, if data is available and at second trying to query these informations. It would be more effective to request the data in one shot (if it is not millions of lines) and compute the sums on the client side.

Maybe this sketch can help you:

// Define matching Ids to read from database
var idsToSearch = rData.Select(item => item.Id);

var matchingOrders = db.OrderTable
    // Define criteria, which data has to be fetch.
    .Join(idsToSearch, order => order.PartNo_Id, id => id, (order, id) => order)
    // Prepare data on the server side to be already being grouped.
    .GroupBy(order => order.PartNo_Id)
    // Load data from the server to the client
    .AsEnumerable()
    // Parse and summarize the data on the client side.
    .Select(group => (group.Key, group.SelectMany(order => int.Parse(order.OrderQty).Sum())
    .ToList();

Depending on your model it could make sense to strip down the really needed data between the .Join() and the .GroupBy() call if the table holds a lot of (in this request unneeded) columns. Also be aware, that this code is not tested and maybe contains some stupid typo or similar, but it should give you a good starting point on how to tackle your problem.