How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?

Entity Framework stored procedure table-valued parameters

Entity Framework itself does not directly support passing table-valued parameters to stored procedures. However, a workaround can be implemented using the ObjectContext method of ExecuteStoreQuery .

In the above code, there is a common ExecuteStoredProcedure method in the repository:

public IEnumerable<T> ExecuteStoredProcedure<T>(string procedureName, params object[] parameters)
{
    StringBuilder command = new StringBuilder();
    command.Append("EXEC ");
    command.Append(procedureName);
    command.Append(" ");

    // 为每个传入的参数添加占位符
    for (int i = 0; i < parameters.Length; i++)
    {
        if (i > 0)
            command.Append(",");

        command.Append("{" + i + "}");
    }

    return this.context.ExecuteStoreQuery<T>(command.ToString(), parameters);
}

When passing table-valued parameters using this method, you may get errors such as "Table type parameter p6 must have a valid type name". To resolve this issue, you must specify SqlDbType as Structured and set SqlParameter.TypeName to the name of the user-defined type (UDT) in the database.

However, even with these modifications, you may still encounter errors related to invalid syntax.

Custom extension method

A custom extension method of the

ObjectContext class can simplify this process:

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

    context.ExecuteStoreCommand(command, parameters);
}

This extension method allows you to call stored procedures directly with table-valued parameters. Example usage is as follows:

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);

Restrictions

When using this extension method or similar custom methods, please consider the following limitations:

• Stored procedure parameters must be in order and correspond to the parameters passed to the extension method.
• All columns in the UDT must be specified, even if they have default values.

The above is the detailed content of How to Pass Table-Valued Parameters to Stored Procedures in Entity Framework?. For more information, please follow other related articles on the PHP Chinese website!