Order rows with Entity Framework

Question

I'm using EF in order to insert and retrieve info from DB, there is any way to insert new row but at the specified position,

Like i have 10 rows with IDs ranging from 0 to 9 and new row i'm inserting will be on the position 4?

I'm using ASP.NET MVC 5 and LINQ.

Thank you.

If you are using an Identity in the database then no you can't. What you can do is to add another column [Sequence]. Set you sequence as you prefer then sort by that field. — Moe, Nov 10 '15 at 20:54
Is `ID` auto-increment? If so, then [you shouldn't do that](http://stackoverflow.com/questions/12969161). If not, why can't you just set the `ID` to `4`? — D Stanley, Nov 10 '15 at 20:54
Are you talking about position, i.e. Id in db table or row in a grid on the page? — LocEngineer, Nov 10 '15 at 21:01
But i dont want it just to order by, but to change a position of items. But all items is stored in database, and for now the order way is ID of rows — Anton Selin, Nov 10 '15 at 21:04

score 1 · Answer 1 · answered Nov 10 '15 at 20:54

The simple answer is no. Order has no meaning unless it's explicit in a database system. Sure in most cases I can insert into a table and pull from this exact table and get the exact order as it was inserted, but this is undefined...and the only guarantee is to use an ORDER BY clause.

If you are talking about changing an auto number property, this is also not possible, the database does not go back and fill in gaps with id numbers. If numbering is critical and important to you don't set the auto-increment property.

score 0 · Answer 2 · answered Nov 10 '15 at 20:56

0

Your ID and order position are different things.

For ID you use an autonumeric and you shouldnt mess with that.

For order you use another column and run a trigger when a new row is insert update all the rows

So when the new row is inserted with order_id = 4 all the rows get update

something like

 UPDATE  table
 set order_id = order_id +1
 when order_id >= 4

answered Nov 10 '15 at 20:56

Juan Carlos Oropeza

47,252
12
78
118

This presents issues on deletes for instance. You'd have to pick up the current order_id and negate one from it for any existing rows coming after the row being deleted. – JonH Nov 10 '15 at 20:57
@JonH You are right but is a place to start. In that case you also need a delete/ update trigger – Juan Carlos Oropeza Nov 10 '15 at 20:58

score 0 · Answer 3 · answered Nov 10 '15 at 21:13

So, I would do so quickly: I would plan the database to not auto increment primary key and saving would so that the id is attributed according to the specific location. Obviously put an IF to verify that it is available, and if I would start a review cycle to the cascade of subsequent ID or positioning the value traded in the end.

for example

MyTable table = myDb.MyTable.Find(id);  //position
if (table==null)
{ table.id=position; table.Field=value; myDb.SaveChanges() }  
else
{
var temp = table.id;
var max = table.count(x=> x.id).value;
table.id=max+1;myDb.SaveChanges();
table.id=id; table.Field=value; myDb.SaveChanges();
}

sorry if translate is no good! ;-)

Order rows with Entity Framework

3 Answers3