... A developer's blog: EF4 using TableValue type in Storeprocedure

The EF5 has been released with .Net framework 4.5 and now it’s supporting the user defined table valued types as parameter in stored procedure. In earlier versions of Entity framework it was not supported. For e.g. 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 the same but none action has been taken and ticket was closed since this feature is shipped with .net 4.5.

For those who can not migrate to 4.5 and are looking for a work around how to work with these kinda stored procedures. I’ve created a work around solution for that. Let’s take 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 database this StoredProcedure will not be listed even if you use FunctionImport to call the stored procedure. Now to workaround I’ve created a custom class that executes this stored procedure as a Sql statement from 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 work’s 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();
}