IList<Item> Collection Class accessing database

Question

I have a database with Users. Users have Items.

These Items can change actively. How do you access the items in a collection type format? For the user, I fill all the user properties at the time of instantiation. If I load the user's items at the time of the instantiation, and the items change, they will have old data.

I was thinking, maybe I need an ItemCollection class and have that a field/property apart of the user class, that way to traverse all the user's items I could use a foreach loop.

So, my question is, what is the best practice/best way of accessing the items from a database using some sort of collection? On accessing the particular Item, it needs to get the latest database information, and when the user does do a foreach loop, the latest item information must be available.

I.e. What I'm trying to do

Console.WriteLine(User.Items[3].ID); returns 5.
//this updates the item information and saves it to the database.
User.Items[3].ID = 13; 

//Add a new item to the database.
User.Items.Add(new Item { id = 17});

foreach (Item item in User.Items) {
    //this would traverse all items in the database.
    //not some cached copy at the time of instantiation of the user.
}

Edit: Sorry, I've written this question a little wrong. I'm using Linq currently, but I have Classes mapping to business objects. When I do a traverse on a business object, I would like it to go to the Linq datacontext and get the information.

When I implement a new class "ItemCollection" which implements interface IList, what do I need to do to get it returning Items?

With the User.Items[3], is implemented in the public Item this[int index] method. Should the index method use the linq.Skip operator to get the index of the item in the database?

I'm just a little confused about mapping business object collections to database.

Answer 1

While this is possible to do, it would require some unusual requirements to be necessary.

First of all, this would produce an excessive amount of traffic between your database and your application. Second, in a concurrent environment, this will introduce a large amount of race conditions.

Just for a quick example, the code segment:

User.Items[3].ID = 13; 
User.Items[3].Color = Color.Blue; 

Could produce states which are not allowed, however it exists in the database for at least a moment because of the eagerness to persist state. Also the above code segment would access the database twice in order to make a simple update. And there it is much more difficult to guarantee that this change will be atomic.