Entity Framework Stored Procedure Table Value Parameter

Entity Framework Stored Procedure Table Value Parameter

UPDATE

I've added support for this on Nuget Package - https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6)

Check out the GitHub repository for code examples.

---

Slightly off question, but none the less useful for people trying to pass user-defined tables into a stored procedure. After playing around with Nick's example and other Stackoverflow posts, I came up with this:

class Program
{
    static void Main(string[] args)
    {
        var entities = new NewBusinessEntities();

        var dt = new DataTable();
        dt.Columns.Add("WarningCode");
        dt.Columns.Add("StatusID");
        dt.Columns.Add("DecisionID");
        dt.Columns.Add("Criticality");

        dt.Rows.Add("EO01", 9, 4, 0);
        dt.Rows.Add("EO00", 9, 4, 0);
        dt.Rows.Add("EO02", 9, 4, 0);

        var caseId = new SqlParameter("caseid", SqlDbType.Int);
        caseId.Value = 1;

        var userId = new SqlParameter("userid", SqlDbType.UniqueIdentifier);
        userId.Value = Guid.Parse("846454D9-DE72-4EF4-ABE2-16EC3710EA0F");

        var warnings = new SqlParameter("warnings", SqlDbType.Structured);
        warnings.Value= dt;
        warnings.TypeName = "dbo.udt_Warnings";

        entities.ExecuteStoredProcedure("usp_RaiseWarnings_rs", userId, warnings, caseId);
    }
}

public static class ObjectContextExt
{
    public static void ExecuteStoredProcedure(this ObjectContext context, string storedProcName, params object[] parameters)
    {
        string command = "EXEC " + storedProcName + " @caseid, @userid, @warnings";

        context.ExecuteStoreCommand(command, parameters);
    }
}

and the stored procedure looks like this:

ALTER PROCEDURE [dbo].[usp_RaiseWarnings_rs]
    (@CaseID int, 
     @UserID uniqueidentifier = '846454D9-DE72-4EF4-ABE2-16EC3710EA0F', --Admin
     @Warnings dbo.udt_Warnings READONLY
)
AS

and the user-defined table looks like this:

CREATE TYPE [dbo].[udt_Warnings] AS TABLE(
    [WarningCode] [nvarchar](5) NULL,
    [StatusID] [int] NULL,
    [DecisionID] [int] NULL,
    [Criticality] [int] NULL DEFAULT ((0))
)

Constraints I found include:

1. The parameters you pass into ExecuteStoreCommand have to be in order with the parameters in your stored procedure
2. You have to pass every column in to your user-defined table, even if they are have defaults. So it seems i couldn't have a IDENTITY(1,1) NOT NULL column on my UDT

How to call stored procedure with table valued parameter from .net core Entity Framework

Finally I could able to call Table values parameter Stored procedure from my .net core service which uses EF core

I have created fake Response Domain Model

   public class MyResponse
    {
        public int Id { get; set; }
        public int Col1{ get; set; }
        public int Col2 { get; set; } 
    }

I called it Fake Domain Model because Domain model is generally table representation in CLR class but I dont have such Table in SQL Server.

Then I have Added property for this under Context class as below

public class MyQueryDbContext : DbContext
{
  public virtual DbSet<MyResponse> Data { get; set; }
    .
    .
    .
}

Then Need to create Data Table for Request parameter list, I have added like below

 DataTable table = new DataTable();
 table.Columns.Add("Id", typeof(int));
 table.Columns.Add("Col1", typeof(int));
 table.Columns.Add("Col2", typeof(int));

Then I have called Stored procedure from .net core API using EF and passed datatable as parameter like below

 var parameter = new SqlParameter("@MyRequest", SqlDbType.Structured);
 parameter.Value = table;
 parameter.TypeName = "[dbo].[MyRequestType]" // My Table valued user defined type
var response=_context.Data
                     .FromSql("EXEC [dbo].[GetMyData] @MyRequest", parameter)
                     .ToList()

You will get response in you response variable.

Pass table value type to SQL Server stored procedure via Entity Framework

Lets say you want to send a table with a single column of GUIDs.

First we need to create a structure using SqlMetaData which represents the schema of the table (columns).

The below code demonstrates one column named "Id" of the GUID is the SQL stored procedure parameter table type

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

Next you create a list of records that match the schema using SqlDataRecord.

The below code demonstrates how to add the items inside a list using the above created schema. Create a new SqlDataRecord for each of the items in the list. Replace SetGuid with the corresponding type and Replace Guid.NewGuid() as the corresponding value.
Repeat new SqlDataRecord for each item and add them to a List

var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

Then create the SqlParameter:

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds"; //@UserIds is the stored procedure parameter name
parameter.TypeName = "{Your stored procedure type name}"
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

Then simply call the stored procedure by using the Database.SqlQuery.

IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):

CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):

CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

How to call stored procedure with both of table value and scalar parametter in entity framework

Please check below code:

public DataTable CallStoredProcedure(System.String procedureName, Dictionary<string, object> parameters)
        {
            var cmd = CreateStoredProcCallCommand(procedureName, true);
            for (int i = 0; i < parameters.Count; i++)
            {
                var param = parameters.ElementAt(i);
                if (param.Value.GetType() == typeof(DataTable))
                    AddTableValuedParameter(cmd, "@" + param.Key, 0, ParameterDirection.Input, param.Value);
                else
                    AddParameter(cmd, "@" + param.Key, 2147483647, ParameterDirection.Input, param.Value);
            }

            var toReturn = new DataTable();
            CreateAndSetupAdapter(cmd).Fill(toReturn);
            return toReturn;
        }
private DbCommand CreateStoredProcCallCommand(string storedProcedureToCall, bool openConnection)
        {
            var cmd = _factoryToUse.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureToCall;
            return SetupCommand(cmd, openConnection);
        }
private DbDataAdapter CreateAndSetupAdapter(DbCommand selectCommand)
{
    var adapter = _factoryToUse.CreateDataAdapter();
    adapter.SelectCommand = selectCommand;
    return adapter;
}

 private static void AddTableValuedParameter(DbCommand cmd, string parameterName, int length, ParameterDirection direction, object value)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = parameterName;
            param.SqlDbType = SqlDbType.Structured;
            param.Size = length;
            param.Value = value;
            param.Direction = direction;
            cmd.Parameters.Add(param);
        }
private static void AddParameter(DbCommand cmd, string parameterName, int length, ParameterDirection direction, object value)
        {
            var dummyParam = new EntityParameter() { Value = value };
            var parameter = cmd.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.Direction = direction;
            parameter.Size = length;
            parameter.Value = value;
            parameter.DbType = dummyParam.DbType;
            cmd.Parameters.Add(parameter);
        }

---

Related Topics