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.System.Runtime.Remoting.Messaging.CallContextt.SetData("MS.SqlDependencyCookie", sqlDependency.Id)
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.