SQLDependency OnChangeEvent not firing

Question

I have the following code to execute a SqlCommand against a database View:

public IEnumerable<PickNote> GetData()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BTNInternalData_LiveEntities"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [PICKINGROUTEID],[CUSTOMER],[SALESNAME]
           FROM [dbo].[PickScreenData] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) or [EXPEDITIONSTATUS] = 3", connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        var data = reader.Cast<IDataRecord>();

                        return data.Select(x => new PickNote
                        {
                            pickingRouteId = x["PICKINGROUTEID"].ToString()
                        }).ToList();
                    }
                }
            }
        }

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        // do stuff
    }

However, the dependency_OnChange method only gets called at the start of my application and doesn't do so again no matter if the data in my View changes. I've debugged SqlNotificationEventArgs and the Info is Invalid but I'm not sure why as the command query runs fine

Edit

I've changed the query so that it queries the table directly but SqlNotificationEventArgs.Info still says Invalid. Here is the new code:

public IEnumerable<PickNote> GetData()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AXLive"].ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand(@"
                SELECT PICKINGROUTEID, EXPEDITIONSTATUS
                FROM         [dbo].[WMSPICKINGROUTE]
                WHERE     (EXPEDITIONSTATUS <> 20) 
                AND (DATEADD(dd, 0, DATEDIFF(dd, 0, [ACTIVATIONDATETIME])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
                    OR [EXPEDITIONSTATUS] = 3)", connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.
                command.Notification = null;

                var dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    var data = reader.Cast<IDataRecord>();

                    return data.Select(x => new PickNote
                    {
                        pickingRouteId = x["PICKINGROUTEID"].ToString()
                    }).ToList();
                }
            }
        }
    }

WMSPICKINGROUTE is the table where my view was getting the data from before.

Answer 1

According to this MSDN page, you cannot use a SqlDependency against a select statement that references a view. That seems to be the problem. Rewrite your query to hit tables and it should work.