How to allow SignalR to push updates from DB to the browser using EF6

Question

How can I allow SignalR to push updates from a SQL Server database to the browser using Entity Framework 6?

Here's my action method:

public ActionResult Index()
{
    var currentGates = _ctx.Transactions
        .GroupBy(item => item.SubGateId)
        .SelectMany(group => group.OrderByDescending(x => x.TransactionDateTime)
        .Take(1))
        .Include(g => g.Card)
        .Include(g => g.Student)
        .Include(g => g.Student.Faculty)
        .Include(g => g.Student.Department)
        .Include(g => g.SubGate)
        .ToList();

    return View(currentGates);
}

---

After a lot of searching, the only result I got is this:

ASP.NET MVC 5 SignalR, SqlDependency and EntityFramework 6

I have tried the suggested way but it didn't work. In addition to that, I found a very important security issue concerning storing sensitive data in a hidden field!

My question is: How can I update my view according to any Insert on Transaction table?

Answer 1

So basically what you need to do is overwrite the SaveChanges() method and action you SignalR function:

public class ApplicationDbContext : IdentityDbContext<IdentityUser>
  {
  public override int SaveChanges()
        {
            var entities = ChangeTracker.Entries().Where(x => x.Entity is Transactions && x.State == EntityState.Added) ;
            IHubContext hubContext = GlobalHost.ConnectionManager.GetHubContext<MyHub>();
            foreach (var entity in entities)
            {
              hubContext.Clients.All.notifyClients(entity);
            }

            return base.SaveChanges();
         }
    }

Answer 2

Normally I would plug into either the service creating the Transaction or the Save changes event like @Vince Suggested However because of your new requirement

the problem is: I don't have this control over the sdk that pushes the transactions of students to db, so I hope that there's some way to work directly over the db table.

In you're case you can just watch the table using SQL Dependencies

Note: Be careful using SqlDependency class - it has problems with memory leaks.

using(var tableDependency = new SqlTableDependency<Transaction>(conString))
{
    tableDependency.OnChanged += TableDependency_Changed;
    tableDependency.Start();
}

void TableDependency_Changed(object sender, RecordChangedEventArgs<Transaction> e)
{
    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;
        //You'll need to change this logic to send only to the people you want to 
        IHubContext hubContext = GlobalHost.ConnectionManager.GetHubContext<MyHub>();
        hubContext.Clients.All.notifyClients(entity);
    }
}

Edit It seems you may have other dependencies e.g include you want with your results. So what you can do is resolve the Id from the entity and then call Get using EntityFramework.

if (e.ChangeType != ChangeType.None)
{
    var changedEntity = e.Entity;
    var id = GetPrimaryKey(changedEntity)
    _ctx.Transactions.Find(id);
}

Edit Other Methods of approach.

1. If you're entities have a last updated field you can scan for changes to the table on a timer. Client has a timer when it elapses it send the in the last time it checked for changes. The server the obtains all of entities who have an last update time stamp greater than the time passed in

2. Although you don't have access to the EF, you probably have access to the web api requests. Whenever someone calls an update or create method, just grab the id of the changed entity, pop all the ids of the entities changed onto a background service which will send the signalr notifications,

The only issue now is obtaining the primary key that can be done with a manual mapping of the types to their id property, or it can be done using the metadata from the model but that's a bit more complicated

Note you probably can modify this so it works generically on all tables

Answer 3

I understand you have the following dataflow:

• 6x card reader devices
• a 'manager' receives the card reader data and pushes it to
• a SQL Server 'transaction' table

All of this is 3rd party. Your goal is to have a custom ASP.NET web page that displays the most recent record received by a card reader.

So the issue basically is, that your ASP.NET service needs to be notified of changes in a monitored database table.

Quick-and-dirty approach:

1. Implement a database trigger on your big 'transaction' table that updates a lightweight 'state' table

2. CREATE TABLE state ( 
       GateId   INT          NOT NULL, 
       UserName VARCHAR (20) NOT NULL, 
       PRIMARY KEY (GateId)
   );
   

   This table is intended to have 1 record per gate only. 6 gates -> 6 records in this table.

3. Change your ASP.NET service to poll the 'state' table in an interval of e.g. 200ms. When doing such a hack, make sure the table you are polling does not have many records! Once you detect a change, notify your SignalR clients.

IMHO, a dataflow via a database is a bad design decision (or limitation). It would be way better if your 'manager' does not only push data to the database, but subsequently notifies all SignalR clients. This dataflow is basically what @Vince answer assumes.

Edit: As you have posted the actual device you are using, I'd encourage you to double-check if you can directly connect to the card reader. It seams that there are approaches to register some kind of callback once the device has read a student card. Do this with the sole goal of achieving a straight dataflow / architecture like this:

-> connect 6x card readers to your 
-> ASP.Net service which at a single point in your code:
   -> updates the database
   -> updates the Signal R clients

• http://kb.supremainc.com/bs2sdk/doku.php?id=en:getting_started
• https://github.com/supremainc/BioStar2_device_SDK
• You might ask the vendor for support, for the C# documentation is not too plenty :|