Entity Framework 5 has been released with .Net framework 4.5 and it now supports user-defined table valued types as a parameter in a Stored Procedure. In earlier versions of Entity Framework it was not supported. For example EF4 is widely being used with .Net Framework 4.0. And these types are not supported in that version. Even some people raise a user voice ticket for it but no action has been taken and the ticket was closed since this feature is shipped with .Net 4.5.
For those who cannot migrate to 4.5 and are looking for a work around of how to work with these kinds of Stored Procedures, I've created a work around solution for that. Let's use some database test objects.
Table
CREATE TABLE [dbo].[testTable](
[mID] [uniqueidentifier] NOT NULL,
[nID] [int] NOT NULL,
[Desc] [varchar](2000) NOT NULL,
PRIMARY KEY CLUSTERED
(
[mID] ASC,
[nID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
User Defined Table Type
CREATE TYPE [dbo].[tbltypeTestTable] AS TABLE(
[mID] [uniqueidentifier] NULL,
[nID] [int] NULL,
[Desc] [varchar](2000) NULL
)
Multi Row Insert Stored Procedure
CREATE PROCEDURE [dbo].[Insert_TableValuedTypeData]
@Paramtable dbo.[tbltypeTestTable] READONLY
AS
begin
merge dbo.testTable as target
using ( select mID, nID, [Desc]
from @Paramtable) as source
on ( target.mID = source.mID and target.nID = source.nID)
when matched then
update set target.[Desc] = source.[Desc]
when not matched then
insert values (source.mID,source.nID,source.[Desc]);
return @@error
end
Now if you try to generate the model from the database this Stored Procedure will not be listed even if you use FunctionImport to call the Stored Procedure. Now to work around that I've created a custom class that executes this Stored Procedure as a SQL statement from a DBContext object.
Extension method
public static class DbContextExtension
{
// Extension method of DbContext object
public static void ExecuteStoreProcedure(this DbContext @this, string storeProcName, params object[] parameters)
{
string command = "EXEC " + storeProcName + " @Paramtable";
@this.Database.ExecuteSqlCommand(command, parameters);
}
}
Now we'll look at the class that will prepare the data and pass it to this extension method to execute.
Callee
public class mnEntity
{
public bool AddMultipleRecords()
{
Test_Entities testEntities = new Test_Entities();
var dt = new DataTable();
dt.Columns.Add("mID");
dt.Columns.Add("nID");
dt.Columns.Add("Desc");
dt.Rows.Add(Guid.NewGuid(), 1, "Test2");
dt.Rows.Add(Guid.NewGuid(), 2, "Test2");
dt.Rows.Add(Guid.NewGuid(), 3, "Test2");
dt.Rows.Add(Guid.NewGuid(), 4, "Test2");
dt.Rows.Add(Guid.NewGuid(), 5, "Test2");
var items = new SqlParameter("Paramtable", SqlDbType.Structured);
items.Value = dt;
items.TypeName = "dbo.tbltypeTestTable";
testEntities.ExecuteStoreProcedure("Insert_TableValuedTypeData", items);
return true;
}
}
Now let's test this method, if it works and call the SP with multiple values insertion.
Main
static void Main(string[] args)
{
mnEntity entity = new mnEntity();
try
{
entity.AddMultipleRecords();
Console.WriteLine("Success!!!");
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
Console.Read();
}
Output
![Output.jpg]()