ImmediateNotificationRegister

// 
// the following code is extraction from https://code.msdn.microsoft.com/How-to-use-SqlDependency-5c0da0b3/sourcecode?fileId=158219&pathId=775745477
//
public class ImmediateNotificationRegister<TEntity> : IDisposable where TEntity : class 
    { 
        private SqlConnection connection = null; 
        private SqlCommand command = null; 
        private IQueryable iquery = null; 
        private ObjectQuery oquery = null; 
        public event EventHandler OnChanged; 
        private SqlDependency dependency = null; 
 
        /// <summary> 
        /// Initializes a new instance of ImmediateNotificationRegister class. 
        /// </summary> 
        /// <param name="query">an instance of ObjectQuery is used to get connection string and  
        /// command string to register SqlDependency nitification. </param> 
        public ImmediateNotificationRegister(ObjectQuery query) 
        { 
            try 
            { 
                this.oquery = query; 
                QueryExtension.GetSqlCommand(oquery, ref connection, ref command); 
                RegisterSqlDependency(); 
            } 
            catch (Exception ex) 
            { 
                throw new Exception("Fails to initialize a new instance of ImmediateNotificationRegister class.", ex); 
            } 
        } 
 
        public ImmediateNotificationRegister(DbContext context, IQueryable query) 
        { 
            try 
            { 
                this.iquery = query; 
 
                // Get the ObjectQuery directly or convert the DbQuery to ObjectQuery. 
                oquery = QueryExtension.GetObjectQuery<TEntity>(context, iquery); 
                QueryExtension.GetSqlCommand(oquery, ref connection, ref command); 
                RegisterSqlDependency(); 
            } 
            catch (Exception ex) 
            { 
                throw new Exception("Fails to initialize a new instance of ImmediateNotificationRegister class.", ex); 
            } 
        } 
        public static void StartMonitor(DbContext context) 
        { 
            try { 
                SqlDependency.Start(context.Database.Connection.ConnectionString); 
            } catch (Exception ex) { 
                throw new System.Exception("Fails to Start the SqlDependency in the ImmediateNotificationRegister class", ex); 
            } 
        } 
        public static void StopMonitor(DbContext context) 
        { 
            //skip for sake of simplicity
        } 
 
        private void RegisterSqlDependency() 
        { 
            if (command == null || connection == null) 
            { 
                throw new ArgumentException("command and connection cannot be null"); 
            }  
            command.Notification = null; 
            // Create and bind the SqlDependency object to the command object. 
            dependency = new SqlDependency(command); 
            dependency.OnChange += new OnChangeEventHandler(DependencyOnChange); 
 
            // After register SqlDependency, the SqlCommand must be executed, or we can't  
            // get the notification. 
            RegisterSqlCommand(); 
        } 
 
        private void DependencyOnChange(object sender, SqlNotificationEventArgs e) 
        {  
            SqlDependency dependency = (SqlDependency)sender; 
            dependency.OnChange -= DependencyOnChange; 
            if (OnChanged != null) 
            { 
                OnChanged(this, null); 
            } 
            // We re-register the SqlDependency. 
            RegisterSqlDependency(); 
        } 
 
        private void RegisterSqlCommand() 
        { 
            if (connection != null && command != null) 
            { 
                connection.Open(); 
                command.ExecuteNonQuery(); 
                connection.Close(); 
            } 
        } 

        public void Dispose() 
        { 
            Dispose(true); 
            GC.SuppressFinalize(this); 
        } 
        protected void Dispose(Boolean disposed) 
        { 
           //skip for sake of simplicity
        } 
        public SqlConnection Connection 
        { get { return connection; } } 
        public SqlCommand Command 
        { get { return command; } } 
        public ObjectQuery Oquery 
        { get { return oquery; } } 
    } 
}