Renumbering database rows is inefficient

Question

My database has a Task table with a Sequence column. The Sequence column specifies the order of the tasks.

In some cases, I need to change the order. So I would use something like this:

var tasks = (dbContext.Tasks
    .Where(t => t.UserId == userId)).ToList();

for (int i = 0; i < tasks.Count; i++)
{
    // Set new sequence
    tasks[i].Sequence = i;
}

dbContext.SaveChanges();

This seems rather inefficient to have to retrieve every column of every Task in the set.

Is there a more efficient way to do this?

Note: Please don't get caught up in the fact that I'm simply setting Sequence to i in the code above. The real code will have data receive data that indicates the correct values. But if I could optimize the code above, I could then adapt it to my final needs.

Possible duplicate of [How to update only one field using Entity Framework](https://stackoverflow.com/questions/3642371/how-to-update-only-one-field-using-entity-framework) — Rufus L, Jan 08 '20 at 00:40
Sorry, forgot my single vote to close would close the question. Please let me know if I got it wrong! — Rufus L, Jan 08 '20 at 00:43
@RufusL: The technique shown for that question creates the data in memory. My code needs to update the `Sequence` column. That means I first need to read that column. So the proposed answer doesn't really address my question. — Jonathan Wood, Jan 08 '20 at 00:57
@stuartd: If you look closer at my code, I'm updating the `Sequence` column. I'm not sorting the results. — Jonathan Wood, Jan 08 '20 at 00:58
Right, you would add a `.Select(t => t.Sequence)` to your query to pull in only that column, then use those results to update the database. But I don't mind re-opening the question. — Rufus L, Jan 08 '20 at 01:01
@RufusL: Looks like I reopened the question. I don't quite understand where this `Select()` would go. The answers for that question simply create an in-memory object. Using a `Select()` instead would change the entire thing. I guess I'm not following you. — Jonathan Wood, Jan 08 '20 at 01:04
`var tasks = dbContext.Tasks.Where(t => t.UserId == userId).Select(t => new Task { UserId = userId, Sequence = t.Sequence });` might work - I can't test it right now. I think this will just query the single column, and then you can use the answer in the possible duplicate to update just that column. — Rufus L, Jan 08 '20 at 01:08
@RufusL: I see. I can play with that. I'm a little unsure about what exactly would get saved when I call `SaveChanges()`. Thanks. — Jonathan Wood, Jan 08 '20 at 01:10
For efficient approach, you should push the data to server at once (bulk insert or TVP) and update the table with single update command. I am not sure if EF can do that. — Antonín Lejsek, Jan 08 '20 at 01:18
I'll post what I *think* would work, but I'm not certain about it. — Rufus L, Jan 08 '20 at 01:18

Rufus L · Accepted Answer · 2020-01-08T16:55:14.697

You should be able to pull down only the column you want to update by using a Select statement, and then, according to this answer, update just that column.

This example might work, but unfortunately I can't test it right now:

// Query just a single column
var tasks = dbContext.Tasks
    .Where(t => t.UserId == userId)
    .Select(t => new Task { UserId = t.UserId, Sequence = t.Sequence })
    .ToList();

// Update a single column and tell EF to track it
for(int i = 0; i < tasks.Count; i++)
{
    tasks[i].Sequence = i;
    dbContext.Attach(tasks[i]);
    dbContext.Entry(tasks[i]).Property(t => t.Sequence).IsModified = true;
}

// Save the changes to that column
dbContext.SaveChanges();

This seems to work once I added the PK to the `Select()` clause. Unfortunately, it appears to require separate queries for each row but perhaps there's no way around that. — Jonathan Wood, Jan 08 '20 at 15:07

Renumbering database rows is inefficient

1 Answers1