Diego Vega's blog

Entity Framework news and sporadic epiphanies

Stored procedures with output parameters using SqlQuery in the DbContext API

Stored procedures with output parameters using SqlQuery in the DbContext API

Rate This
  • Comments 19

The DbContext API introduced in Entity Framework 4.1 exposes a few methods that provide pass-through access to execute database queries and commands in native SQL, such as Database.SqlQuery<T>, DbSet<T>.SqlQuery, and also Database.ExecuteSqlCommand.

These methods are important not only because they allow you do execute your own native SQL queries but because they are right now the main way you can access stored procedures in DbContext, especially when using Code First.

Implementation-wise these are just easier to use variations of the existing ObjectContext.ExecuteStoreQuery<T> and ObjectContext.ExecuteStoreCommand that we added in EF 4.0, however there still seems to be some confusion about what these methods can do and in particular about the query syntax they support.

I believe the simplest way to think about how these methods work is this:

  1. A DbCommand from the underlying ADO.NET provider is setup with the query text passed to the method
  2. The DbCommand is executed with the CommandType property set to CommandType.Text
  3. In addition, if the method can return results (e.g. SqlQuery<T>) objects of the type you passed are materialized based on the values returned by the DbDataReader

For a stored procedure that returns the necessary columns to materialize a Person entity, you can use syntax like this:

 1: var idParam = new SqlParameter { 
 2:     ParameterName = "id",  
 3:     Value = 1}; 
 4: var person = context.Database.SqlQuery<Person>( 
 5:     "GetPerson @id",  
 6:     idParam);

For convenience these methods also allow parameters of regular primitive types to be passed directly. You can use syntax like “{0}” for referring to these parameters in the query string:

 1: var person = context.Database.SqlQuery<Person>(
 2:     "SELECT * FROM dbo.People WHERE Id = {0}", id);

However this syntax has limited applicability and any time you need to do something that requires finer control, like invoking a stored procedure with output parameters or with parameters that are not of primitive types, you will have to use the full SQL syntax of the data source.

I want to share a simple example of using an output parameter so that this can be better illustrated.

Given a (completely useless Smile) stored procedure defined like this in your SQL Server database:

 1: CREATE PROCEDURE [dbo].[GetPersonAndVoteCount] 
 2: ( 
 3:   @id int, 
 4:   @voteCount int OUTPUT 
 5: ) 
 6: AS 
 7: BEGIN 
 8:   SELECT @voteCount = COUNT(*) 
 9:   FROM dbo.Votes 
 10:   WHERE PersonId = @id; 
 11:   SELECT * 
 12:   FROM dbo.People 
 13:   WHERE Id = @id; 
 14: END

You can write code like this to invoke it:

 1: var idParam = new SqlParameter {
 2:      ParameterName = "id",
 3:      Value = 1};
 4: var votesParam = new SqlParameter {
 5:      ParameterName = "voteCount",
 6:      Value = 0,
 7:      Direction = ParameterDirection.Output };
 8: var results = context.Database.SqlQuery<Person>(
 9:     "GetPersonAndVoteCount @id, @voteCount out",
 10:      idParam,
 11:      votesParam);
 12: var person = results.Single();
 13: var votes = (int)votesParam.Value;

There are few things to notice in this code:

  1. The primary syntax that SqlQuery and ExecuteSqlCommand methods support is the native SQL syntax supported by the underlying ADO.NET provider Note: someone mentioned in the comments that SQL Server 2005 won't accept this exact syntax without the keyword EXEC before the stored procedure name.
  2. The DbCommand is executed with CommmandType.Text (as opposed to CommandType.StoredProcedure), which means there is no automatic binding for stored procedure parameters, however you can still invoke stored procedures using regular SQL syntax 
  3. You have to use the correct syntax for passing an output parameter to the stored procedure, i.e. you need to add the “out” keyword after the parameter name in the query string
  4. This only works when using actual DbParameters (in this case SqlParameters because we are using SQL Server), and not with primitive parameters which SqlQuery and ExecuteSqlCommand also support
  5. You will need to read the whole results before you can access the values of output parameters (in this case we achieve this with the Single method) but this is just how stored procedures work and not specific to this EF feature

Once you have learned that you can use provider specific parameters and the native SQL syntax of the underlying data source, you should be able to get most of the same flexibility you can get using ADO.NET but with the convenience of re-using the same database connection EF maintains and the ability to materialize objects directly from query results.

Hope this helps,
Diego

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Any idea whether this would work with an Oracle 11g database (in particular, a SP returning a refcursor), or is this purely MSSQL at the moment?

  • Jimi, I have just sent an answer to the question you posted in StackOverflow at stackoverflow.com/.../8906234. I am not familiar with how this part of the Oracle provider works but if ref curors can be bound as DbParameters I think it should be possible to make them work with this SqlQuery and ExecuteSqlCommand. I have also forwarded your question to some folks hoping they will be able to help you.

  • Does EF 5 SqlQuery materialize related entities or is it not supported yet ?!!

    Thanks.

  • @Nadeem: If you are refering to doing eager loading with SqlQuery, we don't have plans to support that. You can use lazy loading with entities materialized using SqlQuery though.

  • I am using this against a sql2008 server and it's working great for me.  I need to get the application to work against a sql2000 server but when I try it I get the exception "Incorrect syntax near 'stored procedure name here'."  If I build the query string myself without using parameters it goes through fine like below:

    var spResults = context.Database.SqlQuery<GoldmineCustomerInfo>("spweb20_GetCustomerInfoFromGoldMine @custCode = 'custcodevalue').ToList();

    Do I have to tell EF this is a sql2000 server in my configuration somewhere?

  • Found the solution here:

    stackoverflow.com/.../what-causes-incorrect-syntax-near-stored-procedure-name-in-ef-code-first-and

  • Crob, thanks for sharing this detail. I didn't test this with different versions of SQL Server so I didn't hit a problem with the missing EXEC. I will add a note to the post.

  • Diego,

    I had try to search on your blog but not found can you please give me idea if you have "How to Call SQL UDF in Entity Query?"

    social.msdn.microsoft.com/.../6f79d69f-b210-4ba8-a7f4-31aabe5f7d2e

    Thanks

  • I need to call Sql UDF in Entity Query. can you please give me example for this.

  • Hi, thanks for post. I have a question. Do I need define a "Person" class to make "context.Database.SqlQuery<Person>..." work? I think I need a class, only I want to be sure.

  • I'm using EF 5 and did same thing as you suggested except I don't have Single() method so I used iteration, but the output parameter value remains as null, though the SP did set the value.

  • @YL: it is hard to tell without more information. The out parameter should be populated as soon as the enumerator is disposed (which a for loop will do automatically).

  • Using EF5, I accomplished use of SqlQuery with Oracle 11g by using a colon ':' prefixing the parameter names.  Enclose the stored proc name inside BEGIN/END, like this:

      BEGIN storedprocname(:param1, :param2, param_n); END;

    For more detail see this post: stackoverflow.com/.../how-to-call-a-stored-procedure-inside-an-oracle-package-with-entity-framework

  • In your example:

    var person = context.Database.SqlQuery<Person>("GetPerson @id",   idParam);

    you already have a defined class called Person.

    What do you do when your Stored Procedure creates a Pivot table where there is no defined class because you don't know what columns are going to be returned.

  • Hi Diego

    I have a generic routine to execute the stored proc and it s working fine but my concern is does it  require to call  Dispose method to close the DB connection.I am using EF runtime version 4.0. Please help

    public IEnumerable<TDomain> ExecuteQuery<TDomain>(string sqlQuery, params object[] parameters)

               where TDomain : BaseDomain

           {

               var commandTimeoutAppSetting = ConfigurationSettings.AppSettings["CommandTimeout"];

               this._dbContext.SetCommandTimeout(commandTimeoutAppSetting == null ? 30 : System.Convert.ToInt32(commandTimeoutAppSetting));

               var entities = this._dbContext.Database.SqlQuery<TDomain>(sqlQuery, parameters);

               return entities;

           }

Page 1 of 2 (19 items) 12