Click here to Skip to main content
13,558,932 members
Click here to Skip to main content
Add your own
alternative version


89 bookmarked
Posted 31 Jul 2011
Licenced CPOL

AutoRefresh Entity Framework Data Using SQL Server Service Broker

, 14 Sep 2012
Rate this:
Please Sign up or sign in to vote.
How to use SqlDependency to refresh Entity Framework Object Sets automatically on DB changes

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 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.

	(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 = 
    var newConnectionString = new 
    if (!connectionStrings.Contains(newConnectionString))
    ServiceBrokerUtility.ctx = ctx;
    ServiceBrokerUtility.refreshMode = refreshMode;

  static public void AutoRefresh(IEnumerable collection)
   var oldCookie = CallContext.GetData(sqlDependencyCookie);
    var dependency = new SqlDependency();
    collections.Add(dependency.Id, collection);
    CallContext.SetData(sqlDependencyCookie, dependency.Id);
    dependency.OnChange += dependency_OnChange;
    ctx.Refresh(refreshMode, collection);
    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.");
      var id =((SqlDependency)sender).Id;
      IEnumerable collection;
      if (collections.TryGetValue(id, out collection))
        var notifyRefresh = collection as INotifyRefresh;
        if (notifyRefresh != null)
    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()
            if (this.CollectionChanged != null)
                  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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

No Biography provided

You may also be interested in...


Comments and Discussions

Questionmultiple datasets Pin
Member 831284831-Aug-12 8:03
memberMember 831284831-Aug-12 8:03 
AnswerRe: multiple datasets Pin
Harry von Borstel3-Sep-12 22:38
memberHarry von Borstel3-Sep-12 22:38 
GeneralMy vote of 5 Pin
Christian Amado3-Aug-12 7:57
memberChristian Amado3-Aug-12 7:57 
GeneralInvalid url Pin
GSerjo2-Aug-12 7:38
memberGSerjo2-Aug-12 7:38 
GeneralRe: Invalid url Pin
Harry von Borstel2-Aug-12 20:32
memberHarry von Borstel2-Aug-12 20:32 
GeneralMy vote of 5 Pin
mrmiagi010125-Jul-12 23:12
membermrmiagi010125-Jul-12 23:12 
GeneralOnChange is called endless Pin
mrmiagi010125-Jul-12 2:03
membermrmiagi010125-Jul-12 2:03 
GeneralMy vote of 4 Pin
chang3d20-Jun-12 12:27
memberchang3d20-Jun-12 12:27 
QuestionJournal of how I used this... and where I failed... Pin
chang3d18-Jun-12 13:50
memberchang3d18-Jun-12 13:50 
QuestionHow would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge7-May-12 12:48
memberTim Elvidge7-May-12 12:48 
AnswerRe: How would you modify this code for EF 4.3 using DbContext Pin
Harry von Borstel7-May-12 21:57
memberHarry von Borstel7-May-12 21:57 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge8-May-12 18:55
memberTim Elvidge8-May-12 18:55 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Member 831284830-Aug-12 11:53
memberMember 831284830-Aug-12 11:53 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Tim Elvidge30-Aug-12 12:29
memberTim Elvidge30-Aug-12 12:29 
GeneralRe: How would you modify this code for EF 4.3 using DbContext Pin
Member 831284831-Aug-12 8:17
memberMember 831284831-Aug-12 8:17 
GeneralMy vote of 2 Pin
Gusteru_2023-Apr-12 21:22
memberGusteru_2023-Apr-12 21:22 
GeneralRe: My vote of 2 Pin
Harry von Borstel26-Apr-12 2:21
memberHarry von Borstel26-Apr-12 2:21 
GeneralMy vote of 1 Pin
sangchi.rk26-Mar-12 3:09
membersangchi.rk26-Mar-12 3:09 
QuestionReg: downloading of original Code. Pin
sangchi.rk26-Mar-12 2:41
membersangchi.rk26-Mar-12 2:41 
AnswerRe: Reg: downloading of original Code. Pin
Harry von Borstel1-Apr-12 5:52
memberHarry von Borstel1-Apr-12 5:52 
SuggestionNeed download Pin
gs.meena9-Aug-11 1:35
membergs.meena9-Aug-11 1:35 
GeneralRe: Need download Pin
Harry von Borstel9-Aug-11 21:02
memberHarry von Borstel9-Aug-11 21:02 
GeneralRe: Need download Pin
Michael90002-Nov-11 5:22
memberMichael90002-Nov-11 5:22 
AnswerRe: Need download Pin
Harry von Borstel2-Nov-11 7:36
memberHarry von Borstel2-Nov-11 7:36 
GeneralRe: Need download Pin
smurancsik20-Feb-12 7:12
membersmurancsik20-Feb-12 7:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180515.1 | Last Updated 14 Sep 2012
Article Copyright 2011 by Harry von Borstel
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid