Click here to Skip to main content
15,074,668 members
Articles / Desktop Programming / WPF
Posted 31 Jul 2011


93 bookmarked

AutoRefresh Entity Framework Data Using SQL Server Service Broker

Rate me:
Please Sign up or sign in to vote.
4.77/5 (35 votes)
14 Sep 2012CPOL3 min read
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

Comments and Discussions

QuestionWant to Work AutoRefresh Entity Framework Data Using SQL Server Service Broker in MVC Framework Pin
Agamya16-May-19 9:04
MemberAgamya16-May-19 9:04 
Questionmulti AutoRefreshWrapper at the same time Pin
HOSSEIN.AB22-Aug-17 23:58
MemberHOSSEIN.AB22-Aug-17 23:58 
QuestionPorted to .NET 4.6 & EF 6 Pin
gareth.brown12321-Dec-16 23:48
Membergareth.brown12321-Dec-16 23:48 
QuestionWhat exactly and practically it means when you say it's undocumented? Pin
Dariush The Great22-May-16 9:54
MemberDariush The Great22-May-16 9:54 
QuestionLINQ + SP + Params Pin
LordVovin28-Jul-15 6:31
MemberLordVovin28-Jul-15 6:31 
QuestionExcellent work Pin
kashif Atiq3-Jun-15 20:58
Memberkashif Atiq3-Jun-15 20:58 
Christian Del Bianco20-Apr-15 6:09
MemberChristian Del Bianco20-Apr-15 6:09 
GeneralTHANK YOU! (& Entity Framework 6) Pin
qzcreativeit29-Sep-14 23:52
Memberqzcreativeit29-Sep-14 23:52 
Questionsort collection Pin
Member 859429620-Mar-14 1:21
MemberMember 859429620-Mar-14 1:21 
Questionstored procedure Pin
compliedmonkey14-Dec-13 5:30
Membercompliedmonkey14-Dec-13 5:30 web form - update cache Pin
makv20091-Oct-13 23:02
Membermakv20091-Oct-13 23:02 
QuestionHow to use it in winform datagridview Pin
bilalsaeed23227-Sep-13 4:24
Memberbilalsaeed23227-Sep-13 4:24 
QuestionAutoRefresh in MVC Pin
vishal jodh17-Sep-13 3:51
Membervishal jodh17-Sep-13 3:51 
QuestionI have a question if someone has time to lend a hand. Pin
dpgil12-Feb-13 12:57
Memberdpgil12-Feb-13 12:57 
QuestionThere is no refresh event from DB to application! Pin
L. Magyar27-Sep-12 3:37
MemberL. Magyar27-Sep-12 3:37 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:13
professionalKanasz Robert24-Sep-12 6:13 
Questioncould be used with LINQ to SQL Pin
megcode23-Sep-12 18:33
Membermegcode23-Sep-12 18:33 
BugException Pin
Omar Gameel Salem15-Sep-12 1:39
professionalOmar Gameel Salem15-Sep-12 1:39 
GeneralRe: Exception Pin
Kishore Jangid16-Sep-12 7:53
MemberKishore Jangid16-Sep-12 7:53 
NewsSample code for VS 2012 / Entity Framework 5.0 Code First Pin
Harry von Borstel14-Sep-12 5:59
MemberHarry von Borstel14-Sep-12 5:59 
Questionn_tier architecture / WCF Pin
Piedro12346-Sep-12 1:07
MemberPiedro12346-Sep-12 1:07 
AnswerRe: n_tier architecture / WCF Pin
Member 792794421-Sep-15 11:41
MemberMember 792794421-Sep-15 11:41 
NewsSample code uploaded Pin
Harry von Borstel5-Sep-12 5:34
MemberHarry von Borstel5-Sep-12 5:34 
QuestionDownload code Pin
Member 79999663-Sep-12 12:56
MemberMember 79999663-Sep-12 12:56 
AnswerRe: Download code Pin
Harry von Borstel5-Sep-12 5:23
MemberHarry von Borstel5-Sep-12 5:23 

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.