In a generic List<T> of objects, should I store the 'OrderID' inside each object, or just rely on the order in the list?

Question

As the title says :)

In my database the table I'm using has an OrderID field, which determines the order (strangely enough!) that the rows will be shown in when they are outputted.

In the C# code this table of data is loaded in as a generic list (List) which each item in the collection being a copy of a row from the database table.

So should I have a OrderID field within each of the items in my list and use that for ordering. Or should I just use the OrderID from the database to determine the order of the objects in the list.

To create an OrderID property, or not, that is the question

Clarification: When the List is populated, the user will add and remove items as well as change the order of existing items. Then this data will be saved back to the database.

Answer 1

Yes, include the OrderID field on the items themselves. It's safer and more flexible.

For example, you can add and remove items, re-sort the list etc on the client-side without worrying about losing track of the correct OrderID for each item. You can also use an item outside of the context of the list without needing to track its OrderID separately.

Answer 2

Are they always going to be ordered in the same way? For example, might a user be able to re-order alphabetically, and then require you to go back to the original order? If so, it makes sense to retain the OrderID.

Likewise, if you need to be able to add a new item you'll need to be able to position it appropriately.

Personally I'd probably fetch the OrderID even if you don't need it right now, just on the grounds that it's hardly any extra effort and gives greater flexibility later - assuming you're not hugely memory critical. I'd still get the database to do the ordering when you fetch though, rather than fetching and then sorting the collection.

Answer 3

I would prefer that my objects should be complete whether they are in the list or not - consider pulling one item from the list and using it elsewhere, its ID is probably important. So yes, a property for ID.

Answer 4

I would recommend that you create another column in the database for arranging your items instead of using the OrderID - which is, I'm assuming here, may be used as a row identifier/ primary key for your database.

I'll give you a scenario.

Say you have the following items:

• Order ID 1- Orange
• Order ID 2 - Banana
• Order ID 3 - Apple

And you have added another item:

• Order ID 4 - Grapes

What if the user deletes Order ID 2 - Banana, and would want Order ID 4- Grapes to replace the deleted item in your list, how are you going to sort it by then? At least if you add another column, then you can preserve the sorting order for your items without touching your Order ID.

On the other hand, if the Order ID is not the primary key for your table and you have a different primary key, then by all means use the Order ID as your sorting column. Make sure that you implement unique index for the sorting column so that it could enhance your db as well as application performance.

To make your sorting a lot easier in .NET, you can use the classes in System.Collections.Specialized Namespace to sort your lists and want it for strongly-typed collections, i.e. Dictionary, OrderedDictionary, etc. because you will be using a key/value pair if you are implementing a sort order column.

Hope this helps!