Executing Stored Procedure with User Defined Table Type Parameter in Entity Framework Using EntityFrameworkExtras

Table of Contents

1. Introduction
2. Pre-requisite
3. Software Requirement
4. Defining User-defined type
5. Defining Stored procedure
6. Adding EntityFrameworkExtras package in project
7. Defining classes for SQL objects
8. Executing Stored Procedure from object context
9. Summary

Introduction

As a web developer, if you have ever worked on ASP.NET enterprise applications using entity framework, several times you come into situations where there is a need to pass a bunch of records in stored procedure. The handy solution is to pass user defined table type as a parameter in stored procedure, sounds cool!!

But soon, you may realize that your application is using entity framework and Entity framework does not support user defined type by default. Now what to do? Now after analysing different approaches, I found this perfect solution to address this type of situation. Here we go..

So to support user defined type in entity framework, we implement EntityFrameworkExtras package available on NuGet. Below is the step by step implementation for executing stored procedure with user defined table type parameter in entity framework using EntityFrameworkExtras.

Pre-Requisite

This tip assumes that readers have already hands on and knowledge of basics of entity framework, ASP.NET MVC and SQL Server objects, e.g., user-defined type and stored procedure.

Software Requirement

• Visual Studio
• SQL SERVER
• Entity Framework Extras

Defining User-defined Type

User-defined table type will allow you to declare table structure as a type in SQL Server, which may be used as a parameter for stored procedure. Below is a sample code snippet to define a user-defined table type which allows us to pass data table as a parameter.

CREATE TYPE [dbo].[UserDefineType] AS TABLE(
     [ID] [int] NULL,
     [Value] [int] NULL
)
GO

Defining Stored Procedure

Now define a stored procedure that uses the above mentioned user-defined table type as parameter along with other parameters.The below code snippet has 3 parameters defined @name, @description and @udt as parameters where @udt is user-defined table type parameter.

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

Adding EntityFrameworkExtras Package in Project

Add EntityFrameworkExtras package from Nuget in your project as per your version of entity framework.

• Entity Framework 4 - https://www.nuget.org/packages/EntityFrameworkExtras/
• Entity Framework 5 - https://www.nuget.org/packages/EntityFrameworkExtras.EF5/
• Entity Framework 6 - https://www.nuget.org/packages/EntityFrameworkExtras.EF6/

Follow the below steps to add entity framework extras package in the project.

1. Right click references in your project and select Manage NuGet package option from context menu
2. Search EntityFrameworkExtras package and select install.

Defining Classes for SQL Objects

Define Class for User-defined Table Type

Now create a class in your project that represents user-defined table type. Decorate class with [UserDefinedTableType("UserDefinedType")] attribute.

Decorate class properties with [UserDefinedTableTypeColumn()] attribute.

Note the name passed in constructor must match with name of user-defined table type in SQL server.

namespace MyDemoApplication
{
    using EntityFrameworkExtras.EF6;
/// 
/// Represents User Defined Type for database
/// 
 [UserDefinedTableType("UserDefinedType")]
 public class UserDefinedType
  {
    [UserDefinedTableTypeColumn(1)]
    public int ID { get; set; }
 
    [UserDefinedTableTypeColumn(2)]
    public int Value { get; set; }
  }
}

Define Class for Stored Procedure

Create another class in your project that will represent stored procedure.

Decorate class with [StoredProcedure("NameOfStroredProcedure")] attribute.

Decorate class properties with [StoredProcedureParameter()] attribute. Mention type and name of parameter in [StoredProcedureParameter()] constructor for each property.

using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace MyDemoApplication
{
   using EntityFrameworkExtras.EF6;
    /// 
    /// Define TestStoredProcedure stored procedure.
    /// 
    [StoredProcedure("TestStoredprocedure")]
    public class TestStoredProcedure
    {
        [StoredProcedureParameter(SqlDbType.NVarChar, ParameterName = "name")]
        public string Name { get; set; }
 
        [StoredProcedureParameter(SqlDbType.NVarChar, ParameterName = "description")]
        public string Description { get; set; }    
 
        [StoredProcedureParameter(SqlDbType.Udt, ParameterName = "udt")]
        public List<UserDefinedType> UserDefinedType { get; set; }
    }
}

Executing Stored Procedure from Object Context

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()
        {
            new UserDefinedType() {ID = 1, Value = 16},
            new UserDefinedType() {ID = 2, Value = 6},
            new UserDefinedType() {ID = 3, Value = 100},
        }
    };

context.Database.ExecuteStoredProcedure(procedure);

Summary

In this tip, we saw how to execute stored procedure with user-defined table type parameters using entity framework in best and efficient way without violating OOPs concepts. This tip utilizes the capabilities of entity framework extras package available on NuGet.