Entity Framework ensure objects get inserted in order

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.

Answer 1

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.