Entity Framework Code First and Stored Procedures

There are a number of reasons why you might need to call stored procedures when working with the Entity Framework. It might be that you are working with a legacy database where all access has been locked down to stored procedures, or you might have to resort to procedures for particularly complex problems where the generated SQL from the Entity Framework is just not up to the task. You could resort to plain ADO.NET for these occasions, but you lose the ability to work with data in a strongly typed way unless you want to add a lot of plumbing code to hydrate business objects from DataReaders.

Selecting entities

The data that you select will sometimes map directly to the entities that form part of your domain model. You can use the DbSet.SqlQuery method to execute the stored procedure, which will result in the retrieved entities being tracked by the context (unless you turn tracking off). The following method uses a stored procedure in the Northwind database to retrieve an Employee instance:

using (var context = new NorthWindContext())
{
    SqlParameter param1 = new SqlParameter("@EmployeeID", 6); 
    var employee = await context.Employees.SqlQuery("GetEmployee @EmployeeID", param1).SingleAsync();
}

The stored procedure includes a named parameter which is created and passed as the second argument to the SqlQuery method. Some existing documentation suggests that you can simply pass the value of the parameter as the second argument, but doing so will result in a SqlException telling you that you must declare the scalar variable xxx. Notice the use of SingleAsync (or just Single) if you aren't working asynchronously. This forces execution of the query, which results in the employee variable representing an instance of the Employee type. The following code (without the Single method being used) will result in  a compiler error:

Employee employee = context.Employees.SqlQuery("GetEmployee @EmployeeID", param1);

The error message will be along the lines of "Cannot implicitly convert type 'System.Data.Entity.Infrastructure.DbSqlQuery<EFStoredProcedures.Models.Employee>' to 'EFStoredProcedures.Models.Employee'". Just like a LINQ query, until you force execution of the SqlQuery method through the use of First(), Single(), ToList() etc, the right hand side of the expression represents an instance of the actual query object, not the result of executing it.

The Single method will work with stored procedures that return a single result. It will result in an error if more than one record is returned. If your stored procedure returns multiple records, you use one of the extension methods that caters of collections: ToList(), ToArray() etc:

List<Employee> employees = await context.Employees.SqlQuery("GetAllEmployees").ToListAsync();

Selecting non-entity types

Sometimes the stored procedures will return data that doesn't map to existing entities. It might return only a subset of the data required for an entity or it might return a scalar value. In these cases there is no DbSet to call the SqlQuery method on, so you call the version of the SqlQuery method found on the Database property of the DbContext class instead, which is a generic method. The next example illustrates how to call a stored procedure that returns a scalar value. Here's the procedure:

CREATE procedure [dbo].[CountOfOrders]
    @ProductId int
AS
SELECT Count(*) From Orders o 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
WHERE od.ProductID = @ProductId

And here's how it is called in code:

SqlParameter param1 = new SqlParameter("@ProductID", 72); 
var totalOrders = await context.Database.SqlQuery<int>("CountOfOrders @ProductID", param1).SingleAsync();

The Database.SqlQuery method takes a type argument representing how the result of the stored procedure should be mapped to .NET code. The CountOfOrders procedure returns a numeric, which is mapped to a C# int. If you want to return complex types, you need to create a class to represent them. The following procedure returns data with the following fields: FirstName (string), LastName (string) and TotalOrders (numeric):

CREATE procedure [dbo].[AllSalesPeople] 
AS
SELECT  FirstName, LastName, SUM(os.Subtotal) AS TotalSales
FROM Employees e INNER JOIN
    (Orders o INNER JOIN OrderSubtotals os ON o.OrderID = os.OrderID) 
    ON e.EmployeeID = o.EmployeeID 
    GROUP BY e.LastName, e.FirstName 
    ORDER BY TotalSales DESC

This needs to be mapped to a class:

public class SalesPerson
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public decimal TotalSales { get; set; }
}

You call this procedure by passing SalePerson as the type parameter to the SqlQuery method:

var salesPeople = await context.Database.SqlQuery<SalesPerson>("AllSalesPeople").ToListAsync();

Executing non-query procedures

The Database class has another method for executing procedures that don't return results (INSERT, UPDATE, DELETE), the ExecuteSqlCommand method and its Async twin. The following example demonstrates its use in executing a procedure that adds a new category to the database:

using(var context = new NorthWindContext())
{
    SqlParameter category = new SqlParameter("@CategoryName", "Test");
    await context.Database.ExecuteSqlCommandAsync("NewCategory @CategoryName", category);
}

Parameters are buit in the same way as with the procedures that return results. While the example above doesn't show it, the ExecuteSqlCommand and ExecuteSqlCommandAsync methods return an int representing the number of rows of data affected by the execution of the procedure/query.

Generating stored procedures

Entity Framework can be used to generate simple non-query procedures for entities if you prefer to use those rather than the DbSet's Add and Remove methods. You enable this feature as part of the migration configuration by using the MapToStoredProcedures method. You can do this in the onModelCreating method:

modelBuilder.Entity<Territory>().MapToStoredProcedures();

Or you might prefer to keep your configuration in classes that derive from EntityTypeConfiguration<TEntity>:

public class TerritoryMap : EntityTypeConfiguration<Territory>
{
    public TerritoryMap()
    {
        MapToStoredProcedures();
    }
}

Either way, when you add a new migration, the Up method will include code to generate Create, Update and Delete procedures using the pattern EntityName_Action as a naming convention:

public override void Up()
{
    CreateStoredProcedure(
        "dbo.Territory_Insert",
        p => new
            {
                TerritoryID = p.String(maxLength: 20),
                TerritoryDescription = p.String(maxLength: 50),
                RegionID = p.Int(),
            },
        body:
            @"INSERT [dbo].[Territories]([TerritoryID], [TerritoryDescription], [RegionID])
                VALUES (@TerritoryID, @TerritoryDescription, @RegionID)"
    );
            
    CreateStoredProcedure(
        "dbo.Territory_Update",
        p => new
            {
                TerritoryID = p.String(maxLength: 20),
                TerritoryDescription = p.String(maxLength: 50),
                RegionID = p.Int(),
            },
        body:
            @"UPDATE [dbo].[Territories]
                SET [TerritoryDescription] = @TerritoryDescription, [RegionID] = @RegionID
                WHERE ([TerritoryID] = @TerritoryID)"
    );
            
    CreateStoredProcedure(
        "dbo.Territory_Delete",
        p => new
            {
                TerritoryID = p.String(maxLength: 20),
            },
        body:
            @"DELETE [dbo].[Territories]
                WHERE ([TerritoryID] = @TerritoryID)"
    );
            
}

You are able to modify the code in the Up method to change the names of the stored procedures if you wish.

Entity Framework Core Support

Entity Framework Core (previously known as Entity Framework 7) is a new version of EF designed for use with the new ASP.NET Core framework, which is intended for cross-platform development. The RTM version of EF Core should be available very soon (end June 2016) but will only include support for calling stored procedures that return entities. These will be invoked by calling a new DbSet method - FromSql e.g

using (var context = new NorthWindContext())
{
    var products = await context.Products.FromSql("TenMostExpensiveProducts").ToListAsync();
}

Summary

When I was looking for information on supporting stored procedures with Entity Framework, most of the articles I found related to Database First and edmx files, or they only covered one aspect of stored procedure support, mostly selecting entities. I put this article together to cover all of the most common scenarios related to using stored procedures with EF 6 Code First and migrations. As a result, this article has explored mapping results to entities and non-entities, returning collections, single results and scalar values. It has also shown how to execute procedures that don't return data, and how to use migrations to generate simple CUD procedures for entities. Finally, it briefly looked at the current level of support offered in the new version of Entity Framework.