通过实体框架将表值类型传递给SQL Server存储过程

本文关键字：SQL 存储过程 Server 类型 实体 框架 | 更新日期: 2023-09-27 18:22:23

我在SQL Server中创建了一个用户定义的表类型：

CREATE TYPE dbo.TestType AS TABLE 
(
    ColumnA int,
    ColumnB nvarchar(500)
)

我正在使用一个存储过程将记录插入数据库：

create procedure [dbo].[sp_Test_CustomType]
   @testing TestType READONLY
as
    insert into [dbo].[myTable]
        select ColumnA, ColumnB 
        from @testing

我想使用EF来执行这个存储过程，但问题是：如何将用户定义的表传递给存储过程？

我尝试将存储过程添加到模型中，但在更新的上下文中找不到所需的存储过程。

我想做的是对表执行大容量插入，下面是我目前使用的方法：

List<items> itemToInsertToDB = //fetchItems;
foreach(items i in itemToInsertToDB)
{
     context.sp_InsertToTable(i.ColumnA, i.ColumnB)
}

目前，我使用foreach循环遍历列表，将项插入到DB中，但如果列表中有很多项，则会出现性能问题，因此，我正在考虑将列表传递给存储过程，并在内部进行插入。

那么如何解决这个问题呢？或者有更好的方法吗？

通过实体框架将表值类型传递给SQL Server存储过程

假设您希望发送一个具有单列GUID的表。

首先，我们需要使用SqlMetaData创建一个结构，该结构表示表（列）的模式。

下面的代码展示了一个名为"；Id"；GUID的是SQL存储过程参数表类型

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

接下来，您将使用SqlDataRecord创建与架构匹配的记录列表。

下面的代码演示了如何使用上面创建的模式在列表中添加项。为列表中的每个项创建一个新的SqlDataRecord。将SetGuid替换为相应的类型，将Guid.NewGuid（）替换为相应值。为每个项目重复新的SqlDataRecord，并将它们添加到列表中

var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

然后创建SqlParameter:

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds"; //@UserIds is the stored procedure parameter name
parameter.TypeName = "{Your stored procedure type name}"
parameter.Value = table;
var parameters = new SqlParameter[1]
{
  parameter
};

然后使用Database.SqlQuery.简单地调用存储过程

IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

在SQL Server中，创建用户定义的表类型（我在它们后面加上TTV，表类型值）：

CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

然后将类型指定为参数（别忘了，表类型值必须是只读的！）：

CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON
  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

我建议您不要使用存储过程来插入大容量数据，而只使用实体框架插入机制。

List<items> itemToInsertToDB = //fetchItems;
foreach(items i in itemToInsertToDB)
{
    TestType t = new TestType() { ColumnA = i.ColumnA, ColumnB = i.ColumnB };
    context.TestTypes.Add(t);
}
context.SaveChanges();

实体框架将在单个事务和（通常）单个查询执行中智能地执行这些插入，这几乎等于执行存储过程。这比仅仅依靠存储过程来插入大量数据要好。

相关文章：