使用List<DateTime>发起JSON Post请求时遇SQL参数缺失异常

异常信息 #

Microsoft.Data.SqlClient.SqlException: '参数化查询"(@PK int,@Title nvarchar(8),@Description nvarchar(8),@StartDate "期望参数"@RecurrenceException"，但未提供。'

场景说明 #

通过JSON Post请求调用AddAppointment()方法时，RecurrenceException属性确实为预期的null值，但API控制器抛出上述异常。

相关代码 #

客户端Razor Page代码 #

async Task AddAppointment(SchedulerCreateEventArgs e)
{
    UvwHolidayPlanner holidayPlannerItem = e.Item as UvwHolidayPlanner;

    List<DateTime> lst = new List<DateTime>();

    holidayPlanner.Pk = holidayPlannerItem.Pk;
    holidayPlanner.Title = holidayPlannerItem.Title;
    holidayPlanner.Description = holidayPlannerItem.Description;
    holidayPlanner.StartDate = holidayPlannerItem.StartDate;
    holidayPlanner.EndDate = holidayPlannerItem.EndDate;
    holidayPlanner.IsAllDay = holidayPlannerItem.IsAllDay;

    if (holidayPlannerItem.RecurrenceRule == null)
    {
        holidayPlanner.RecurrenceRule = " ";
    }
    else
    {
        holidayPlanner.RecurrenceRule = holidayPlannerItem.RecurrenceRule;
    }

    holidayPlanner.RecurrenceException = holidayPlannerItem.RecurrenceException;
    holidayPlanner.RecurrenceId = holidayPlannerItem.RecurrenceId;

    await http.CreateClient("ClientSettings").PostAsJsonAsync<UvwHolidayPlanner>($"{_URL}/api/HolidayPlannerOperations/HolidayPlanner", holidayPlanner);

    HolidayPlanners = (await http.CreateClient("ClientSettings").GetFromJsonAsync<List<UvwHolidayPlanner>>($"{_URL}/api/lookup/HolidayPlanner"))
    .OrderBy(t => t.Title)
    .ToList();

    StateHasChanged();
}

实体类代码 #

public class UvwHolidayPlanner
{
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public bool IsAllDay { get; set; }
    public int Pk { get; set; }
    public string RecurrenceRule { get; set; }
    public List<DateTime> RecurrenceException { get; set; }
    public int RecurrenceId { get; set; }
}

API控制器代码 #

[HttpPost]
[Route("HolidayPlanner")]
public void Post([FromBody] UvwHolidayPlanner item)
{
    string SQLSTE = "EXEC [dbo].[usp_AddHolidayPlanner] @PK, @Title, @Description, @StartDate, @EndDate, @IsAllDay, @RecurrenceRule, @RecurrenceException, @RecurrenceId";
    using (var context = new TestAppContext())
    {
        List<SqlParameter> param = new List<SqlParameter>
        {
            new SqlParameter { ParameterName = "@PK", Value = item.Pk },
            new SqlParameter { ParameterName = "@Title", Value = item.Title },
            new SqlParameter { ParameterName = "@Description", Value = item.Description },
            new SqlParameter { ParameterName = "@StartDate", Value = item.StartDate },
            new SqlParameter { ParameterName = "@EndDate", Value = item.EndDate },
            new SqlParameter { ParameterName = "@IsAllDay", Value = item.IsAllDay },
            new SqlParameter { ParameterName = "@RecurrenceRule", Value = item.RecurrenceRule },
            new SqlParameter { ParameterName = "@RecurrenceException", Value = item.RecurrenceException },
            new SqlParameter { ParameterName = "@RecurrenceId", Value = item.RecurrenceId }
        };

        context.Database.ExecuteSqlRaw(SQLSTE, param);
    }
}

原因分析 #

1. Null参数未被正确识别：当RecurrenceException为null时，直接赋值给SqlParameter.Value会导致SQL Server认为该参数未提供，因为.NET的null无法直接映射到SQL的参数默认值逻辑。
2. 类型不匹配：List<DateTime>是.NET集合类型，无法直接作为SQL参数传递，SQL Server没有对应的原生类型支持这种直接传入。

解决方案 #

1. 处理Null参数 #

将null值替换为DBNull.Value，确保SQL能识别该参数已提供且值为null：

new SqlParameter 
{ 
    ParameterName = "@RecurrenceException", 
    Value = item.RecurrenceException == null ? DBNull.Value : JsonSerializer.Serialize(item.RecurrenceException),
    SqlDbType = SqlDbType.NVarChar,
    Size = -1 // 对应NVARCHAR(MAX)
}

2. 转换集合类型为SQL兼容格式 #

将List<DateTime>序列化为JSON字符串，在存储过程中通过OPENJSON解析使用：

存储过程修改示例

CREATE PROCEDURE [dbo].[usp_AddHolidayPlanner]
    @PK INT,
    @Title NVARCHAR(MAX),
    @Description NVARCHAR(MAX),
    @StartDate DATETIME,
    @EndDate DATETIME,
    @IsAllDay BIT,
    @RecurrenceRule NVARCHAR(MAX),
    @RecurrenceException NVARCHAR(MAX) = NULL, -- 设置默认值为NULL
    @RecurrenceId INT
AS
BEGIN
    -- 解析JSON格式的异常日期列表
    DECLARE @ExceptionDates TABLE (ExceptionDate DATETIME)
    IF @RecurrenceException IS NOT NULL AND @RecurrenceException != ''
    BEGIN
        INSERT INTO @ExceptionDates (ExceptionDate)
        SELECT CONVERT(DATETIME, value) FROM OPENJSON(@RecurrenceException)
    END

    -- 后续业务逻辑，比如插入主表+异常日期表
    -- ...
END

3. 客户端可选优化（非必须） #

如果需要确保null值在JSON序列化时被保留，可以配置JsonSerializerOptions：

var options = new JsonSerializerOptions { DefaultIgnoreCondition = JsonIgnoreCondition.Never };
await http.CreateClient("ClientSettings").PostAsJsonAsync<UvwHolidayPlanner>(url, holidayPlanner, options);

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