Razor Pages中存储过程返回Null值问题求助

问题背景 #

现有两个SQL Server存储过程AvailableHourSlots和AvailableHalfAnHourSlots，在SQL Server Management Studio(SSMS)中执行可正常返回结果，但在Razor Pages中通过EF Core调用时返回Null值。相关代码如下：

存储过程代码 #

AvailableHourSlots

CREATE PROCEDURE [dbo].[AvailableHourSlots] 
    @ActivityName nvarchar(max) ,
    @BookedDate datetime2(7)
AS  
    SELECT DISTINCT HourlyBasedTime 
    FROM ManageBooking AS mb, HourlyBased AS hb  
    WHERE bookingdate = @BookedDate 
      AND ActivityName = @ActivityName
      AND (SUBSTRING(HourlyBasedTime, 1, 2) <> SUBSTRING(mb.PreferredTimeslot, 1, 2))

AvailableHalfAnHourSlots

CREATE PROCEDURE [dbo].[AvailableHalfAnHourSlots] 
    @ActivityName nvarchar(max) ,
    @BookedDate datetime2(7)
AS  
    SELECT DISTINCT hh.halfanhourtime
    FROM ManageBooking AS mb, Halfanhour hh
    WHERE bookingdate = @BookedDate
      AND ActivityName = @ActivityName 
      AND (SUBSTRING(hh.halfanhourtime, 1, 5) <> SUBSTRING(mb.PreferredTimeslot, 1, 5))
      AND (RIGHT(hh.halfanhourtime, 5) <> RIGHT(mb.PreferredTimeslot, 5))

实体模型 #

namespace ActivityBookingSystem.Models
{
    public class HourlyBasedView
    {
        public string HourlyBasedTime { get; set; }   
    }
}

EF Core上下文配置 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{            
    modelBuilder.Entity<NonOperatingDaysView>().HasNoKey();
    modelBuilder.Entity<AvailableOneHourSlots>().HasNoKey();
    modelBuilder.Entity<HourlyBasedView>().HasNoKey();
}

public DbSet<ActivityBookingSystem.Models.HourlyBasedView> HourlyBasedView { get; set; }

前端AJAX调用 #

function GetAvailableSlots(e) {
        var rootPath = '@Url.Content("~")';
        var ActivityList = document.getElementById("DrpDwnActivityList");
        var ActivityListValue = ActivityList.options[ActivityList.selectedIndex].value;
        var BookedDate = document.getElementById("DateBookedDate").value;
        
        $.ajax({
            type: "Get",
            url: rootPath + "/ManageBooking/Create?handler=GetAvailableSlots",
            headers: { "RequestVerificationToken": $('input[name="__RequestVerificationToken"]').val() },
            data: { Duration: e.value,ActivityName:ActivityListValue,BookedDate:BookedDate },
            dataType: "json",
            success: function (result) {
                alert(result);
            },
            error: function (data) {
                alert(data);
            }
        });
    }

后端Razor Page处理代码（修改后） #

public async Task<JsonResult> OnGetGetAvailableSlotsAsync(string ActivityName, string BookedDate, string Duration)
{
    Console.WriteLine(Duration);
    DateTime BookingDate = Convert.ToDateTime(BookedDate);
    ActivityName = ActivityName.ToString();
            
    if (Duration == "1 Hour")
    {
        var LstPreferredTimeSlot = _context.HourlyBasedView.FromSqlRaw("EXEC dbo.AvailableHourSlots @BookedDate = {0}, @ActivityName = {1}", BookingDate, ActivityName)
                    .AsNoTracking().ToList();
    }

    return new JsonResult(LstPreferredTimeSlot);
}

排查与解决步骤 #

1. 修复变量作用域问题 #

当前代码中，LstPreferredTimeSlot仅在if块内部声明，外部return语句无法访问该变量，会导致编译错误或返回未初始化的Null。需将变量声明移至if块外部：

public async Task<JsonResult> OnGetGetAvailableSlotsAsync(string ActivityName, string BookedDate, string Duration)
{
    Console.WriteLine(Duration);
    // 提前声明并初始化变量，避免返回Null
    var LstPreferredTimeSlot = new List<HourlyBasedView>();
    
    // 安全转换日期，处理格式错误情况
    if (!DateTime.TryParse(BookedDate, out DateTime BookingDate))
    {
        return new JsonResult(new { success = false, message = "日期格式无效" });
    }
    
    // 校验活动名称非空
    if (string.IsNullOrEmpty(ActivityName))
    {
        return new JsonResult(new { success = false, message = "活动名称不能为空" });
    }
            
    if (Duration == "1 Hour")
    {
        // 匹配存储过程参数顺序，避免绑定错误
        LstPreferredTimeSlot = _context.HourlyBasedView
            .FromSqlRaw("EXEC dbo.AvailableHourSlots @ActivityName = {0}, @BookedDate = {1}", ActivityName, BookingDate)
            .AsNoTracking()
            .ToList();
    }
    // 新增半小时时段的处理分支
    else if (Duration == "30 Minutes")
    {
        // 调用AvailableHalfAnHourSlots的逻辑
        // LstPreferredTimeSlot = _context.xxx.FromSqlRaw(...).ToList();
    }

    return new JsonResult(LstPreferredTimeSlot);
}

2. 匹配存储过程参数顺序与命名 #

存储过程AvailableHourSlots的参数定义顺序为@ActivityName在前、@BookedDate在后，即使使用命名参数，建议保持传递顺序与定义一致，避免潜在的参数绑定错误。

3. 验证日期转换的正确性 #

前端传入的日期字符串可能存在格式差异，使用DateTime.TryParse或DateTime.TryParseExact进行安全转换，避免转换失败导致参数错误：

if (!DateTime.TryParse(BookedDate, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out DateTime BookingDate))
{
    return new JsonResult(new { success = false, message = "请输入有效的日期格式" });
}

4. 确认EF Core无键实体映射 #

确保实体类HourlyBasedView的属性名与存储过程返回的列名完全匹配，可显式添加[Column]属性强制映射：

using System.ComponentModel.DataAnnotations.Schema;

namespace ActivityBookingSystem.Models
{
    public class HourlyBasedView
    {
        [Column("HourlyBasedTime")]
        public string HourlyBasedTime { get; set; }   
    }
}

5. 启用EF Core日志查看执行SQL #

在Program.cs中添加日志配置，查看实际执行的SQL语句和参数值，对比SSMS中的测试语句是否一致：

builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", Microsoft.Extensions.Logging.LogLevel.Information);

日志会输出完整的执行SQL，可直接复制到SSMS中运行，验证是否返回结果。

6. 校验前端参数传递正确性 #

在后端添加参数日志输出，确认前端传递的值与SSMS测试值一致：

Console.WriteLine($"接收参数：Duration={Duration}, ActivityName={ActivityName}, BookedDate={BookedDate}");

7. 优化存储过程逻辑 #

原存储过程使用隐式连接，可能在无匹配预订记录时返回空。建议改用LEFT JOIN改写，确保无预订时也能返回所有可用时段：

CREATE PROCEDURE [dbo].[AvailableHourSlots] 
    @ActivityName nvarchar(max) ,
    @BookedDate datetime2(7)
AS  
    SELECT DISTINCT hb.HourlyBasedTime 
    FROM HourlyBased AS hb
    LEFT JOIN ManageBooking AS mb 
        ON SUBSTRING(hb.HourlyBasedTime, 1, 2) = SUBSTRING(mb.PreferredTimeslot, 1, 2)
        AND mb.bookingdate = @BookedDate 
        AND mb.ActivityName = @ActivityName
    WHERE mb.PreferredTimeslot IS NULL

内容的提问来源于stack exchange，提问作者Teena