We had a requirement to call a stored procedure in order to insert some data into a table as part of the products setup. My first impressions were great, this should be easy, I know Entity Framework supports stored procedures!
I was expecting to update my model, select the new stored procedure and Entity Framework would import it and create me a nice strongly typed method like so:
Stored procedure name: CreateProductDefaultsForSetup
Expected Entity Framework result: _context.CreateProductDefaultsForSetup(productId);
After importing the stored procedure I searched the generated file for the procedure name with no luck. I searched online and found loads of posts explaining how it can be achieved when you want to map it back to an entity, which I did not!
Eventually I found out that I needed to write some code that looks like this:
public void Execute(string storedProcedureName, params KeyValuePair<string, object>[] arguments)
{
using (EntityConnection connection = (EntityConnection)_context.Connection)
{
using (EntityCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
foreach (KeyValuePair<string, object> argument in arguments)
{
command.Parameters.AddWithValue(argument.Key, argument.Value);
}
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
I could then call the method like so:
Execute("Entities.CreateProductDefaultsForSetup", new KeyValuePair<string, object>("ProductID", productId));
Not quite as clean as I wanted! Note that the command text is not just the name of the stored procedure.
After all this, it still didn’t work, giving me an error of:
“The FunctionImport CreateProductDefaultsForSetup could not be found in the container”
This took a while to fix with no documentation found online. You need to right-click some whitespace in the Entity Framework designer and select Add > Function Import from the context menu, then follow the simple online prompt.
Finally I had my stored procedure being executed from Entity Framework, shame it wasn’t as straightforward as I have thought!