ASP.net MVC with Entity Framework reordering database records

Question

I've been battling to find a resource that can help me resolve my issue and it seems to be a rather tricky issue to wrap my head around.

Say I have a table in the DB (100 records) with an integer sequence field (which is incremental). Now I have functionality to reorder the list where the user can drag a item from the bottom say to the middle (e.g. Sequence 100 -> 50).

At the moment I set that records sequence to 50 and every record after will be updated with a 1+ to their sequence. (e.g.50 becomes 51, 51 becomes 52) so on...

To me this is a inefficient way as it must update 50 records just to change the sequence and can take awhile especially if the table grows in size.

Is there any efficient manner to do reordering that does not take the need for updating each record?

Answer 1

You could order your elements based on a linked list structure.

Add a record id and instead of an order id use a next record id.

This presents a similar problem from the other side though. You have to look at each record to determine the order of your elements.

Best practice is to just update all other records.

Answer 2

Give the table a new column called 'order' which is decimal type and the default value is 1 to 100.

When you drag 100 to 50 set this 'order' value to (49+50)/2 so the original 100 will between 49 and 50. And use 'order' for sorting.

Answer 3

I don't see why you find it an issue that you are updating 50 records when this is exactly what the user is doing. Also if the table does grow how could possibly the user reorder that much items in the UI if the computer can't handle it.

If the entities themselves are heavy and you only need to change the numbers then you can write a stored procedure or execute raw SQL via EF for this specific operation.