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