Database Reference
In-Depth Information
Chapter 10
Stored Procedures
Stored procedures are fixtures in the life of just about anyone who uses modern relational database systems such
as Microsoft's SQL Server. A
stored procedure
is a bit of code that lives on the database server and often acts as an
abstraction layer isolating the code consuming the data from many of the details of the physical organization of the
data. Stored procedures can increase performance by moving data-intensive computations closer to the data, and
they can act as a data-side repository for business and security logic. The bottom line is that if you use data, you will
consume it at some point through a stored procedure.
In this chapter, we explore a number of recipes specifically focused on using stored procedures with Entity
Framework. We used stored procedures in other recipes throughout this topic, but usually they were in the context
of implementing Insert, Update, and Delete actions. In this chapter, we'll show you several ways to consume the data
exposed by stored procedures.
10-1. Returning an Entity Collection with Code Second
Problem
You want to get an entity collection from a stored procedure using a code-second approach.
Solution
Code second
refers to the practice of applying Code-First techniques to model an existing database schema.
Let's say that you have a POCO model like the one shown in Listing 10-1.
Listing 10-1.
The Customer POCO Model
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Company { get; set; }
public string ContactTitle { get; set; }
}
We've set up our DbContext subclass and have configured our Customer entities in Listing 10-2.
