ASP.NET Core项目实时通知实现咨询（无法使用SignalR）

Hey there! Since you can't use SignalR for real-time notifications in your ASP.NET Core Razor Pages project (with EF Core and SQL Server), let's break down practical, workable alternatives to get that red badge alert for admins when a user submits a new form. All these play nicely with your existing stack and role setup (admin/user).

1. Client-Side Polling (Simplest to Implement) #

This is the easiest approach—have the admin's page periodically check the backend for new un-reviewed forms. It's low-effort and works for most small-to-medium traffic scenarios.

How to Set It Up: #

1. Add a Backend Endpoint
   Create a Razor Page handler in your admin dashboard page to return the count of un-reviewed forms:

   public class AdminDashboardModel : PageModel
   {
       private readonly AppDbContext _context;
   
       public AdminDashboardModel(AppDbContext context)
       {
           _context = context;
       }
   
       public async Task<IActionResult> OnGetCheckUnreviewedForms()
       {
           var unreviewedCount = await _context.Forms
               .Where(f => !f.IsReviewed)
               .CountAsync();
           return new JsonResult(unreviewedCount);
       }
   }
   

2. Add Frontend JavaScript
   In your admin dashboard's Razor view, add a script to poll the endpoint every X seconds (adjust the interval based on how "real-time" you need it):

   // Poll every 5 seconds (5000ms)
   setInterval(async () => {
       try {
           const response = await fetch('/Admin/AdminDashboard?handler=CheckUnreviewedForms');
           const count = await response.json();
           const notificationBadge = document.getElementById('new-forms-alert');
           
           // Show/hide the red badge based on count
           if (count > 0) {
               notificationBadge.classList.remove('d-none');
               notificationBadge.textContent = count; // Optional: show the number of new forms
           } else {
               notificationBadge.classList.add('d-none');
           }
       } catch (err) {
           console.error('Error checking for new forms:', err);
       }
   }, 5000);
   

3. Add the Red Alert Element
   Drop this somewhere in your admin layout (like the navigation bar):

   <span id="new-forms-alert" class="badge bg-danger ms-2 d-none">!</span>
   

Pro Tip: #

To reduce database load, cache the count using IMemoryCache—update the cache whenever a form is submitted or reviewed, so your poll endpoint reads from cache instead of hitting the database every time.

2. Long Polling (More Efficient Than Regular Polling) #

Long polling cuts down on unnecessary requests by having the backend hold the request until there's a new form or a timeout. The frontend immediately re-sends the request after getting a response, keeping a near-real-time connection.

How to Set It Up: #

1. Backend Long Poll Handler
   Modify your admin page handler to wait for new forms or time out after 30 seconds:

   public async Task<IActionResult> OnGetLongPollUnreviewedForms()
   {
       var timeout = TimeSpan.FromSeconds(30);
       var startTime = DateTime.UtcNow;
   
       while (DateTime.UtcNow - startTime < timeout)
       {
           var unreviewedCount = await _context.Forms
               .Where(f => !f.IsReviewed)
               .CountAsync();
   
           if (unreviewedCount > 0)
           {
               return new JsonResult(unreviewedCount);
           }
   
           await Task.Delay(1000); // Check every second
       }
   
       return new JsonResult(0); // Timeout, return 0
   }
   

2. Frontend Long Poll Script
   Replace the regular polling script with one that re-sends the request immediately after a response:

   async function checkForNewForms() {
       try {
           const response = await fetch('/Admin/AdminDashboard?handler=LongPollUnreviewedForms');
           const count = await response.json();
           const notificationBadge = document.getElementById('new-forms-alert');
   
           if (count > 0) {
               notificationBadge.classList.remove('d-none');
               notificationBadge.textContent = count;
           } else {
               notificationBadge.classList.add('d-none');
           }
       } catch (err) {
           console.error('Long poll error:', err);
       } finally {
           // Restart the poll immediately
           setTimeout(checkForNewForms, 100);
       }
   }
   
   // Start polling when the page loads
   window.addEventListener('load', checkForNewForms);
   

Why This Is Better: #

You'll get alerts faster than regular polling, and you'll send way fewer requests to your server—great for busier apps.

3. SQL Server Query Notifications (Most Precise) #

If you want to avoid polling entirely (or at least reduce database hits), use SQL Server's SqlDependency to listen for changes to the Forms table. When a new form is submitted, the backend gets notified, and you can update a cache that your frontend checks.

Prerequisite: #

First, enable Service Broker on your SQL Server database (run this in SSMS):

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

How to Set It Up: #

1. Configure Cache and SqlDependency
   Inject IMemoryCache into your admin page model and set up the dependency listener:

   public class AdminDashboardModel : PageModel
   {
       private readonly AppDbContext _context;
       private readonly IMemoryCache _cache;
       private const string UnreviewedCountKey = "UnreviewedFormsCount";
   
       public AdminDashboardModel(AppDbContext context, IMemoryCache cache)
       {
           _context = context;
           _cache = cache;
           InitializeSqlDependency();
       }
   
       private void InitializeSqlDependency()
       {
           // Start the SqlDependency listener
           SqlDependency.Start(_context.Database.GetConnectionString());
   
           using var connection = new SqlConnection(_context.Database.GetConnectionString());
           connection.Open();
   
           // Query must be specific (no SELECT *, explicit columns, etc.)
           using var command = new SqlCommand(
               "SELECT COUNT(*) FROM dbo.Forms WHERE IsReviewed = 0",
               connection);
   
           var dependency = new SqlDependency(command);
           dependency.OnChange += OnFormsChanged;
   
           // Load initial count into cache
           var initialCount = (int)command.ExecuteScalar();
           _cache.Set(UnreviewedCountKey, initialCount);
       }
   
       private void OnFormsChanged(object sender, SqlNotificationEventArgs e)
       {
           // Re-check count when the table changes
           if (e.Type == SqlNotificationType.Change)
           {
               using var connection = new SqlConnection(_context.Database.GetConnectionString());
               connection.Open();
   
               using var command = new SqlCommand(
                   "SELECT COUNT(*) FROM dbo.Forms WHERE IsReviewed = 0",
                   connection);
   
               var newCount = (int)command.ExecuteScalar();
               _cache.Set(UnreviewedCountKey, newCount);
   
               // Re-initialize dependency (it only works once)
               InitializeSqlDependency();
           }
       }
   
       public IActionResult OnGetCheckFromCache()
       {
           var count = _cache.Get<int>(UnreviewedCountKey);
           return new JsonResult(count);
       }
   }
   

2. Frontend Script
   Use either regular polling or long polling to check the cached count endpoint (/Admin/AdminDashboard?handler=CheckFromCache)—this will hit the cache instead of the database every time.

Note: #

SqlDependency has strict rules for the query (no wildcards, must reference tables with schema names, etc.). Double-check the query format requirements to avoid issues.

Which Should You Choose? #

• Regular Polling: Go here if you want something quick and dirty—perfect for small teams or low-traffic apps.
• Long Polling: Better for apps that need faster alerts and want to reduce server load.
• SQL Server Notifications: Best for high-traffic apps where you want to minimize database hits and get precise alerts when data changes.

内容的提问来源于stack exchange，提问作者Lukáš Václavek