Refreshing the LINQ to SQL DataContext by Clearing the Cache

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.

  • Pritesh Ostwal

    Good job .. Thanks for the solution

  • Sarfarosh

    I am getting an error like “Must specify binding flags describing the invoke operation required (BindingFlags.InvokeMethod CreateInstance GetField SetField GetProperty SetProperty).”

    I added a new item new my Context and Inserted succsessfully in db too. Immediately after that if i search for that item in the Context (I can see that particular item is present in the Context), it gives no result eventhough the item present? Any Idea?

    • Anonymous

      1. Can you specify the code that you used?
      2. How did you know that the item was present if it gave you no result? Did you check the database?

    • Tonny Vo

      Here my code and it worked. Try to add BindingFlags.InvokeMethod to @denniland:disqus code

      GetType().InvokeMember(“ClearCache”, BindingFlags.InvokeMethod | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic, null, this, null);

      Thanks

  • Alex Gaisman

    I am getting the same error like “Must specify binding flags describing the invoke operation requred…” But i think I am getting this error becouse I am using DbLinq.dll and MySqlContext which doesn’t have ClearCache method in it. I couldn’t find any other class method that would be helpful in this case, but some other article proposed just to dispose and recreate DataContext class.

    • Anonymous

      Hi Alex,

      This article is only for LINQ to SQL. Since it’s a calling a L2S private method, there’s no guarantee that method exists in other libraries.

      Other possible workarounds are just like you said, by disposing and recreating the DataContext instance, or simple instantiate a new DataContext instance.

  • Madina

    strange, the code given in the link http://blog.robustsoftware.co.uk/2008/11/clearing-cache-of-linq-to-sql.html works, but your interpretation throws exception “ArgumentException” Additional information: Must specify binding flags describing the invoke operation required (BindingFlags.InvokeMethod CreateInstance GetField SetField GetProperty SetProperty).

    • http://www.denniland.com/ Denni Gautama

      Hi Madina,

      I assume you’re using .NET 3.5 or later. If it’s so, you need an additional mandatory BindingFlags.

      In this case, you need the InvokeMethod flag.

      Please try the following:

      context.GetType().InvokeMember(
      “ClearCache”,
      BindingFlags.InvokeMethod | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic,
      null, context, null);

      • Madina

        yes, I was using 4.0 framework. thanks for your answer!