Enter a table type value into a SQL Server stored procedure through the Entity Framework

Question

I created a custom table type in SQL Server:

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

And I use the 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 

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 a stored procedure?

I tried to add the stored procedure to the model, but I could not find the desired stored procedure in the updated context.

What I'm trying to do is do a bulk insert into a table, here is the method I'm using now:

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

I am currently using the foreach to cycle through the list to insert an item in the database, but if there are a lot of items in the list, then there will be a performance problem, so I think about passing the list to the stored procedure and doing an internal insert.

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

Answer 1

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

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

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

Then you create a list of records that match the schema using SqlDataRecord .

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

Then create an SqlParameter :

 var parameter = new SqlParameter(); parameter.SqlDbType = SqlDbType.Structured; parameter.ParameterName = "@UserIds"; parameter.Value = table; var parameters = new SqlParameter[1] { parameter }; 

Then just call the stored procedure using Database.SqlQuery .

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

In SQL Server, create your user-defined table type (I suffix them with TTV, table value):

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

Then specify the type as a parameter (do not forget that table type values ​​should be read-only!):

 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 

Answer 2

I suggest you not use a stored procedure to insert bulk data, but simply rely on the Entity Framework insert mechanism.

 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(); 

An Entity structure will efficiently perform these inserts in a single transaction and (usually) when executing a single request, which is almost equivalent to executing a stored procedure. This is better than relying on a stored procedure to insert most of the data.