SqlDependency with Entity Framework 5.0

• Download EFChangeNotify.zip - 2.1 MB

Introduction

This article will show you how to subscribe to data changes using Entity Framework 5.0. You will be able to use this method to have your application notified whenever there is a change to the results of a query, without having to constantly poll your database. Along the way we will learn some concepts about reflection and a little about the internals of the Entity Framework.

Background

This article assumes you are familiar with the Entity Framework and how to create a basic Code-First application. It also assumes you have some knowledge of reflection and how it works in the .Net framework.

Using the code

At the end of this article, you should be able to use the following to have your application notified any time a product with a Name of "Lamp" is created or deleted. Basically, any time the results of the query would change:

using (var notifer = new EntityChangeNotifier<Product, StoreDbContext>(p => p.Name == "Lamp"))
{
    notifer.Changed += (sender, e) =>
    {
        Console.WriteLine(e.Results.Count());
        foreach (var p in e.Results)
        {
            Console.WriteLine("  {0}", p.Name);
        }
    };

    Console.WriteLine("Press any key to stop...");
    Console.ReadKey(true);
}

Warning

This code makes use of undocumented parts of the Entity Framework, and as such, the EF team may change these without notice, causing any application using this approach to break, possibly very badly. Do NOT use this in ANY environment without understanding this risk.

SqlDependency

The basis for this approach is the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx">SqlDependency</a> class. This class is used to represent a query notification between an application and an instance of SQL Server. The basic operation of this class is as follows: first, you need to notify SQL Server that you will be using query notifications; then we will register the query we want notifications for; finally, we notify SQL Server we no longer need notifications, to clean everything up. Note: There are strict rules for what types of queries can be registered for notification. See Creating a Query for Notification on MSDN for a list of these rules.

Notifying SQL Server we are going to use query notifications:

string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;

SqlDependency.Start(connectionString);

<a href="http://msdn.microsoft.com/en-us/library/ms224872.aspx">SqlDependecy.Start</a> is the method used to notify SQL Server we will be using query notifications. This creates the Service Broker Queue on the database and starts the listener for the current AppDomain for receiving dependency notifications from SQL Server.

Registering our Query:

static void RegisterNotification()
{
    string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;
    string commandText = @"
Select
    dbo.Products.Id,
    dbo.Products.Name,
    dbo.Products.LastEditDate
From
    dbo.Products
Where
    dbo.Products.Name = 'Lamp'
";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            connection.Open();
            var sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

            // NOTE: You have to execute the command, or the notification will never fire.
            using (SqlDataReader reader = command.ExecuteReader())
            {
            }
        }
    }
}

Our query, when normally executed, just returns all Products with a name of "Lamp". As a query notification, however, it tells SQL Server that we want to be notified anytime the results of this query would change. This means:

1. Any time a new Product with Name = "Lamp" is inserted;
2. Any time an existing Product with Name = "Lamp" changes its name to something other than "Lamp";
3. Any time an existing Product with Name = "Lamp" is deleted;
4. Any time the value of one of the columns in the SELECT statement changes value for a Product with Name = "Lamp".

The query notification will NOT fire when:

1. Any product with Name != "Lamp" is modified, inserted, deleted;
2. Any column not specified in the SELECT statement is modified.

Reacting to notifications:

static void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    Console.WriteLine("Info: {0}; Source: {1}; Type: {2}", e.Info, e.Source, e.Type);
    RegisterNotification();
}

This method will get called anytime the results of our query change, or when an error occurs with registering the query. Notice that we call RegisterNotification again. If we don't, we will never get another update. SQL Server only sends one notification per registration. The SqlNotificationEventArgs contains several helpful properties for debugging issues and for understanding the nature of the change notification. Info is a SqlNotificationInfo enum. This tells what caused the notification, for example, Insert, Update, Delete, Invalid, etc. Source is a SqlNotificationSource enum. This tells whether the notification source is Data, Timeout, Database, etc. This value will be Statement if your statement is not a valid statement for notifications as laid out in Creating a Query for Notification. Type is a SqlNotificationType enum. This will be Change if the data was modified on the server or Subscribe if there was a failure to create the notification subscription. Note that this will NOT include any result set of any data from the database. It is up to you to go back and re-fetch the data.

Where is my Entity Framework?!?

So far, we have just covered the basic implementation of the SqlDependency and not how we plan to make it work with Entity Framework. As you can see from the example above, we just need to convert our Entity Framework expression into a SqlCommand that we can register with a SqlDependency. So, how do we convert:

db.Products.Where(x => x.Name == "Lamp")

into:

Select
    dbo.Products.Id,
    dbo.Products.Name,
    dbo.Products.LastEditDate
From
    dbo.Products
Where
    dbo.Products.Name = 'Lamp'

When we do something like db.Products.Where(x => x.Name == "Lamp") we can actually convert that to a DbQuery<Product>. This allows us to get at the base SQL Statement for the query, by just calling .ToString() on the query object:

var db = new StoreDbContext();

var query = db.Products.Where(x => x.Name == "Lamp") as DbQuery<Product>;

Console.WriteLine(query.ToString());

This actually returns the following:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[LastEditDate] AS [LastEditDate]
FROM [dbo].[Products] AS [Extent1]
WHERE N'Lamp' = [Extent1].[Name]

This query fits all of the guidelines specified in the Creating a Query for Notification document, so it will work great. This also means we can change our RegisterNotification method to the following:

static void RegisterNotification()
{
    string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;

    string commandText = null;

    using (var db = new StoreDbContext())
    {
        var query = db.Products.Where(x => x.Name == "Lamp") as DbQuery<Product>;

        commandText = query.ToString();
    }

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            connection.Open();

            var sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

            // NOTE: You have to execute the command, or the notification will never fire.
            using (SqlDataReader reader = command.ExecuteReader())
            {
            }
        }
    }
}

Notice now how we are no longer hard coding a string for the SQL command. Instead, we are using the lambda expression x => x.Name == "Lamp" on the DbContext object to generate the command for us. But what if we want to watch for products other than lamps? Let's change the RegisterNotification method to accept a parameter for the product name:

static void RegisterNotification(string productName)
{
    string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;

    string commandText = null;

    using (var db = new StoreDbContext())
    {
        var query = db.Products.Where(x => x.Name == productName) as DbQuery<Product>;

        commandText = query.ToString();
    }

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            connection.Open();

            var sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

            // NOTE: You have to execute the command, or the notification will never fire.
            using (SqlDataReader reader = command.ExecuteReader())
            {
            }
        }
    }
}

Uh-oh. Now when we run the example, we get an error!

Unhandled Exception: System.Data.SqlClient.SqlException: Must declare the scalar variable "@p__linq__0".

Where did this come from? Well, it turns out that the Entity Framework is smart enough to know that when you specify a hard-coded string in the expression, like x => x.Name == "Lamp", the value will never change so it generates a query with the string hard-coded as well. However, when you specify a variable, like x => x.Name == productName, it takes advantage of SQL Execution Plan Caching by creating a paramaterized query, so the query plan can be reused even with the value of productName changes. So, let's take a look at what we are getting now from the DbQuery<Product>.ToString() call:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[LastEditDate] AS [LastEditDate]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[Name] = @p__linq__0

Just like we thought, it is now using a parameterized query instead of hard-coding the values. Unfortunately, the DbQuery<T> class does not expose what parameters are in the query, other than through the string. Now, from here we have two options. First, we could assume that the parameters are always going to be named @p__linq__N, where N is the index of the parameter in the query. This doesn't seem like a good assumption to me, so we are going to look for another way. The second way of doing it is using reflection to find the parameters themselves. After all, they have to be in there somewhere, even if they aren't exposed. It's not magic, after all.

Unfortunately, this is where the warnings come into play. What follows is NOT supported in any way by the Entity Framework, and may break at ANY TIME.

Well, it turns out that internally, DbQuery<T> relies on a class that is marked as internal to the EntityFramework.dll assembly called InternalDbQuery<T>. Looking at InternalDbQuery<T>, we can see that it relies on the ObjectQuery<T> class to do its heavy lifting. And, it turns out, ObjectQuery<T> derives from ObjectQuery, which has a Parameters property. So, how do we get at this ObjectQuery from the DbQuery<T>? As I said before, we need to use reflection.

First, we get the InternalDbQuery<T> reference:

var internalQuery = query.GetType()
    .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
    .Where(field => field.Name == "_internalQuery")
    .Select(field => field.GetValue(query))
    .First();

And then the ObjectQuery<T> reference:

var objectQuery = internalQuery.GetType()
    .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
    .Where(field => field.Name == "_objectQuery")
    .Select(field => field.GetValue(internalQuery))
    .Cast<ObjectQuery<T>>()
    .First();

Using this ObjectQuery instance, we can then just iterate through the parameters, adding them to the SqlCommand. So now our RegisterNotification method looks like this:

static void RegisterNotification(string productName)
{
    string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;

    string commandText = null;
    var parameters = new SqlParameter[0];

    using (var db = new StoreDbContext())
    {
        var query = db.Products.Where(x => x.Name == productName) as DbQuery<Product>;

        commandText = query.ToString();

        var internalQuery = query.GetType()
            .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .Where(field => field.Name == "_internalQuery")
            .Select(field => field.GetValue(query))
            .First();

        var objectQuery = internalQuery.GetType()
            .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .Where(field => field.Name == "_objectQuery")
            .Select(field => field.GetValue(internalQuery))
            .Cast<ObjectQuery<Product>>()
            .First();

        parameters = objectQuery.Parameters.Select(x => new SqlParameter(x.Name, x.Value)).ToArray();
    }

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            connection.Open();

            command.Parameters.AddRange(parameters);

            var sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

            // NOTE: You have to execute the command, or the notification will never fire.
            using (SqlDataReader reader = command.ExecuteReader())
            {
            }
        }
    }
}

Now when we run the application, everything works as planned. However, I think we can clean up the code to make it more generic and allow us to reuse this for other queries as well. Let start by creating an extension method for DbQuery<T> to covert it to an ObjectQuery<T>:

public static class DbQueryExtension
{
    public static ObjectQuery<T> ToObjectQuery<T>(this DbQuery<T> query)
    {
        var internalQuery = query.GetType()
            .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .Where(field => field.Name == "_internalQuery")
            .Select(field => field.GetValue(query))
            .First();

        var objectQuery = internalQuery.GetType()
            .GetFields(BindingFlags.NonPublic | BindingFlags.Instance)
            .Where(field => field.Name == "_objectQuery")
            .Select(field => field.GetValue(internalQuery))
            .Cast<ObjectQuery<T>>()
            .First();

        return objectQuery;
    }
}

Now the relevant portion of our RegisterNotification method can turn into the following:

using (var db = new StoreDbContext())
{
    var query = db.Products.Where(x => x.Name == productName) as DbQuery<Product>;

    commandText = query.ToString();

    var objectQuery = query.ToObjectQuery();

    parameters = objectQuery.Parameters.Select(x => new SqlParameter(x.Name, x.Value)).ToArray();
}

But now I think we can take it evern further and create a new extension method for turning a DbQuery<T> directly into a SqlCommand:

public static SqlCommand ToSqlCommand<T>(this DbQuery<T> query)
{
    SqlCommand command = new SqlCommand();

    command.CommandText = query.ToString();

    var objectQuery = query.ToObjectQuery();

    foreach (var param in objectQuery.Parameters)
    {
        command.Parameters.AddWithValue(param.Name, param.Value);
    }

    return command;
}

This turns our RegisterNotification method into:

static void RegisterNotification(string productName)
{
    string connectionString = ConfigurationManager.ConnectionStrings["StoreDbContext"].ConnectionString;

    SqlCommand command;

    using (var db = new StoreDbContext())
    {
        var query = db.Products.Where(x => x.Name == productName) as DbQuery<Product>;

        command = query.ToSqlCommand();
    }

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (command)
        {
            command.Connection = connection;
            connection.Open();

            var sqlDependency = new SqlDependency(command);

            sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);

            // NOTE: You have to execute the command, or the notification will never fire.
            using (SqlDataReader reader = command.ExecuteReader())
            {
            }
        }
    }
}

This is great, but we are still hard coding a query against the Products DbSet on our DbContext. To get around this, lets create a new generic class that we can use to register queries for notifications.

EntityChangeNotifier

So, here is our first pass at moving all of the above into its own class:

public class EntityChangeNotifier<TEntity, TDbContext>
    : IDisposable
    where TEntity : class
    where TDbContext : DbContext, new()
{
    private DbContext _context;
    private Expression<Func<TEntity, bool>> _query;
    private string _connectionString;

    public EntityChangeNotifier(Expression<Func<TEntity, bool>> query)
    {
        _context = new TDbContext();
        _query = query;
        _connectionString = _context.Database.Connection.ConnectionString;

        SqlDependency.Start(_connectionString);
        RegisterNotification();
    }

    private void RegisterNotification()
    {
        _context = new TDbContext();

        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            using (SqlCommand command = GetCommand())
            {
                command.Connection = connection;
                connection.Open();

                var sqlDependency = new SqlDependency(command);
                sqlDependency.OnChange += new OnChangeEventHandler(_sqlDependency_OnChange);

                // NOTE: You have to execute the command, or the notification will never fire.
                using (SqlDataReader reader = command.ExecuteReader())
                {
                }
            }
        }
    }

    private SqlCommand GetCommand()
    {
        var q = GetCurrent();

        return q.ToSqlCommand();
    }

    private DbQuery<TEntity> GetCurrent()
    {
        var query = _context.Set<TEntity>().Where(_query) as DbQuery<TEntity>;

        return query;
    }

    private void _sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine("Info: {0}; Source: {1}; Type: {2}", e.Info, e.Source, e.Type);
        RegisterNotification();
    }

    public void Dispose()
    {
        SqlDependency.Stop(_connectionString);
    }
}

As you can see, our EntityChangeNotifier class takes two generic parameters, the first is the entity type and the second is the DbContext type. We are also using IDisposable to allow us to use the using syntax to start and stop the SqlDependency for the connection string. This allows us to write the following:

using (var notifer = new EntityChangeNotifier<Product, StoreDbContext>(x => x.Name == "Lamp"))
{
    Console.WriteLine("Press any key to stop listening for changes...");
    Console.ReadKey(true);
}

That's great, but now we need to do something with the event that gets fired when the change notification occurs. Let's create some events of our own on the EntityChangeNotifier class. First, the EventArgs classes:

public class EntityChangeEventArgs<T> : EventArgs
{
    public IEnumerable<T> Results { get; set; }
    public bool ContinueListening { get; set; }
}

public class NotifierErrorEventArgs : EventArgs
{
    public string Sql { get; set; }
    public SqlNotificationEventArgs Reason { get; set; }
}

And then our event declarations:

public event EventHandler<EntityChangeEventArgs<TEntity>> Changed;
public event EventHandler<NotifierErrorEventArgs> Error;

And then our OnEvent methods:

protected virtual void OnChanged(EntityChangeEventArgs<TEntity> e)
{
    if (Changed != null)
    {
        Changed(this, e);
    }
}

protected virtual void OnError(NotifierErrorEventArgs e)
{
    if (Error != null)
    {
        Error(this, e);
    }
}

And now we can change our SqlDependency event handler to:

private void _sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Subscribe || e.Info == SqlNotificationInfo.Error)
    {
        var args = new NotifierErrorEventArgs
        {
            Reason = e,
            Sql = GetCurrent().ToString()
        };

        OnError(args);
    }
    else
    {
        var args = new EntityChangeEventArgs<TEntity>
        {
            Results = GetCurrent(),
            ContinueListening = true
        };

        OnChanged(args);

        if (args.ContinueListening)
        {
            RegisterNotification();
        }
    }
}

Now we are checking if the event is fired because of an error or a change. If it is an error, we fire our own Error event. If it is a change, we fire our Changed event. We even include the IEnumerable<T> results from the query in our EventArgs class, so the consuming method can get the results directly. This allows us to do the following:

using (var notifer = new EntityChangeNotifier<Product, StoreDbContext>(x => x.Name == "Lamp"))
{
    notifer.Changed += (sender, e) =>
    {
        Console.WriteLine(e.Results.Count());
    };

    Console.WriteLine("Press any key to stop listening for changes...");
    Console.ReadKey(true);
}

Hooray! Looks like everything is working great! Well... almost. Let's see what happens when we do the following:

using (var notifer = new EntityChangeNotifier<Product, StoreDbContext>(x => x.Name == "Lamp"))
{
    notifer.Changed += (sender, e) =>
    {
        Console.WriteLine(e.Results.Count());
    };

    using (var otherNotifier = new EntityChangeNotifier<Product, StoreDbContext>(x => x.Name == "Desk"))
    {
        otherNotifier.Changed += (sender, e) =>
        {
            Console.WriteLine(e.Results.Count());
        };

        Console.WriteLine("Press any key to stop listening for changes...");
        Console.ReadKey(true);
    }

    Console.WriteLine("Press any key to stop listening for changes...");
    Console.ReadKey(true);
}

If you run the application now, you should see it print the total count of each type of product any time you make a change to that product in the database. Go ahead and run some queries directly on the database, like:

INSERT INTO Products (Name, LastEditDate) Values ('Lamp', GetDate())
INSERT INTO Products (Name, LastEditDate) Values ('Desk', GetDate())

Now press any key to close out the monitoring of the "Desk" notifier. Now run the insert statement for the desk again. What happened? We still got the notification! This tells us something important. Namely, that internally, SqlDependency keeps track of all the places the Start method was called (probably just a counter) and only stops listening when Stop has been called an equal number of times. We can fix this by maintaining and checking the state inside the EntityChangeNotification class itself. Let's change the Dispose method to not only call the SqlDependency.Stop method, but also to dispose of the DbContext instance as well:

public void Dispose()
{
    SqlDependency.Stop(_connectionString);

    if (_context != null)
    {
        _context.Dispose();
        _context = null;
    }
}

Now we can check in the SqlDependency event handler whether _context is null, and if so, just return:

private void _sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (_context == null)
        return;
    .
    .
    .
}

Now, let's try it again. Now after closing out the "Desk" notifier, we no longer receive notifications when the "Desk" rows change. And since we don't re-register the notification, it only happens once.

Points of Interest

The beauty of this approach is that we can get instant notifications sent to our application when data changes in the database, without having to poll the database every x seconds. It won't work with every query, and the limits applied by the SQL Server team as to what queries can trigger notifications are strict, but when it can be used it provides a much better way for your application to respond to data changes than just relying on polling. You should also read the MSDN documentation on the SqlDependency class, including the notes about how it was intended to be used. Specifically the notes about how it was (emphasis mine):

...designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server. If you are developing an application where you need reliable sub-second notifications when data changes, review the sections Planning an Efficient Query Notifications Strategy and Alternatives to Query Notifications in the Planning for Notifications topic in SQL Server Books Online.

Do keep these notes in mind when deciding whether SqlDependency is the right approach for your application.

Source Code

All source code is available on GitHub here.

History

November 2012 - First Release