Tuesday, February 1, 2011

Caching in Entity Framework using Query Notifications

Query Notifications were introduced in SQL Server 2005 and SQL Server Native Client. It normally can be used via SqlDepedency and SqlCommand. However, Entity Framework hides the SqlCommand from the developers. Luckily, Mike Pizzo from the ADO.NET revealed a tip that resolved the problem. It can be found in Ryan Dunn's blog: USING SQLDEPENDENCY OBJECTS WITH LINQ. The tip is:
System.Runtime.Remoting.Messaging.CallContextt.SetData("MS.SqlDependencyCookie", sqlDependency.Id)
Basically, when "MS.SqlDependencyCookie" is set to a SqlDepedency's Id, the SqlDependency will be automatically associated with the SqlCommand created by Entity Framework. An open source project LinqToCache automates this process by extending the IQueryable interface.
The Prerequisites to use Query Notifications
  • Enable Service Broker in the database.
Tip: This statement can be used: ALTER DATABASE [DBName] SET ENABLE_BROKER. It requires exclusive access to the database. Without exclusive access, it will hang up without ending. To get exclusive access: ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
  • Enable Query Notifications by calling SqlDependency.Start().
Query Notifications is very efficient especially when there are multiple application servers. It allows applications to be notified when data has changed - no matter how data is changed and which client makes the change.
A query notification dependency is not able to detect changes after a database mirroring failover - to resolve this issue, add code to monitor database mirroring failovers. Then, re-execute the relevant commands that are used together with the dependencies.
EF may generate complex query that are not supported by Query Notifications. See Creating a Query for Notification for the limitations.


  1. I've read this is not possible with EF. I'm not sure how to modify my EF implementation to use this. Can you expand your example?

  2. 1. Enable Service Broker on the DB:
    -- This command need exclusive access the MyDB

    2. Start Listener when application starts:
    // Can get connectionString from ((System.Data.EntityClient.EntityConnection)context.Connection).StoreConnection.ConnectionString;
    // SqlDependency.Stop(connectionString);

    3. Get and cache items: