Entity Framework 4 and TableValue Type in Stored Procedure (Fix)

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