I have a Blazor application that uses SQLTableDependency to detect database changes, then notify all clients about the change via SignalR. This works but I need a way to be able to detect changes and only notify specific SignalR groups. Because SQLTableDependency doesn't care about who inserted, altered, or deleted a record in the database, I am not sure how to know which group to send the update too. Please see below for more details about my app and what i'm trying to accomplish.
For each customer we setup a new organization. An organization has its own list of assets, and can have multiple users.
Organization.cs
public class Organization
{
public int OrganizationId { get; set; }
public string OrganizationName { get; set; }
public List<Asset> Assets { get; set; }
public List<ApplicationUser> Users { get; set; }
public bool IsDisabled { get; set; }
}
Asset.cs
public class Asset
{
public int AssetId { get; set; }
public string SerialNumber { get; set; }
public int OrganizationId { get; set; }
public virtual Organization Organization { get; set; }
public DateTime DateAdded { get; set; }
}
ApplicationUser.cs
public class ApplicationUser
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int OrganizationId { get; set; }
public virtual Organization Organization { get; set; }
public List<Connection> Connections { get; set; }
public string Timezone { get; set; }
}
Connection.cs - I am storing each SignalR Connection in the database.
public class Connection
{
public string ConnectionId { get; set; }
public string UserName { get; set; }
public bool Connected { get; set; }
public string Group { get; set; }
public DateTime ConnectionTime { get; set; }
}
AssetService.cs
public class AssetService : IAssetService
{
private readonly IServiceScopeFactory _serviceScopeFactory;
public AssetService(IServiceScopeFactory serviceScopeFactory)
{
_serviceScopeFactory = serviceScopeFactory;
}
public async Task<Asset> AddAssetAsync(Asset asset, string currentUserName)
{
try
{
using (var scope = _serviceScopeFactory.CreateScope())
{
var db = scope.ServiceProvider.GetService<DataContext>();
if (asset.Device != null)
{
db.Entry(asset.Device).State = EntityState.Modified;
}
asset.DateAdded = DateTime.UtcNow;
await db.Assets.AddAsync(asset);
await db.SaveChangesAsync();
return asset;
}
}
catch (System.Exception ex)
{
throw ex;
}
}
}
AssetHub.cs - SignalR Hub
public class ChatHub : Hub
{
private readonly UserManager<ApplicationUser> _userManager;
private readonly IServiceScopeFactory _serviceScopeFactory;
public ChatHub(UserManager<ApplicationUser> userManager, IServiceScopeFactory serviceScopeFactory)
{
_userManager = userManager;
_serviceScopeFactory = serviceScopeFactory;
}
public async Task SendAssetToGroup(string userName, string location, Asset asset)
{
if (!string.IsNullOrWhiteSpace(userName))
{
var user = await _userManager.Users.Include(x => x.Connections).SingleAsync(x => x.UserName == userName);
if (user != null)
{
var group = $"{user.AccountId}-{location}";
await Clients.Group(group).SendAsync("AssetUpdate", user.Email, asset);
}
}
}
public override async Task OnConnectedAsync()
{
var httpContext = Context.GetHttpContext();
var location = httpContext.Request.Query["location"];
using (var scope = _serviceScopeFactory.CreateScope())
{
var db = scope.ServiceProvider.GetService<ApplicationDbContext>();
if (!string.IsNullOrWhiteSpace(userName))
{
var user = await db.Users.Include(x => x.Connections).SingleAsync(x => x.UserName == httpContext.User.Identity.Name);
if (user != null)
{
var group = $"{user.OrganizationId}-{location}";
var connection = new Connection { Connected = true, ConnectionId = Context.ConnectionId, Group = group, UserName = user.UserName };
await Groups.AddToGroupAsync(connection.ConnectionId, group);
user.Connections.Add(connection);
db.Users.Update(user);
}
}
await db.SaveChangesAsync();
}
await base.OnConnectedAsync();
}
public override async Task OnDisconnectedAsync(Exception exception)
{
if (!string.IsNullOrWhiteSpace(Context.ConnectionId))
{
using (var scope = _serviceScopeFactory.CreateScope())
{
var db = scope.ServiceProvider.GetService<ApplicationDbContext>();
var connection = await db.Connections.Where(x => x.ConnectionId ==
Context.ConnectionId).FirstOrDefaultAsync();
if (connection != null)
{
await Groups.RemoveFromGroupAsync(connection.ConnectionId, connection.Group);
db.Connections.Remove(connection);
await db.SaveChangesAsync();
}
}
}
await base.OnDisconnectedAsync(exception);
}
}
AssetTableChangeService.cs - Here is where I need help. When SQLTableDependency detects a change to the Assets table, I need to be able to call the SendAssetToGroup Method in the AssetHub. Since users are apart of organizations, I don't want to push the update out to all organizations, I only want to send the update only to users that are apart of the specific organization group.
public class AssetTableChangeService : IAssetTableChangeService
{
private const string TableName = "Assets";
private SqlTableDependency<Asset> _notifier;
private IConfiguration _configuration;
public event AssetChangeDelegate OnAssetChanged;
public StockTableChangeService(IConfiguration configuration)
{
_configuration = configuration;
// SqlTableDependency will trigger an event
// for any record change on monitored table
_notifier = new SqlTableDependency<Asset>(
_configuration.GetConnectionString("DefaultConnection"),
TableName);
_notifier.OnChanged += AssetChanged;
_notifier.Start();
}
private void AssetChanged(object sender, RecordChangedEventArgs<Asset> e)
{
OnAssetChanged.Invoke(this, new AssetChangeEventArgs(e.Entity, e.EntityOldValues));
}
public void Dispose()
{
_notifier.Stop();
_notifier.Dispose();
}
So the flow should look like this.
I have everything working up until steps 9 and 10. Is there anyway to make this possible since SQLTableDependency doesn't care about who made the change, so I have no way of looking up the connection group in which the update needs to be pushed too. Any ideas?
When the UI is working with a class for example called : Student
The UI component joins a group called "Student" or "BlahNamespace.Student". If its a list just the name, if its an entity join the both the name and another group with the ID as a string concatenated "BlahNamespace.Student:201" In your case you could append the the organization's name as well for finer grain if the db knows this from the entity.
The server can notify the groups as needed depending on the operation.
I inject the hub into the API controller to achieve this.
Personally I would not use the signalr service to transfer the data, keep it light weight, just "signal" the change. The client can then decide how to deal with that. That way the data can be only accessed one way via the API with all the configured security.