C# ASP.NET Core 2.1调用带UDT的存储过程报参数未提供错误

在ASP.NET Core 2.1 + EF Core环境下调用带表值参数的SQL Server存储过程时，抛出参数未提供的错误，相关代码、存储过程定义、错误信息、临时修复方案如下：

原始调用代码 #

var dataTable = new DataTable();
dataTable.Columns.Add("Value", typeof(string));

foreach(var status in requiredStatuses)
{
    var row = dataTable.NewRow();
    row["Value"] = status;
    dataTable.Rows.Add(row);
}

string executeSpSql = @"EXEC [dbo].[GetCourseDelegatesByStatus] @Year, @CourseId, @DelegateUserId, @Statuses";
object[] parameters = new object[] {
    new SqlParameter("@Year", year) { SqlDbType = SqlDbType.Int },
    new SqlParameter("@CourseId", courseId) { SqlDbType = SqlDbType.Int },
    new SqlParameter("@DelegateUserId", delegateUserId) { SqlDbType = SqlDbType.UniqueIdentifier },
    new SqlParameter("@Statuses", SqlDbType.Structured)
    {
        Value = dataTable,
        TypeName = "[dbo].[StringList]"
    }
};

var result = await _context.SimpleCourseDelegate.FromSql(executeSpSql, parameters).ToListAsync();

存储过程定义 #

CREATE PROCEDURE [dbo].[GetCourseDelegatesByStatus]
    @Year INT = NULL,
    @CourseId INT = NULL,
    @DelegateUserId UNIQUEIDENTIFIER = NULL,
    @Statuses StringList READONLY

运行时错误 #

{"error":"One or more errors occurred. (The parameterized query '(@Year int,@CourseId int,@DelegateUserId uniqueidentifier,@Statu' expects the parameter '@CourseId', which was not supplied.)"}

临时修复方案 #

将@CourseId参数传值改为DBNull.Value后错误消失：

new SqlParameter("@CourseId", DBNull.Value) { SqlDbType = SqlDbType.Int },

• 核心原因是C#的null和SQL Server的NULL不是同一个概念，ADO.NET的SqlParameter不会自动把C# null转换为数据库的DBNull。
• 当给SqlParameter构造函数传入C# null作为参数值时，SqlParameter会判定没有给这个参数赋值，相当于未提供该参数，即使存储过程给该参数设置了默认值NULL，也会因为参数未被传入而抛出“参数未提供”的错误。
• 只有显式给SqlParameter的Value属性赋值DBNull.Value，ADO.NET才会识别为要给对应SQL参数传递数据库NULL值，此时存储过程才能正常走参数默认值逻辑。
• 出错那次调用中year、delegateUserId都有实际非null值，所以没有触发问题，只有courseId当时为C# null，才出现了报错。

所有可空的存储过程参数，传值时都要做null判断：如果变量值为C# null，就传DBNull.Value，否则传变量本身，示例：

new SqlParameter("@CourseId", (object)courseId ?? DBNull.Value) { SqlDbType = SqlDbType.Int },
new SqlParameter("@Year", (object)year ?? DBNull.Value) { SqlDbType = SqlDbType.Int },
new SqlParameter("@DelegateUserId", (object)delegateUserId ?? DBNull.Value) { SqlDbType = SqlDbType.UniqueIdentifier },

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