AutoRefresh Entity Framework Data Using SQL Server Service Broker

• Download source code for VS 2012 (EFAutoRefresh2012.zip) - 247.1 KB
• Download source code for VS 2010 (EFAutoRefresh.zip) - 23.6 KB

Automatically Update Your Application on DB Changes

Starting with SQL Server 2005, Microsoft introduced a new feature called SQL Server Service Broker (SSB) that allows communications (and notifications!) from SQL Server to other places. There is a class in the .NET Framework called SqlDependency that allows to notify the client about database changes. To get this to work, the SqlDependency constructor needs a SqlCommand - and that's a problem when you're using Entity Framework. The SqlCommand objects are hidden deep inside EF's objects.

Some time ago, a great post from Fritz Onion (previously published at http://www.pluralsight-training.net/community/blogs/fritz/archive/2006/06/15/27694.aspx) revealed a "magic cookie" that allows to connect a SqlDependency object to a hidden SqlCommand. This technique has been used in the open source project LinqToCache that maintains a cache using LINQ to SQL.

In this article, I'll introduce a way to use SQL Server Service Broker notifications in order to automatically update an Entity Framework object set.

The AutoRefresh Extension Method

You'll probably be familiar with the Refresh method that allows you to update an object set (a representation of a table) in your object context with fresh data from your database:

this.MyObjectContext.Refresh(RefreshMode.StoreWins, this.MyObjectContext.MyObjectSet);

Using the ServiceBrokerUtility outlined below, it's easy to refresh your object set not only once, but automatically on each update that occurs in the database. All you need is to replace the Refresh method by the new AutoRefresh extension method defined in ServiceBrokerUtility.

this.MyObjectContext.AutoRefresh
	(RefreshMode.StoreWins, this.MyObjectContext.MyObjectSet);

The ServiceBrokerUtility

Here's the code for all that magic (coming out of the cookie):

public static class ServiceBrokerUtility
{
  private static List<string> connectionStrings = new List<string>();
  private const string sqlDependencyCookie = "MS.SqlDependencyCookie";
  private static ObjectContext ctx;
  private static RefreshMode refreshMode;
  private static readonly Dictionary<string, IEnumerable> collections = new Dictionary<string, IEnumerable>();

  static public void AutoRefresh(this ObjectContext ctx, 
                RefreshMode refreshMode, IEnumerable collection)
  {
    var csInEF = ctx.Connection.ConnectionString;
    var csName = csInEF.Replace("name=", "").Trim();
    var csForEF = 
      System.Configuration.ConfigurationManager.ConnectionStrings
		[csName].ConnectionString;
    var newConnectionString = new 
      System.Data.EntityClient.EntityConnectionStringBuilder
			(csForEF).ProviderConnectionString;
    if (!connectionStrings.Contains(newConnectionString))
    {
      connectionStrings.Add(newConnectionString);
      SqlDependency.Start(newConnectionString);
    }
    ServiceBrokerUtility.ctx = ctx;
    ServiceBrokerUtility.refreshMode = refreshMode;
    AutoRefresh(collection);
  }

  static public void AutoRefresh(IEnumerable collection)
  {
   var oldCookie = CallContext.GetData(sqlDependencyCookie);
   try
   {
    var dependency = new SqlDependency();
    collections.Add(dependency.Id, collection);
    CallContext.SetData(sqlDependencyCookie, dependency.Id);
    dependency.OnChange += dependency_OnChange;
    ctx.Refresh(refreshMode, collection);
   }
   finally
   {
    CallContext.SetData(sqlDependencyCookie, oldCookie);
   }
  }

  static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
  {
    if (e.Info == SqlNotificationInfo.Invalid)
    {
      Debug.Print("SqlNotification:  A statement was provided that cannot be notified.");
      return;
    }
    try{
      var id =((SqlDependency)sender).Id;
      IEnumerable collection;
      if (collections.TryGetValue(id, out collection))
      {
        collections.Remove(id);
        AutoRefresh(collection);
        var notifyRefresh = collection as INotifyRefresh;
        if (notifyRefresh != null)
          System.Windows.Application.Current.Dispatcher.BeginInvoke(
            (Action)(notifyRefresh.OnRefresh));
      }
    }
    catch (Exception ex)
    {
      System.Diagnostics.Debug.Print("Error in OnChange: {0}", ex.Message);
    }
  }
}

Almost Ready to Use

Isn't that fancy? Once I'd developed that code, I instantly bound my (now automatically updated) object set to the ItemsSource property of a DataGrid and pressed F5 in Visual Studio in order to compile and run my program. I displayed Visual Studio's Server Explorer, right-clicked on my table, selected "Show Table Data" and thus changed the value of a field in the database.

Tadaa - the field's value changed in my program's grid accordingly! After I'd enjoyed that game some time I tried to add a new record to the database. Disappointingly my grid didn't honor the new record in any way.

The debugger revealed that new records arrived as soon as they were created in my object set, but the DataGrid didn't care. After some research, I found that (for whatever reason) the object sets are lacking any support of the INotifyCollectionChanged interface.

New problems - ok, we need more code.

The AutoRefreshWrapper

This class takes an object set and spits out a typed enumerable with suitable notification support for the controls and of course with ServiceBrokerUtility's autorefresh power.

Using this wrapper class, we can easily implement a property in a view model that we can use to bind to:

public class MyViewModel
{
    public MyViewModel()
    {
        ..
        MyThings = new AutoRefreshWrapper<thing>(
              MyObjectContext.MyObjectSet, RefreshMode.StoreWins);
        ..
    }
 
    public IEnumerable<thing> MyThings { get; private set; }
 
    ..
}

Here is the wrapper class:

public class AutoRefreshWrapper<T> : IEnumerable<T>, INotifyRefresh
    where T : EntityObject
{
    private IEnumerable<T> objectQuery;
    public AutoRefreshWrapper(ObjectQuery<T> objectQuery, RefreshMode refreshMode)
    {
        this.objectQuery = objectQuery;
        objectQuery.Context.AutoRefresh(refreshMode, this);
    }

    public IEnumerator<T> GetEnumerator()
    {
        return objectQuery.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }

    public void OnRefresh()
    {
        try
        {
            if (this.CollectionChanged != null)
                CollectionChanged(this, 
                  new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Reset));
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.Print("Error in OnRefresh: {0}", ex.Message);
        }
    }

    public event NotifyCollectionChangedEventHandler CollectionChanged;
}

This class implements a new interface INotifyRefresh:

public interface INotifyRefresh : INotifyCollectionChanged
{
	void OnRefresh();
}

Finally, that whole solution worked fine for me. But be careful - there are some restrictions regarding the queries that are working with Service Broker. And finally: The "Magic Cookie" is an undocumented feature, thus you should take care.