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?
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).
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.
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";
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", 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
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.