Caching Linq2Sql data

I have a web application. It does a TON of data reads on small tables and a few writes. I want to pull entire tables of data into cache, and query the cached data. I override the context's Table<T>() method so we can serve cache instead of live data. Works great for inserts and supports unit testing data access as well. Awesome.

So we started using cache data and discovered that updates and deletes don't work.

We pull an object from db, throw it into a List<T>, put that list in the httpCache, then drop the data context. 开发者_StackOverflow社区

Two requests later, we call the overridden Table<T>, it gets the list, and pulls the object out. We change it and then need to attached the cached, then changed object to the db context. So we call myContext.Attach(myobject) and.....BOOM

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

Is there a way around this limitation?

It sounds like your cached entities are still attached to the original DataContext that they were queried with. One way around this (that breaks the best-practice rule of keeping the DataContext life-cycle short) is to cache the DataContext along with your entities, and then use that cached context when you want to delete or update the cached entities.

You mention that the cached entities are used "two requests later" - if those requests all occur within a short timespan, then breaking short-life-cycle rule might not be an issue.

Another option is to cache objects that are based on entities but that are not entities, and when it's time to update entities with values stored in the cached non-entity objects, query clean entities on a new DataContext, update those entities and save then on the newly-created DataContext.

There are yet other ways to do it - I've found the book LINQ in Action to be very helpful in dealing with cases like these.