How can I use Entity Framework to provide parameters to a stored procedure?

entity-framework parameters stored-procedures


I want to know how to send parameters to stored procedure from entity framework? Thanks in advance.

11/30/2011 7:48:40 PM

Accepted Answer

First question is: for which version of the Entity Framework?? .NET 3.5? .NET 4 ?? Things have significantly changed (and improved!) in .NET 4.

And secondly: what do you want to do:

  • retrieve rows from the database

  • execute a stored proc without return value

  • map INSERT/UPDATE/DELETE operations on an entity to a stored proc??

These are three pretty different scenarios - so we need to know what you're going for.

Also: just search with Google (or Bing) - there are plenty of blog post and tutorials out there showing you how to do it - a quick list:

and literally thousands more ......

Update: ok, so you want to retrieve data from the database. In that case, your steps are:

  • go to your EF model (*.edmx file) in the designer
  • right-click and select Update Model from Database
  • pick the stored procedure you want to use and go through the wizard

enter image description here

This creates an entry for the stored procedure in your physical storage model. Next:

  • go to the Model Browser (see the above context menu? It's just below Update Model from Database), navigate to the Storage Model and find your procedure
  • right-click on that procedure

enter image description here

  • Select Add Function Import which imports the "function" (stored procedure) from the physical storage model into the conceptual model (your entity context class, basically).

enter image description here

Here, you have four choices:

  • your stored proc might not return anything (like in my sample) - then it's just a method on your context class that you can call that does something
  • your stored proc might return a collection of scalars, e.g. a list of INT values or something - pick the appropriate value in the dropdown
  • your stored proc might return entities from your model, e.g. complete Customer entities - in that case, select the last option and pick the entity you want to map to (your stored proc must return all columns for that entity, in this case)


  • your stored proc returns something - but neither just scalars (not just INT), nor an entity - in that case, you can pick the third option and define a new complex type (a class) that will hold your results returned from the stored procedure.

Whichever you do - basically EF will create a method on your object context class that you can call. Any parameters your stored proc requires will be parameters of that method so you can very easily pass in e.g. strings, ints etc.

12/1/2011 5:56:53 AM

Popular Answer

Another scenario is needing to call a stored procedure with multiple OUTPUT parameters. Below is a complete sample.

public void MyStoredProc(int inputValue, out decimal outputValue1, out decimal outputValue2)
    var parameters = new[] { 
        new SqlParameter("@0", inputValue), 
        new SqlParameter("@1", SqlDbType.Decimal) { Direction = ParameterDirection.Output }, 
        new SqlParameter("@2", SqlDbType.Decimal) { Direction = ParameterDirection.Output } 

    context.ExecuteStoreCommand("exec MyStoredProc @InParamName=@0, @OutParamName1=@1 output, @OutParamName2=@2 output", parameters);

    outputValue1 = (decimal)parameters[1].Value;
    outputValue2 = (decimal)parameters[2].Value;

Please note the Types used (decimal.) If another type is needed, remember to not only change it in the method argument list but also the SqlDbType.XXX.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow