Practical .NET

Capturing Results from Stored Procedures with Entity Framework 6

Entity Framework 6 gives you a variety of ways to call stored procedures that return data and capture the results those procedures return. Here's a look at all of them.

• By Peter Vogel
• 05/30/2014

In an earlier column, I showed how to call a stored procedure from Entity Framework without having to mess with connection and parameter objects (though you could mess with connection and parameter objects if you wanted to). However, that column only showed how to call an update query where the result returned from the stored procedure is the number of rows affected. This column shows what to do if your stored procedure is returning some other result (and it's a result you want to capture). As a bonus, the following tools aren't limited to stored procedures -- they'll work with any SQL Select statement.

Assuming that your stored procedure is just returning an integer result (for example, a success or failure code), the simplest tool to use is the ExecuteSQLCommand method, called from your DbContext Database property. The ExecuteSQLCommand accepts, as its first parameter, a string containing placeholders. In subsequent parameters you provide the values for those parameters.

This example calls a stored procedure that requires two parameters:

Dim soe As New SalesOrdersEntities()
If soe.Database.ExecuteSqlCommand("GetCustomersInDivision {0}, {1}", 123, "West") = -1 Then
  ' ...handle failure...
End If

If you're using SQL Server 2005, you might need to write the first parameter to the method as exec GetCustomersInDivision.

(By the way: Don't panic if this code looks like it's opening you up to SQL injection attacks by passing raw strings as parameters. Under the hood, Entity Framework wraps each of your values in a Parameter object to protect you from hackers.)

If you want to take some control over your parameters (to specify one of the parameters can be updated by the stored procedure, for instance), you can explicitly wrap your values in Parameter objects. This example specifies that the first parameter is an InputOutput parameter that can be updated by the stored procedure:

If soe.Database.ExecuteSqlCommand("GetCustomersInDivision",
  New SqlParameter("Category",123).Direction = System.Data.ParameterDirection.InputOutput,
  New SqlParameter("Division","West")) = -1 Then
  ' ...handle failure...
End If

Catching Non-Integer Values
If your stored procedure is returning some value other than an integer, you need a different tool. This trivial procedure returns a string, for instance:

CREATE PROCEDURE [dbo].[ReturnNonIntegerValue] AS
Select "OK"

The right tool to use here is the SqlQuery method (also available from the Database property of the DbContext object). With the SqlQuery method, you must specify the stored procedure's return type, in addition to providing the name of the stored procedure to execute and any required parameters. The result is a collection you can process using LINQ.

This example calls the previous stored procedure and captures the result returned by the procedure. It then pulls the first (and only) value from the result:

Dim res As String
res = soe.Database.SqlQuery(Of String)("ReturnNonIntegerValue").Single

You can pass parameters to the SqlQuery method using either placeholders or Parameter objects. Either of these examples, for instance, will work with a stored procedure that requires a single parameter:

res = soe.Database.SqlQuery(of String)(
  "ReturnNonIntegerValueWithParameter {0}",
  "Value1").Single

res = soe.Database.SqlQuery(of String)(
  "ReturnNonIntegerValueWithParameter",
  New SqlParameter("Parm1", "Value1")).Single

Catching Multiple Results
SqlQuery will also handle stored procedures that return more than just a single value, or that return multiple rows. All you need to do is specify a class that has a property for each value you want to capture (this class doesn't have to be known to your DbContext object).

For example, I have a stored procedure with a Select statement that returns one or more rows that have columns called FirstName, LastName, and City. I'm only interested in the FirstName and LastName columns, so I create a class like this to capture the columns I want:

Public Class SimpleCustomer
  Public Property FirstName As String
  Public Property LastName As String
End Class

Either of the following examples will give me a collection of SimpleCustomer objects corresponding to the rows returned by the stored procedure:

Dim custs = soe.Database.SqlQuery(of SimpleCustomer)(
  "GetCustomersByDivision {0}, {1}",
  123, "West")

Dim custs = soe.Database.SqlQuery(of SimpleCustomer)(
  "GetCustomersByDivision",
  New SqlParameter("Category", 123),
  New SqlParameter("Division", "West"))

One warning: Changes to objects returned by SqlQuery are not tracked by Entity Framework. Changes you make to these objects will not flow back to your database when you call the DbContext object SaveChanges method. That probably doesn't matter because, presumably, there are only two scenarios where you wouldn't use the DbContext entity collections to retrieve these objects. One scenario is that the stored procedure that's returning your rows is returning some result produced by complex processing in your stored procedure and whose results can't be updated anyway. Alternatively, you do want to do updates based on changes to those objects, but you have some related stored procedures to handle those updates. Good news! Now you know how to call those related stored procedures.