通过实体框架将表值类型传递给SQL Server存储过程 [英] Pass table value type to SQL Server stored procedure via Entity Framework

问题描述

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

I created a user-defined table type in SQL Server:

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

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

And I'm using a stored procedure to insert records into the database:

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

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

And I would like to use EF to execute this stored procedure, but here's the problem: how can I pass a user defined table to the stored procedure?

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

I tried adding the stored procedure to the model, but I'm unable to find the desired stored procedure in the updated context.

我要执行的操作是对表执行批量插入，这是我当前正在使用的方法：

What I'm trying to do is to execute a bulk insert to a table, here's the method that I'm currently using:

List<items> itemToInsertToDB = //fetchItems;

foreach(items i in itemToInsertToDB)
{
     context.sp_InsertToTable(i.ColumnA, i.ColumnB)
}

当前，我使用 foreach 循环遍历列表以将项目插入数据库，但是如果列表中有很多项目，那么就会出现性能问题，因此，我正在考虑将列表传递给存储过程并在其中进行插入。

Currently, I use a foreach loop to loop through the list to insert item to DB, but if the list have a lot of items, then there will be a performance issue, so, I'm thinking of passing a list to the stored procedure and do the insert inside.

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

So how to solve this problem? or are there any better ways to do this?

推荐答案

让我们说您想发送一个包含GUID单列的表。

Lets say you want to send a table with a single column of GUIDs.

首先，我们需要使用 SqlMetaData 代表表的架构（列）。

First we need to create a structure using SqlMetaData which represents the schema of the table (columns).

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中，创建用户定义的表类型（I在它们的后缀TTV，表类型值）：

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):

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

然后指定类型作为参数（不要忘记，表类型值必须是只读的！）：

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):

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

这篇关于通过实体框架将表值类型传递给SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！