集成SignalR与SqlTableDependency后页面需手动刷新显更新数据

在我的ASP.NET Core项目中，之前通过Ajax添加数据后能在成功回调里自动刷新列表，详情页更新数据后跳转到Index页也能直接看到更新内容，代码运行完全正常。但添加SignalR与SqlTableDependency库之后，数据库对应表出现了名为Sender的触发器，现在的问题是：虽然还能通过Ajax加载列表，但添加或更新数据后必须手动刷新页面才能看到最新内容，程序没有报错，但需要频繁刷新页面，非常麻烦。

此前正常运行的代码 #

AccountingController.cs #

[HttpGet]
public async Task<IActionResult> GetExpenseByUser()
{
    var user = await _userManager.FindByNameAsync(User.Identity.Name);
    int id = user.Id;
    var client = _httpClientFactory.CreateClient();
    var responseMessage = await client.GetAsync($"https://localhost:7198/api/Expense/ListExpenseByUser?id={id}");
    if (responseMessage.IsSuccessStatusCode)
    {
        var jsonData = await responseMessage.Content.ReadAsStringAsync();
        var values = JsonConvert.DeserializeObject<List<ResultExpenseByUserDto>>(jsonData);
        return Json(values);
    }
    return View();
}

Index.cshtml #

<script>
    $(document).ready(function () {
        function listExpense() {
            $.ajax({
                type: "Get",
                url: "/Accounting/GetExpenseByUser",
                success: function (value) {
                    $("#tableExpense tbody").empty();
                    var tablerow;
                    $.each(value, (index, item) => {
                        tablerow = $("<tr/>");
                        tablerow.append(`<td><a class="fw-semibold text-primary">#${item.periodID}</a></td>`)
                        tablerow.append(`<td>${item.createUser}</td>`)
                        tablerow.append(`<td>${item.periodMonth}</td>`)
                        tablerow.append(`<td>${item.periodYear}</td>`)
                        switch (item.paymentStatus) {
                            case 9999:
                                tablerow.append(`<td><span class="badge bg-warning-transparent">Bekleniyor</span></td>`)
                                break;
                            case 9998:
                                tablerow.append(`<td><span class="badge bg-warning-transparent">Onay Sürecinde - Yönetici</span></td>`)
                                break;
                            case 1:
                                tablerow.append(`<td><span class="badge bg-warning-transparent">Ödeme Bekleniyor - Muhasebe</span></td>`)
                                break;
                            case 2:
                                tablerow.append(`<td><span class="badge bg-success-transparent">Ödeme Yapıldı</span></td>`)
                                break;
                            case 3:
                                tablerow.append(`<td><span class="badge bg-dark-transparent">Gecikmiş Ödeme</span></td>`)
                                break;
                            case 4:
                                tablerow.append(`<td><span class="badge bg-danger-transparent">İptal Edilen Ödeme</span></td>`)
                                break;
                        }
                        let formattedTotalAmount = parseFloat(item.totalAmount).toLocaleString('tr-TR', { minimumFractionDigits: 2 });
                        tablerow.append(`<td>₺${formattedTotalAmount}</td>`);
                        tablerow.append(`<td><a href="#" class="btn btn-primary-light btn-icon btn-sm"><i class="ri-eye-line"></i></a> <a href="/Accounting/ExpenseTransactions/${item.expenseID}" class="btn btn-primary-light btn-icon btn-sm"><i class="ri-send-plane-2-line"></i></a></td>`);
                        $("#tableExpense").append(tablerow);
                    });
                },
                error: function (xhr, status, error) {
                }
            });
        }

        $("#btnAddExpense").click(function () {
            $.ajax({
                type: "Post",
                url: "/Accounting/Index",
                success: function () {
                    listExpense();

                    Toastify({
                        text: 'Harcama Dönemi Oluşturuldu',
                        duration: 3000,
                        gravity: "top",
                        position: 'right',
                        backgroundColor: "#4CAF50",
                        stopOnFocus: true,
                    }).showToast();
                },
                error: function (xhr, status, error) {
                    var errorMessage = xhr.responseJSON ? xhr.responseJSON : "Bu Döneme Ait Kayıt Bulunmaktadır";
                    Toastify({
                        text: errorMessage,
                        duration: 3000,
                        gravity: "top",
                        position: 'right',
                        backgroundColor: "#f64e60",
                        stopOnFocus: true,
                    }).showToast();
                }
            });
        });
    });
</script>

• 检查SqlTableDependency的通知逻辑：SqlTableDependency自动创建的Sender触发器负责监听表变更，需确认初始化代码已正确关联目标表，且订阅了OnChanged事件。数据变更时，要确保事件触发后能通过SignalR Hub推送消息到客户端。
• 完善前端SignalR订阅：前端需连接SignalR Hub，收到变更通知时自动调用listExpense()刷新列表。示例代码如下：

<script src="~/lib/signalr/dist/browser/signalr.js"></script>
<script>
    $(document).ready(function () {
        // 保留原有listExpense和按钮点击逻辑

        // 初始化SignalR连接
        const connection = new signalR.HubConnectionBuilder()
            .withUrl("/expenseHub") // 替换为你的Hub路由
            .build();

        // 监听服务端推送的更新通知
        connection.on("ReceiveExpenseUpdate", () => {
            listExpense();
        });

        // 启动连接
        connection.start().catch(err => console.error(err));
    });
</script>

• 排查触发器执行问题：用SQL Server Profiler跟踪Sender触发器的执行情况，检查数据库账户是否有足够权限运行触发器，确认触发器未吞掉异常。
• 临时回退验证：若暂时无法定位问题，可先禁用Sender触发器，验证是否恢复原有自动刷新逻辑，再逐步排查SignalR与SqlTableDependency的集成问题。

内容的提问来源于stack exchange，提问作者Gökmen Ada