LINQ to SQL Tips 6: How to refresh given DataContext won't stomp on your objects

  • Article

The DataContext is a rather careful beast. Once an object is retrieved, the DataContext will not stomp on it if a query returns the same object again. This is intentional. Imagine the chaos if you modified some of the retrieved objects or even read the values and made some decision based on that and then the results of a subsequent query just stomped all over that object with newly changed values from the database.

But that does create a separate problem: if you want to get the new values from the database and want your object overwritten, simply querying will not do. DataContext will faithfully execute the queries and bring back the rows with new data for old objects but the rows will be effectively discarded if an object with the same id exists in the cache.

The Refresh() method could be used. It is primarily designed for optimistic concurrency conflict resolution and requires you to state what objects you care to refresh. If you are using it, keep in mind that the changes in the database for a given query can be:

  1. Newly inserted objects
  2. Updates of existing objects
  3. Deleted objects that were present in a previous execution of the same query

Issuing the same query with the same DataContext takes care of the first. Refresh() can handle #2. But #3 requires a little more thought and computation. You could certainly use another DataContext to compare results. But ...

Why is there always a "but"? It is there because if you find your design getting too complicated, it is time to revisit the problem and question the scenario. Are you really using the DataContext lifetime wisely? Why are you keeping the DataContext instance for so long that you are getting into these issues? Is it time to toss the old DataContext instance and create a new one (e.g. to periodically recycle reference data - maybe once every n hours)? Is it time to use two different instances for reference data vs transactional data?

I can't answer these questions for your app. But if you think about them, you will likely have a better design. And maybe you won't need a query refresh! In the design of LINQ to SQL, we often pulled back from complex designs when we felt the designs were way off the main road wandering in the woods. More about that design philosophy followed in the C# design meeting some other day. Meanwhile, hope you find some of the questioning refreshing :-)

Dinesh