Trigger cache clearing on table crud operation in linq to sql

Question

I have a method that gets all the records from a particular database, then stores it in the cache. The next time that method is called, it first checks the cache to see if it can simply return a cache version, if that cache object hasn't been expired.

Question: how do I trigger a method everytime dataContext.SubmitChanges() is called? For example, if I get all the books from the Book table and store it in Cache["AllBooks"], I want this cache object to be cleared on any crud operations related to the Book table.

What I'm currently doing:

var b = dataContext.Books.Where(x => x.BookId == 4).SingleOrDefault();
b.Title = "new title for book w/ id of 4";
dataContext.SubmitChanges();
ClearBookCache();

later...

private void ClearBookCache() {
    CustomCachingSystem.Clear["AllBooks"];
}

What I want: the ClearBookCache() to be automatically triggered on any Book table crud operations, vs. me having to remember to call it everytime I do a crud operation on the Book table.

Note: I wouldn't want that ClearBookCache() method to be called if I do a crud operation on a table that's unrelated to the Book table.

I hope this makes sense!

Answer 1

You could use the SqlDependency Class. Basically, it will let you detect changes to the queried data (if you use M$ SQL Server 2005+).

You may want to look into Service Broker technology (if you use M$ SQL Server 2005+).

Detecting it directly in your application probably won't be enough - you won't be able to detect any changes that were performed outside of your application.

Answer 2

Take a look at DataContext.GetChangeSet() - you could inherit from DataContext and override the SubmitChanges methods to clear the relevant caches based on the ChangeSet contents and then call MyBase.SubmitChanges(...).