Executing stored procedure with user defined table type parameter in entity framework using EntityFrameworkExtras

Entity framework does not support user defined type by default. So to support user defined type in entity framework we implement EntityFrameworkExtras package available on NuGet.

Below are step by step implementation for executing stored procedure with user defined table type parameter in entity framework using EntityFrameworkExtras
Step 1: Create user defined type in SQL SERVER
CREATE TYPE [dbo].[UserDefineType] AS TABLE(
     [ID] [int] NULL,
     [Value] [int] NULL
)
GO

Step 2: Create stored procedure and use above defined type in step 1 as parameter of stored procedure along with other parameter if needed.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestStoredProcedure]
     (@name varchar(100)
     ,@description varchar(500)
     ,@udt UserDefineType READONLY
     )
AS
BEGIN
—- Your logic
INSERT INTO [dbo].[TestTable]
           (
             [ID]
           , [Value])
     SELECT ID
           , Value          
FROM @udt
—- Your logic
END
Step 3: Add EntityFrameworkExtras package from Nuget in your project as per your version of entity framework.
3.1: Right click references in your project and select Manage NuGet package option from context menu
3.2: Search EntityFrameworkExtras package and select install.











Step 4: Create a class in your project that will represent user defined type created in step 1.

/// <summary>
/// Represents User Defined Type for database
/// </summary>
[UserDefinedTableType("UserDefinedType")]
public class UserDefinedType
{
    [UserDefinedTableTypeColumn(1)]
    public int ID { getset; }
    [UserDefinedTableTypeColumn(2)]
    public int Value { getset; }
}

Note: UserDefinedTableType("UserDefinedType") attribute take name of your user defined type in SQL Server.

Step 5: Create another class in your project that will represent stored procedure defined in step 2.
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyDemoApplication
{
    /// <summary>
    /// Define TestStoredProcedure stored procedure.
    /// </summary>
    [StoredProcedure("TestStoredprocedure")]
    public class TestStoredProcedure    
{
        [StoredProcedureParameter(SqlDbType.NVarCharParameterName = "name")]
        public string Name { getset; }

        [StoredProcedureParameter(SqlDbType.NVarCharParameterName = "description")]
        public string Description { getset; }    

        [StoredProcedureParameter(SqlDbType.UdtParameterName = "udt")]
        public List<UserDefinedTypeUserDefinedType { getset; }
    }
}
Note: Third property parameterName value must be same as defined in stored procedure

Step 6: Now finally execute stored procedure using dbcontext or ObjectContext.

DbContext context = new DbContext("ConnectionString");
var procedure = new TestStoredProcedure()
   {
        Name = "Ashish",
        Description = "Executing user defined table type in entity framework",
        UserDefinedType = new List<UserDefinedType>()
        {
            new UserDefinedType() {ID = 1, Value = 16}
        }
    };
context.Database.ExecuteStoredProcedure(procedure);

That’s all.


Advertisements

2 thoughts on “Executing stored procedure with user defined table type parameter in entity framework using EntityFrameworkExtras

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s