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.
Cons
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.
Posts
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?
ReplyDeleteThanks!
1. Enable Service Broker on the DB:
ReplyDelete-- This command need exclusive access the MyDB
ALTER DATABASE MyDB SET ENABLE_BROKER;
2. Start Listener when application starts:
// Can get connectionString from ((System.Data.EntityClient.EntityConnection)context.Connection).StoreConnection.ConnectionString;
// SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
3. Get and cache items:
context.MyTables.AsCached("MyTablesCacheKey").ToList();