Signalr not recognizing updates to SQL database.

Question

I'm using Signalr for a realtime notifications for when you get a new message or friend request, and when you view them it changes a "HasBeenViewed" field to true in the database.

The problem is that when Signalr checks for new notifications it doesn't see the changed "HasBeenViewed" field, it thinks it is still false. In the database itself i can confirm that HasBeenViewed has been changed correctly.

The query in Signalr does correctly recognize if a new message/friend request has been added, or if one has been removed. It just doesn't see the updated value for HasBeenViewed.

If I restart IIS then it Signalr finally does see the updated HasBeenViewed value and displays the notifications correctly. However, if a new notification gets sent and viewed, then it is back to not recognizing the viewed status on the new notifications.

Here is my code for the notification check. Using ASP.NET Core. I'm querying from a repository to an MS SQL database.

public void NotificationCheck(string userName)
    {

        var user = _repo.Query<ApplicationUser>().Where(u => u.UserName == userName).Include(u => u.FreindRequests).Include(u => u.Messages).FirstOrDefault();


        var messageCount = user.Messages.Where(m => m.HasBeenViewed == false).Count();

        var friendRequests = user.FreindRequests.Where(f => f.HasBeenViewed == false).Count();  

        Clients.Caller.notificationCount(friendRequests + messageCount);

    }

I'm not sure what the problem is. I'm guessing there is some interaction between the query and signalr hub where it is storing it in memory instead of getting the new information every time, but i'm not sure what to do to fix it.

Thanks for any help

if you want realtime notifications from SQL you should check out service broker. — Matthew Whited

Kelso Sharp Kelso Sharp · Accepted Answer · 2016-10-05T15:10:57

Without using SQLDependency triggers, your best option is the manage the message state in the client, and just use the sql database for long term persistence, that way you can get the status of messages when the user logs in, and you have immediate access to state in the client.

Signalr not recognizing updates to SQL database.

1 Answers