change a sort order field in a table using entity framework 6
I have a table with three fields: Id, location, sortorder.
Id location sortorder -- -------- --------- 1 a 1 2 b 2 3 c 3 4 d 4
I want to the user to be able to amend the sort order on the items in the table. I'm using EF to write to the database, is there any way of amending the sort order on the table without having to loads of calls to the database.
If I move an item to the top of the list from the bottom I would need to update all the rows that were underneath that new row, to move them down the order. If possible I would like to avoid n updates to the database, and just do it in the least number possible.
Is this possible?
I believe Gert's suggestion of using floats for sort order is probably the best one to go with. Drupal uses weights of menu items for the same purpose but inserts at increments of 100 or 1000 so you can go between things. I think that it also can run a cron job to respace the ordering so you don't run out of numbers in a more efficiently stored data type but that sounds like a holdover from my BASIC days in middle school where you had to do that with line numbers.
Also, I would wager that it isn't actually as awful as running n updates because it's instead doing one update that affects n rows. Yes, at the end of the day it does have to change n rows but that's on the DB side so there are tons of efficiencies that can be implemented to speed it up.