Friday, 14 September 2007

Using SQLDependency objects with LINQ

A question came up the other day on how to get LINQ to SQL to participate in using the SQL Notification Services.  Of course, I didn't know, but Mike Pizzo from the ADO.NET team was kind enough to answer.  I figured it must be possible, and sure enough, it is.  Essentially, you have to create a SQL dependency context, which is very similar to a transaction context.  Any code that participates within that context will automatically be associated with the SQLDependency.  Create the dependency first, before any LINQ (or other data access technology).  Here is the relevant code (note: this code is not optimized, so you might want to do things like change the SQLDependency to static or pass it in so it won't be garbage collected).

    static class GlobalNotifications


        public static event OnChangeEventHandler OnChange;


        public static void InitializeNotifications(string connectString)


            // Initialize notifications


            // Create and register a new dependency

            SqlDependency dependency = new SqlDependency();

            dependency.OnChange += new OnChangeEventHandler(NotificationCallback);

            System.Runtime.Remoting.Messaging.CallContext.SetData("MS.SqlDependencyCookie", dependency.Id);



        internal static void NotificationCallback(object o, SqlNotificationEventArgs args)


            OnChange.Invoke(o, args);



There is also one major caveat to using this with LINQ: Beware of complex queries that can be easily generated using LINQ.  The Query Processor will invalidate the command and fire an error event saying it was too complex.  Since you can easily generate lots of complex queries using LINQ (part of its power really), you need to really be cognizant of this limitation.