.NET - EDMX/Auto-generated DbContext skips table-type parameters during procedure import-CodePudding

I have the following SQL procedure and type:

CREATE TYPE oprSlidingScaleProtocolEntrySet AS TABLE (
    LowerValue DECIMAL(4,2) NOT NULL,
    HigherValue DECIMAL(4,2),
    UnitToDeliver DECIMAL(18,4) NOT NULL);
GO

CREATE PROCEDURE oprUpdateSlidingScaleProtocol
    @PrescriptionId INT,
    @newEntries oprSlidingScaleProtocolEntrySet READONLY
-- ...

Using Entity Framework's EDMX, I should be able to import the procedure as a C# function, parameters included. But here's what the EDMX come up with:

public virtual int oprUpdateSlidingScaleProtocol(Nullable<int> prescriptionId)

newEntries is missing, maybe importing data-type parameters is outside EF's capabilities? I've tried using the SqlCommand class instead, but I get the following exception at runtime:

DataTable data = new DataTable("oprSlidingScaleProtocolEntrySet");
data.Columns.Add("LowerValue");
data.Columns.Add("HigherValue");
data.Columns.Add("UnitToDeliver");
// (populate table)
var sqlCmd = new SqlCommand("oprUpdateSlidingScaleProtocol", (SqlConnection)db.Database.Connection) {
    CommandType = CommandType.StoredProcedure
};
sqlCmd.Parameters.AddWithValue("@prescriptionId", prescriptionId);
SqlParameter paramNewEntries = sqlCmd.Parameters.AddWithValue("@newEntries", data);
paramNewEntries.SqlDbType = SqlDbType.Structured;

sqlCmd.ExecuteNonQuery(); // RUNTIME ERROR: System.InvalidOperationException: 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.'

That's weird, but I didn't wanted to mess with cranky connections so I've moved on and written my own procedure import using the same method used inside an auto-generated DbContext:

((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectContext.ExecuteFunction(
    "oprUpdateSlidingScaleProtocol",
    new ObjectParameter("prescriptionId", prescriptionId),
    new ObjectParameter("newEntries", data)
);

But I get the following exception:

System.InvalidOperationException: 'The DbType 'Object' is not valid for the EntityParameter.DbType property on the 'newEntries' object.'

The only objects in my code is those used to define "data":

foreach (SlidingScaleProtocol.Entry entry in formData.GetEntries()) {
    var row = data.NewRow();
    row.ItemArray = new object[] {
        entry.LowerValue,
        entry.HigherValue,
        entry.UnitToDeliver
    };
}

But I need these objects to create my rows!

Documentation on that subject is surprisingly limited, so I'm not sure what else I should try. Any solutions or ideas?

CodePudding user response：

You must open the connection before running SqlCommands.

db.Database.Connection.Open();
var sqlCmd = new SqlCommand("oprUpdateSlidingScaleProtocol", (SqlConnection)db.Database.Connection) {
    CommandType = CommandType.StoredProcedure
};

EF will close the connection for you when the DbContext is Disposed.