17

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

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)
}

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?

Improve this question
1

2 Answers 2

Reset to default
18

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

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

The below code demonstrates one column named "Id" of the GUID is the SQL stored procedure parameter table type

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

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

The below code demonstrates how to add the items inside a list using the above created schema. Create a new SqlDataRecord for each of the items in the list. Replace SetGuid with the corresponding type and Replace Guid.NewGuid() as the corresponding value. Repeat new SqlDataRecord for each item and add them to a List

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

Then create the 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
};

Then simply call the stored procedure by using the 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
{

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Where did the.SetGuid come from ?
SqlDataRecord.SetGuid()
1) mouseover result in the debugger will throw bogus exception "stored procedure Must declare the scalar variable" because it's trying to evaluate the parameter twice. Use .ToList() (as above) to avoid this. 2) I was getting exception "The table type parameter '@table' must have a valid type name." which was fixed by specifying the parameter type: parameter.TypeName = "dbo.TestType" (with "dbo" prefix)
I don't think BurnsBA is trying to ask a question about the code. I think he's trying to suggest improvements. At least, that's how his comment reads to me.
Hi. First of all, thanks for your solution. I used it, but did not work for me. I spent about 2 hours figuring out what is wrong. See stackoverflow.com/questions/46908349/…. The problem was, that I forgot to pass the parameter while calling "myContext.Database.SqlQuery<User>("GetUsers", parameters)". It would be great if you update your answer, so it looks like this "myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)". Thank, Martin
|
3

I suggest you not using Stored Procedure to insert bulk data, but just rely to 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();

Entity framework will smartly perform those insertion in single transaction and (usually) in single query execution, which will almost equal to executing stored procedure. This is better rather than relying on stored procedure just to insert bulk of data.

Improve this answer

3 Comments

Thank you for your suggestion, I know that Entity framework have a function called "bulkinsert" efbulkinsert.codeplex.com , but I was required to use stored procedure for easier maintenance.
EF works well unless you're in a very complicated distributed transaction scenario using transaction scopes.
EF is not that smart. Save changes executes a transaction, but it issues individual insert statements for each entity inserted. That's why the BulkInsert is a separate, paid library, although older, free versions exists. By default, EF will issue a separate insert statement for each item inserted. The performance is horrible. For bulk inserts, use SqlBulkCopy or use the free version of the bulk insert extension.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.