How to run proration using ASP.NET MVC 5 and ASP.N

Question

Here are two tables,

amounttbl,   amountId - amountValue
requesttbl   reqId - reqAmount - Rate - soldAmount, amountId

Data:

 amounttbl
 amountId | amountValue
 ---------|------------
        1 | 5000

 requesttbl
 reqId | reqAmount | Rate | soldAmount | amountId
 ------|-----------|------|------------|---------
      1|       2000|    12|           0|       1
      2|        500|    12|           0|       1
      3|       1000|    11|           0|       1
      4|        500|    10|           0|       1
      5|       1000|    10|           0|       1

For this, I have prepared an action that should sell the amount 5000, from a high rate and to a low rate, which here it will sell from (1-2-3) total will be 4500 it will remain 500. Now it should distribute for (4 & 5). First, it should do it for:

4-- 500/1500 = 0.33 and for 5 -- 1000/1500 = 0.66

4 will get 0.33 percent of 500 and 5 will get 0.66 percent of 500.

For this I have created an action, but it has some problems:

id = 1;
amount = 5000;
var requests = db.request.Where(a => a.amountId== id).OrderBy(a => a.Rate);
foreach(var item in requests)
{
    decimal soldamount = db.request.Where(a => a.amountId== id).Sum(a => a.reqAmount);
    decimal available= amount - soldamount ;
    while (available>= item.reqAmount)
    {
        item.soldAmount= item.reqAmount;
    }
}
db.SaveChanges();

Here I am facing two problems, one inside the foreach:

There is already an open DataReader associated with this Command which must be closed first.

It is because:

decimal soldamount = db.request.Where(a => a.amountId== id).Sum(a => a.reqAmount);

And I don't know how to calculate the record 4, 5 and set their part.

Answer 1

Well, to be frank I don't get your algo but as for open DataReader issue try this code:

id = 1;
amount = 5000;

//Get list of objects from db (assuming db is `DbContext` and request is `IDbSet` stuff)
var requests = db.request.Where(a => a.amountId== id).OrderBy(a => a.Rate).ToList();

//we already have requests with amountId == id in *requests* variable above.
//db.request.Where(a => a.amountId== id).Sum(a => a.reqAmount);

//This does not change during loop, so calculate before hand
var soldamount = requests.Sum(a => a.reqAmount);

decimal available = amount - soldamount;

foreach(var item in requests)
{
    //don't get concept of while here as you are not changing either
    //item.reqAmount not available
    if(available>= item.reqAmount)
    {
        item.soldAmount= item.reqAmount;
    }
}
db.SaveChanges();