How to iterate over database records and update every x rows and then reset and repeat?

Question

I am returning the below record set. What i am trying to accomplish is the data posted below. So, I need to update every numberUp records with a formNum that increments. The problem is in this case is that every third record gets skipped it is the third iteration when the code checks if idx == numberUp. What am I missing here? Any help would be greatly appreciated.

DATA BEFORE

Quantity    TransitDays Form    NumberUp
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2
1750        3           0       2

DATA AFTER

Quantity    TransitDays Form    NumberUp
1750        3           1       2
1750        3           1       2
1750        3           2       2
1750        3           2       2
1750        3           3       2
1750        3           4       2
1750        3           5       2
1750        3           5       2
1750        3           6       2
1750        3           6       2
1750        3           0       2

numberUp = 2;
var formNum = 1;
var idx = 0;

foreach (var qty in quantities)
{
    for (int i = transitdays.Count; i > 0; i--)
    {
        var query = (from runlist in db.Runlists
                     where runlist.UserId == user.Id && runlist.Quantity == qty.Quantity &&
                           runlist.TransitDays == i && runlist.Form == 0
                     orderby runlist.TransitDays descending
                     select runlist).ToList();


        if (query.Count >= numberUp)
        {
            foreach (var item in query)
            {
                if (idx < numberUp)
                {
                    item.Form = formNum;
                    db.SaveChanges();
                    idx++;
                }
                else
                {
                    formNum++;
                    idx = 0;
                }
            }
        }
    }
}

CreateTable(
    "dbo.Runlist",
    c => new
        {
            Quantity = c.Int(nullable: false),
            TransitDays = c.Int(nullable: false),
            Form = c.Int(nullable: true),
            NumberUp= c.Int(nullable: true)
        })

A rule of thumb for working with databases: avoid database operations in client code: it's both extremely inefficient and error-prone. Create an SQL (or maybe LINQ) query which does what you need, and call it. — , Sep 10 '19 at 01:11
Could you give an example code of what you suggest that would accomplish this? I am using LINQ posted above. — SGekko, Sep 10 '19 at 01:13
`db.SaveChanges();` on foreach loop doesnt feel good, it will execute the update query immediately for every single item you have. anyway, if you want to batch the items to like every hundred of them, you could [group](https://stackoverflow.com/questions/23921210/grouping-lists-into-groups-of-x-items-per-group) them first, then do the save changes on each of the groups. or just do a counting in general. — Bagus Tesa, Sep 10 '19 at 01:20
@BagusTesa It is likely a single SQL statement could do **all** of the changes - but we can't be sure until we see the `CREATE TABLE` and the expected results. — mjwills, Sep 10 '19 at 01:21
sorry, im too focused on: **What i am trying to accomplish is to iterate over each row and after every 2 rows update the FORM field with a 1**. — Bagus Tesa, Sep 10 '19 at 01:21
The image above is the recordset returned from my LINQ query. The variable numberUp drives the number of rows that need to be updated at a time. numberUp could be 2,3 or 4. In my case it is a 2 so in my case every two records need to have the same number in the FORm column. Example from top down the first two rows would have 1,1 in the FORM field. Next two rows would have 2,2 and so on. — SGekko, Sep 10 '19 at 01:30

score 0 · Answer 1 · answered Sep 10 '19 at 01:17

0

you don't need those 2 many variables.

    int ctr = 0;
    foreach (var item in query)
    {
        if (ctr % 2 = 0)
        {
            item.Form = ctr;
            db.SaveChanges();               
        }
        ctr++;
    }

answered Sep 10 '19 at 01:17

Ed Bangga

12,879
4
16
30

How to iterate over database records and update every x rows and then reset and repeat?

1 Answers1