Entity Framework ensure objects get inserted in order

c# entity-framework-6 sql-server

Question

I have a table Rules on my database. I insert rules like:

Rule[] rulesToInsert = // some array of rules to insert

using(var db = new MyEntities())
{
     foreach(var rule in rulesToInsert)
         db.Rules.Add(rule);
     db.SaveChanges();
}

When I retrieve later the rules that I have just added I notice they are in a different order. What is the best way to retrieve them in the order I added them? Should I call db.SaveChanges() every time I add a new rule? Or should I add a new column called SortOrder? Why are the items not being added in the order I added them?

Edit

The id is a guid (string) because one rule can have other rules. In other words I am creating a tree structure. (The rules table has a foreign key to itself). It was crashing when I used the primary key as an integer and it autoincremented so I just used a guid instead. I guess I will add a separate column called sort order.

1
3
11/17/2015 8:12:04 PM

Accepted Answer

If you want your items to be inserted in the order you add them in the foreach statement, you have to make a big compromise, to call the db.SaveChanges in each iteration.

foreach(var rule in rulesToInsert)
{
    db.Rules.Add(rule);
    db.SaveChanges();
}

I say that's a big compromise, because for each rule you have to insert you have to make a round-trip to the database, instead of doing only one round-trip as in your original code.

One possible workaround, it would be to add an extra column in the corresponding table in your database, that would hold the information of order. If you do so, you could add one more property in the rule object and refactor a bit your code. Then you will have the expected result.

-1
11/17/2015 8:13:16 PM

Popular Answer

Tables have no sort order (new rows are not guaranteed to be added to the end or any other place). The only safe way to retrieve rows in any particular order is to have a query with Order by.
So yes you will need to add a SortOrder column. (Can just set it as an identity column.)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow