LINQ to SQL DataContext can sometimes be difficult to deal with. Especially when combined with the use of stored procedures. For example, there is a case where I want to delete multiple records with a stored procedure rather than using the LINQ to SQL API for performance reason. After executing the stored procedure, the DataContext is not aware of the changes and still thinks that the entities are still there because the DataContext caches them. Hence, when I try to add another record using the same key, the DataContext throws an exception with the following message:
Cannot add an entity with a key that is already in use.
Logically, I want to refresh the DataContext memory so it will recheck the database. The provided
DataContext.Refresh() method requires the original entity set to be sent which can be a problem. I don’t want to query the DataContext for the entities for performance reason, hence the stored procedure is used in the first place. So I have to find another way to refresh the DataContext or clear the cache without relying on the original entity set.
There is no official way to do it, but I stumbled on this blog post telling that there is a DataContext method called
ClearCache() that can be used for that purpose. Unfortunately that method has internal modifier so you cannot call it from your code. But with a bit of reflection magic you can borrow the hidden power as follows:
context.GetType().InvokeMember( "ClearCache", BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic, null, context, null);
After invoking the
ClearCache() method, I can now successfully add new entities to the DataContext by reusing the keys from the deleted records.