SQL Server 2018下编写接收nvarchar列表的存储过程及LINQ调用方法

一、SQL Server 端配置 #

首先需要创建表值参数（TVP） 作为存储过程的入参类型，这是SQL Server官方推荐的集合参数传递方案，无SQL注入风险，性能远高于字符串拆分等实现，支持万级别的元素传入，完全满足1到有限个元素的使用需求。

步骤1：创建自定义表值类型 #

该类型仅需创建一次，后续所有需要传入nvarchar列表的存储过程都可以复用：

CREATE TYPE dbo.NvarcharList AS TABLE 
(
    -- 可根据业务需要调整字段长度，比如NVARCHAR(100)
    Value NVARCHAR(MAX) NOT NULL 
);
GO

步骤2：编写接收表值参数的存储过程 #

注意表值参数必须加READONLY修饰，不允许在存储过程内部修改参数内容：

CREATE PROCEDURE dbo.YourStoredProcedureName
    @InputList dbo.NvarcharList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 此处替换为你的业务逻辑，示例：查询匹配列表值的记录
    SELECT * FROM dbo.YourTable 
    WHERE TargetField IN (SELECT Value FROM @InputList);
END
GO

二、C# 端通过LINQ调用方案 #

以下分别提供EF Core和EF6两种常用LINQ框架的实现方式：

前置准备 #

将你创建的自定义表类型、存储过程同步到你的EF数据模型中，如果是Code First模式，需要先创建和表值结构对应的实体类：

// 和SQL端dbo.NvarcharList的结构完全对应
public class NvarcharListItem
{
    public string Value { get; set; }
}

在DbContext的OnModelCreating方法中配置该实体：

// 表值类型无主键，配置为无键实体
modelBuilder.Entity<NvarcharListItem>().HasNoKey();

2.1 EF Core 调用示例 #

// 你的业务输入列表
List<string> inputList = new List<string> { "值1", "值2", "值3" };

// 构造表值参数对应的DataTable
DataTable tvpTable = new DataTable();
tvpTable.Columns.Add("Value", typeof(string));
foreach (var item in inputList)
{
    tvpTable.Rows.Add(item);
}

// 构造SQL参数，指定类型为Structured（表值参数）
var param = new SqlParameter("@InputList", SqlDbType.Structured)
{
    TypeName = "dbo.NvarcharList", // 和SQL端自定义表类型名完全一致
    Value = tvpTable
};

// LINQ方式执行存储过程，获取返回结果（示例返回的是YourEntity实体集合）
var result = yourDbContext.YourEntities
    .FromSqlRaw("EXEC dbo.YourStoredProcedureName @InputList", param)
    .ToList();

2.2 EF6 调用示例 #

和EF Core逻辑基本一致，仅执行方法有区别：

// 同上构造tvpTable和param参数
var result = yourDbContext.Database
    .SqlQuery<YourEntity>("EXEC dbo.YourStoredProcedureName @InputList", param)
    .ToList();

注意事项 #

• 自定义表类型的字段名、数据类型必须和C#端DataTable的列、实体类属性完全对应，否则会触发类型不匹配错误
• 建议根据实际业务限制Value字段的nvarchar长度，避免不必要的性能损耗
• 若传入列表为空，可提前做判断返回默认结果，避免无效数据库请求

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