ASP.NET Core中使用SignalR和SqlDependency执行JOIN查询时出现500内部服务器错误的问题求助

大家好，我在项目里结合SignalR和SqlDependency做数据库变更通知功能，普通的SELECT或者参数化查询都能正常跑，但一用带JOIN的查询，前端控制台就抛出GET https://localhost:7243/Home/GetIndex 500 (Internal Server Error)的内部服务器错误，麻烦帮忙看看问题出在哪？

以下是我的相关代码：

Repository层代码 #

public List<ResultNotificationDto> ListNotification(int id)
{
    var notificationDtos = new List<ResultNotificationDto>();

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        string query = "SELECT N.LogID, U.Name + ' ' + U.Surname AS SenderUserID, N.RecordID, N.Timestamp, N.Description, N.Title, N.NotificationIcon FROM Notification N INNER JOIN AspNetUsers U ON N.SenderUserID = U.Id WHERE N.Status = 0 AND N.ReceiverUserID = @receiverUserID";

        var cmd = new SqlCommand(query, connection);
        cmd.Parameters.AddWithValue("@receiverUserID", id);

        var dependency = new SqlDependency(cmd);
        dependency.OnChange += new OnChangeEventHandler(dbChangeNotification);

        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            var notificationDto = new ResultNotificationDto
            {
                LogID = Convert.ToInt32(reader["LogID"]),
                SenderUserID = reader["SenderUserID"].ToString(),
                Action = reader["Action"].ToString(),
                TableName = reader["TableName"].ToString(),
                RecordID = Convert.ToInt32(reader["RecordID"]),
                Timestamp = Convert.ToDateTime(reader["Timestamp"]),
                Description = reader["Description"].ToString(),
                Title = reader["Title"].ToString(),
                NotificationIcon = reader["NotificationIcon"].ToString(),
            };
            notificationDtos.Add(notificationDto);
        }
    }
    return notificationDtos;
}

private void dbChangeNotification(object sender, SqlNotificationEventArgs e)
{
    _hubContext.Clients.All.SendAsync("ReceiveNotification");
}

Index.cshtml前端脚本 #

<script>
$(document).ready(() => {
    let connection = new signalR.HubConnectionBuilder().withUrl("/appHub").build();
    connection.start();

    connection.on("ReceiveNotification", function () {
        loadData()
    });

    loadData();

    function loadData() {
        $.ajax({
            type: "Get",
            url: "/Home/GetIndex",
            success: function (value) {
                $("#tableExpense tbody").empty();
                var tablerow;
                $.each(value, (index, item) => {
                    tablerow = $("<tr/>");
                    tablerow.append(`<td><a class="fw-semibold text-primary">#${item.logID}</a></td>`)
                    tablerow.append(`<td>${item.senderUserID}</td>`)
                    tablerow.append(`<td>${item.action}</td>`)
                    tablerow.append(`<td>${item.tableName}</td>`)
                    tablerow.append(`<td>${item.recordID}</td>`);
                    tablerow.append(`<td>${item.timeStamp}</td>`);
                    tablerow.append(`<td>${item.description}</td>`);
                    tablerow.append(`<td>${item.title}</td>`);
                    tablerow.append(`<td>${item.notificationIcon}</td>`);
                    $("#tableExpense").append(tablerow);
                })
            },
            error: function (xhr, status, error) {
            }
        })
    }
});
</script>

HomeController代码 #

[HttpGet]
public async Task<IActionResult> GetIndex()
{
    var findUser = await _userManager.FindByNameAsync(User.Identity.Name);
    int id = findUser.Id;
    return Ok(_notificationDal.ListNotification(id));
}

我自己排查出几个可疑点，也想请大家帮忙验证： #

1. 查询结果列缺失：Repository的SELECT语句里根本没选Action和TableName这两个字段，但读取DataReader时却要获取这两个列的值，这肯定会抛出IndexOutOfRangeException，应该是导致500错误的直接原因。
2. 参数类型不匹配：AspNetUsers的Id默认是字符串（GUID格式），但我把它转成int传给了ListNotification方法，如果Notification表的ReceiverUserID是字符串类型的话，这里会出现类型不匹配，导致查询执行出错。
3. SqlDependency的查询限制：SqlDependency对查询语法要求很严格，比如：
   • 必须明确指定表的架构（比如dbo.Notification、dbo.AspNetUsers），不能省略
   • 尽量避免用U.Name + ' ' + U.Surname这种字符串拼接，建议换成CONCAT(U.Name, ' ', U.Surname)试试
   • 要确保数据库开启了Service Broker，SqlDependency依赖这个功能才能工作，可用以下SQL检查：

     SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsBrokerEnabled')
     

     如果返回0，需要执行语句开启：

     ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
     

希望大家能帮我定位到问题，谢谢！

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