Stored procedures with output parameters using SqlQuery in the DbContext API

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

Comments

• Anonymous
  January 17, 2012
  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?

• Anonymous
  January 17, 2012
  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.

• Anonymous
  May 22, 2012
  Does EF 5 SqlQuery materialize related entities or is it not supported yet ?!! Thanks.

• Anonymous
  May 22, 2012
  @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.

• Anonymous
  September 11, 2012
  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?

• Anonymous
  September 11, 2012
  Found the solution here: stackoverflow.com/.../what-causes-incorrect-syntax-near-stored-procedure-name-in-ef-code-first-and

• Anonymous
  September 11, 2012
  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.

• Anonymous
  September 12, 2012
  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

• Anonymous
  September 12, 2012
  I need to call Sql UDF in Entity Query. can you please give me example for this.

• Anonymous
  May 31, 2013
  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.

• Anonymous
  November 22, 2013
  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.

• Anonymous
  November 24, 2013
  @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).

• Anonymous
  April 29, 2014
  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

• Anonymous
  July 28, 2014
  In your example: var person = context.Database.SqlQuery<Person>("GetPerson @id&quot;,   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.

• Anonymous
  September 14, 2014
  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;        }

• Anonymous
  February 02, 2015
  Thanks

• Anonymous
  February 02, 2015
  @NilanjanS very sorry I did not see your question before. You don't need to do anything special because SqlQuery<T>() will play with the default connection management of EF.

• Anonymous
  April 06, 2015
  The comment has been removed

• Anonymous
  April 06, 2015
  @YL, also try ExecuteSqlCommand instead of SqlQuery method.