Linq doesn't recognize changes in the database

Question

Hi, I did not find something simular so i have to ask:

I use linq to SQL and all was fine until i started to use Stored Procedures to update the database entries. (My Stored Proc is something like update all entries which groupid x) The Update runs fine and the values in the database change. But the DataContext ignores this changes.

I have to say that the data context is a singlton which i know is nocommon way but i have diffrent reasons why i have to do it like this.

so

db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues);

doesn't help.

way does he dont know the changes of the db?

Answer 1

Because the datacontext is caching the values. Here's an article on how to clear the cache. But now you have the problem of implementing a notification system of knowing when to clear it.

Microsoft recommend that a data context should be only used for a single unit of work. Hanging onto it as a singleton probably isn't a good idea.

Answer 2

What you are trying to do goes very much against how LinqToSql works.

Using a long lived DataContext is very difficult to do correctly, especially if you need to call stored procedures, where LinqToSql can't easily track the data changes.

Changes you make through the DataContext are generally tracked automatically, so the DataContext can properly manage its cache and keep track of changes being made to the database from that DataContext. That isn't always the case however. The DataContext doesn't (and can't easily) understand what your stored procedure is doing, so it has no idea how to keep its cache correct. At that point, after calling the stored procedure, your very best option is to get rid of that DataContext and create a new one. That effectively blows away your cache, which may or may not be a significant performance hit, but data integrity should be your primary concern.

If your Singleton DataContext isn't the only thing modifying the database (for example, your database could be modified by things like: triggers, batch processing, other applications, etc.), your DataContext may also contain inaccurate data in its cache, which is yet another reason to have a short lived DataContext.

So, while you could possibly succeed with a long lived Singleton DataContext, you will be fighting the system the entire way and the system is likely to win in the end.

You have to decide: How important is data integrity?