IList<Item> Collection Class accessing database

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 conf开发者_StackOverflow社区used about mapping business object collections to database.

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.

Have you checked out LINQ to SQL or the Entity Framework?

They both provide similar functionality to what you need:

// Get first user with Id of 5
var users = _dbContext.Users.FirstOrDefault(u => u.Id == 5);

// Loop through all users in the context
foreach(var user in _dbContext.Users)
{
    foreach(var item in user.Items)
    {
        // Work with your item here.
    }
}

You just have to make sure you manage your contexts so that you have the latest info.

You need to implement IList in your database classes, and also write to the database whenever a property is changed.

Having IList access to the data might be good in some situations, but automatically writing to the database whenever someone changes a property will create unpleasant situations when someone changes 20 properties on an instance.

When it comes to the best way to access data in a collection-like way, look at LiNQ.

You want an Object-Relational Mapping tool.

I use and would recommend ADO.Net Entity Framework.

Most ORMs include a caching layer. And that's pretty much what you're beginning to write.

A big benefit is that that caching layer you're now designing has been built for you and most likely will outperform your ( or my ) implementation.

Specifically for Entity Framework, the main component that comes into play is the "Object Services" component

From "Saving Changes and Managing Concurrency"...

Object Services tracks changes that have been made to objects in the cache. When the SaveChanges method is called, Object Services tries to merge changes back to the data source. SaveChanges can fail with an OptimisticConcurrencyException when data changes in the object cache conflict with changes that were made in the data source after objects were added to or refreshed in the cache.

So this is handle mostly for you behind the scenes. And as your Entity Set grows, you don't have to modify your object caching layer as it's all done for you.