I’m ashamed that this is STILL on my to-do list as I have probably written and presented more about Query Notification than most people. But I see via Roger Jennings blog that Ryan Dunn has the key of the implementation posted on his blog (by way of a hot tip from Mike Pizzo on the DP* team :-)).

To activate Query Notification, you need to get a query “registered” in SQL Server’s Service Broker then listen for a notification.

ADO.NET 2.0’s SqlDependency class does all the dirty work then all you need to do is create a SqlDependency object and attach it to a SqlCommand before you execute it. When the command gets to the server, the server sees the dependency and sets up the watch and the notification in Service Broker. When SQL Server sees something change that would impact the results of your query, it fires back a notification. SqlDependency has an event handler to catch that notification as well as other properties to interact with it.

SqlNotification is a more low level approach for complex scenarios where you want to have more control over the process. Here you need to create your own listener.

ASP.NET also uses Query Notification with the SqlCacheDependency object as well as dynamically engaging it in the Page directive.

The obvious (if you have played with this stuff) problem here is that with LINQ to SQL, we are not executing the command ourselves. So how do we register a query with service broker and how do we listen for it?

Rather than attaching the SqlDependency to a SqlCommand, you can expliclty wire up some instructions to have the SqlDependency taken along when a call is made to SqlServer. Pretty cool, although I’m already wondering about how I might want to control that. Certainly you don’t want to attempt to register EVERY SINGLE query with Service Broker. Many will fail anyway and when that happens you get an immediate notice about the failure. There are a limited scope of scenarios in which QueryNotification is a benefit (oft-called and infrequently changing data e.g. “states in the u.s.” “categories of items we sell”) and plenty of rules about what is and is not a valid query for notification. With a blanket approach, you could be creating a performance nightmare.

The key is the CallContext class, which is buried in System.Runtime.Remoting.Messaging. I’ve never heard of it before and would never ever have figured this out on my own! Here’s more info on CallContext.

Here’s the critical line of code from Ryan’s post which has a more complete example of using SqlDependency in it’s simplest form, which is always the right place to start.

  System.Runtime.Remoting.Messaging.CallContext.SetData(“MS.SqlDependencyCookie”, dependency.Id)

So now my todo list is getting edited. I will have to see how to control this puppy and also what happens when you use the asp.net page directive.

(*DP=Data Programmability)

I’ve been watching Roger Jennings blog as he forayed into hammering on the use of stored procs in LINQ to SQL. Like a great and supportive pal, I sat on the sidelines while he dug dizzyingly deeper and deeper to try to work out issues with what a stored proc actually returns (different than using LINQ to get dynamic sql) and it’s impact on paging with databound asp.net controls as well as loading children. It also impacts the use of LINQDataSource.

I definitely have an interest as I’m doing some more talks on LINQ and Databinding later this fall.

Today he reports that with the required obsessive persistence (grin) and help from Matt Warren, he’s only solved one of the issues he was running into and 1/4 of another one. (It took me a while to figure out his math on that!)

Here is are the related posts:

Problems Using Stored Procedures for LINQ to SQL Data Retrieval  This post has been updated to reflect his up-to-date findings.

Update and summary of the problem is included at the top of this post LINQ and Entity Framework Posts for 9/14/2007+

On a related front, Mike Taulty has started digging into disconnected LINQ to SQL. Mike is one of the web services gentry in my mind, so I definitely trust his conclusions. Rick Strahl has some interesting posts on it as well. I’ve been trying to work out the same scenarios with Entity Framework (with XML serialization and binary) and the challenges and solutions are pretty similar so there’s a ton that we can learn from each other.