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

Question

我在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，但是如果列表中有很多项目，则将存在性能问题，因此，我正在考虑将列表传递给存储过程，并在里面做插入。

那么如何解决这个问题呢？还是有更好的方法来做到这一点？

Answer 1

假设您要发送一个包含一列GUID的表。

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

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

接下来，使用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";
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", 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