Why SQL dependency not firing event with group by clause and top ?

See more:

ADO.NET

SqlDependency

SignalR

, +

hi
i am working with Signal r and sql dependency . i have created one demo for such requirement that if i update in database . it will auto change ui content. its working fine but when i tried to use group by clause or top or Rownum . Sql dependency Change event is not firing and it does not reflect any change on UI.

readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        public IEnumerable<productinfo> GetAllMessages()
        {
            var ProductInfo = new List<productinfo>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"[dbo].[GetAllRecordsWithParams]", connection))
                {
                    command.Notification = null;
                    command.CommandType = CommandType.StoredProcedure;
                    var dependency = new SqlDependency(command);
                    SqlDependency.Start(_connString);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

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

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        ProductInfo.Add(item: new ProductInfo
                        {
                            CustomerID = (string)reader["CustomerID"],
                            ContactName = (string)reader["ContactName"],
                            Phone = (string)reader["Phone"],
                            ProductName = (string)reader["ProductName"],
                            UnitPrice = (decimal)reader["UnitPrice"],
                            IsDeleted = (bool)reader["IsDeleted"]
                        });
                    }
                    
                }
                connection.Close();
                connection.Dispose();
            }
            return ProductInfo.ToList();
        }
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                MessagesHub.SendMessages();
            }
        }

Sql changes ::

ALTER PROCEDURE [dbo].[GetMixItems] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	

    -- Insert statements for procedure here
	  select  dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight
	   from [dbo].[Messages] as dm inner join [dbo].[TestTable] as 
	   dt on dm.MessageID  = dt.MessageTestId where IsRight = 0 
	   group by dm.MessageID, dm.Message, dm.EmptyMessage, dm.Date, dt.MessageExtendedText,dt.IsRight 
	   order by dm.MessageID desc
	  
END

What I have tried:

i tried all i have mentioned .. please tell me d solution of this. how to do such all clause work with sql dependency...