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 book, 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.
Listing 10-2. The DbContext Subclass for Customer Entities
public class EF6RecipesContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public EF6RecipesContext() : base("name=EF6CodeFirstRecipesContext")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Types<Customer>()
.Configure(c =>
{
c.HasKey(cust => cust.CustomerId);
c.Property(cust => cust.CustomerId) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
c.Property(cust => cust.Name)
.HasMaxLength(50);
c.Property(cust => cust.Company)
.HasMaxLength(50);
c.Property(cust => cust.ContactTitle)
.HasMaxLength(50);
c.ToTable("Customer", "Chapter10");
});
}
}
In the database, we have defined the stored procedure in Listing 10-3, which returns customers for given a company name and customer title.
Listing 10-3. GetCustomers Returns All of the Customers with the Given Title in the Given Company.
create procedure Chapter10.GetCustomers
(@Company varchar(50),@ContactTitle varchar(50))
as
begin
select * from
chapter10.Customer where
(@Company is null or Company = @Company) and
(@ContactTitle is null or ContactTitle = @ContactTitle)
End
To use the GetCustomers stored procedure in the model, do the following.
Listing 10-4. A New Method to Return a Collection of Customer Objects
public ICollection<Customer> GetCustomers(string company, string contactTitle)
{
throw new NotImplementedException();
}
Listing 10-5. DbContext Subclass with GetCustomers() Implementation
public class EF6RecipesContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
public EF6RecipesContext() : base("name=EF6CodeFirstRecipesContext")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Types<Customer>()
.Configure(c =>
{
c.HasKey(cust => cust.CustomerId);
c.Property(cust => cust.CustomerId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
c.Property(cust => cust.Name)
.HasMaxLength(50);
c.Property(cust => cust.Company)
.HasMaxLength(50);
c.Property(cust => cust.ContactTitle)
.HasMaxLength(50);
c.ToTable("Customer", "Chapter10");
});
}
public ICollection<Customer> GetCustomers(string company, string contactTitle)
{
return Database.SqlQuery<Customer>("EXEC Chapter10.GetCustomers @Company, @ContactTitle"
, new SqlParameter("Company", company)
, new SqlParameter("ContactTitle", contactTitle))
.ToList();
}
}
Listing 10-6. Querying the Model with the GetCustomers Stored Procedure via the GetCustomers() Method
//Add customers to the database that we will query with our stored procedure.
using (var context = new EF6RecipesContext())
{
var c1 = new Customer {Name = "Robin Steele", Company = "GoShopNow.com",
ContactTitle="CEO"};
var c2 = new Customer {Name = "Orin Torrey", Company = "GoShopNow.com",
ContactTitle="Sales Manager"};
var c3 = new Customer {Name = "Robert Lancaster", Company = "GoShopNow.com",
ContactTitle = "Sales Manager"};
var c4 = new Customer { Name = "Julie Stevens", Company = "GoShopNow.com",
ContactTitle = "Sales Manager" };
context.Customers.Add(c1);
context.Customers.Add(c2);
context.Customers.Add(c3);
context.Customers.Add(c4);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
var allCustomers = context.GetCustomers("GoShopNow.com", "Sales Manager");
Console.WriteLine("Customers that are Sales Managers at GoShopNow.com");
foreach (var c in allCustomers)
{
Console.WriteLine("Customer: {0}", c.Name);
}
}
The following is the output of the code in Listing 10-6:
Customers that are Sales Managers at GoShopNow.com
Customer: Orin Torrey
Customer: Robert Lancaster
Customer: Julie Stevens
How It Works
To retrieve an entity collection from a stored procedure in the database, we implemented a new method in the DbContext subclass called GetCustomers(). Within the method implementation, we call DbContext.Database.SqlQuery<T>() to execute the GetCustomers stored procedure, which we defined in Listing 10-3.
The SqlQuery() method can be used to execute nearly any DML statement that returns a result set. The method takes a string parameter to specify the query to execute, as well as additional SQL parameters to be substituted in the query itself. The SqlQuery<T>() generic method will return a strongly-typed collection of T entities, which allows the developer to avoid enumerating and casting a collection of objects.
10-2. Returning Output Parameters
Problem
You want to retrieve values from one or more output parameters of a stored procedure.
Solution
Let’s say you have a model like the one shown in Figure 10-1.
Figure 10-1. A simple model for vehicle rental
For a given date, you want to know the total number of rentals, the total rental payments made, and the vehicles rented. The stored procedure in Listing 10-7 is one way to get the information you want.
Listing 10-7. A Stored Procedure for the Vehicles Rented, the Number of Rentals, and the Total Rental Payments
create procedure [chapter10].[GetVehiclesWithRentals]
(@date date,
@TotalRentals int output,
@TotalPayments decimal(18,2) output)
as
begin
select @TotalRentals = COUNT(*), @TotalPayments = SUM(payment)
from chapter10.Rental
where RentalDate = @date
select distinct v.*
from chapter10.Vehicle v join chapter10.Rental r
on v.VehicleId = r.VehicleId
end
To use the stored procedure in Listing 10-7 in the model, do the following.
Listing 10-8. Querying the Model Using the GetVehiclesWithRentals Stored Procedure via the GetVehiclesWithRentals() method
using (var context = new EF6RecipesContext())
{
var car1 = new Vehicle { Manufacturer = "Toyota", Model = "Camry",
Year = 2013 };
var car2 = new Vehicle { Manufacturer = "Chevrolet", Model = "Corvette",
Year = 2013 };
var r1 = new Rental { Vehicle = car1,
RentalDate = DateTime.Parse("5/7/2013"),
Payment = 59.95M };
var r2 = new Rental { Vehicle = car2,
RentalDate = DateTime.Parse("5/7/2013"),
Payment = 139.95M };
context.AddToRentals(r1);
context.AddToRentals(r2);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
string reportDate = "5/7/2013";
var totalRentals = new ObjectParameter("TotalRentals", typeof(int));
var totalPayments = new ObjectParameter("TotalPayments", typeof(decimal));
var vehicles = context.GetVehiclesWithRentals(DateTime.Parse(reportDate),
totalRentals, totalPayments);
Console.WriteLine("Rental Activity for {0}",reportDate);
Console.WriteLine("Vehicles Rented");
foreach(var vehicle in vehicles)
{
Console.WriteLine("{0} {1} {2}",vehicle.Year.ToString(),
vehicle.Manufacturer, vehicle.Model);
}
Console.WriteLine("Total Rentals: {0}",
((int)totalRentals.Value).ToString());
Console.WriteLine("Total Payments: {0}",
((decimal)totalPayments.Value).ToString("C"));
}
The following is the output of the code in Listing 10-8:
Rental Activity for 5/7/2013
Vehicles Rented
2013 Toyota Camry
2013 Chevrolet Corvette
Total Rentals: 2
Total Payments: $200.00
How It Works
When we updated the model with the GetVehiclesWithRentals stored procedure, the wizard updated the store model with the stored procedure. By importing the function (in Step 2), we updated the conceptual model. The result is that the stored procedure is exposed as the GetVehiclesWithRentals() method, which has a signature semantically similar to the stored procedure.
There is one important thing to note when calling the GetVehiclesWithRentals() method: the returned entity collection must be materialized before the output parameters will become available. This should not be too surprising to those who have used multiple result sets in ADO.NET. The data reader must be advanced (with the NextResult() method) to the next result set. Similarly, the entire returned entity collection must be accessed or disposed before the output parameters can be accessed.
In our example, it is not enough to materialize the first vehicle for the output parameters to become available. The entire collection must be materialized. This means moving the lines that print the total rentals and total payments to a position after the foreach loop. Alternatively, we could materialize the entire collection with the ToList() method and then iterate through the list. This would allow us to access the output parameters prior to iterating through the collection.
10-3. Returning a Scalar Value Result Set
Problem
You want to use a stored procedure that returns a result set containing a single scalar value.
Solution
Let’s say you have a model like the one shown in Figure 10-2.
Figure 10-2. A model representing ATM machines and withdrawal transactions
You want to use a stored procedure that returns the total amount withdrawn from a given ATM on a given date. The code in Listing 10-9 is one way to implement this stored procedure.
Listing 10-9. The GetWithdrawals Stored Procedure That Returns the Total Amount Withdrawn from a Given ATM on a Given Date
create procedure [Chapter10].[GetWithdrawals]
(@ATMId int, @WithdrawalDate date)
as
begin
select SUM(amount) TotalWithdrawals
from Chapter10.ATMWithdrawal
where ATMId = @ATMId and [date] = @WithdrawalDate
end
To use the stored procedure in Listing 10-9 in the model, do the following:
Listing 10-10. Querying the Model with the GetWithdrawals Stored Procedure via the GetWithdrawals() Method
DateTime today = DateTime.Parse("5/7/2013");
DateTime yesterday = DateTime.Parse("5/6/2013");
using (var context = new EF6RecipesContext())
{
var atm = new ATMMachine { ATMId = 17, Location = "12th and Main" };
atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 20.00M, Date= today});
atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 100.00M, Date = today});
atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 75.00M, Date = yesterday});
atm.ATMWithdrawals.Add(new ATMWithdrawal {Amount = 50.00M, Date= today});
context.ATMMachines.Add(atm);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
var forToday = context.GetWithdrawals(17, today).FirstOrDefault();
var forYesterday = context.GetWithdrawals(17, yesterday).FirstOrDefault();
var atm = context.ATMMachines.Where(o => o.ATMId == 17).FirstOrDefault();
Console.WriteLine("ATM Withdrawals for ATM at {0} at {1}",
atm.ATMId.ToString(), atm.Location);
Console.WriteLine(" {0} Total Withdrawn = {1}",
yesterday.ToShortDateString(), forYesterday.Value.ToString("C"));
Console.WriteLine(" {0} Total Withdrawn = {1}", today.ToShortDateString(),
forToday.Value.ToString("C"));
}
The following is the output from the code in Listing 10-10:
ATM Withdrawals for ATM at 17 at 12th and Main
5/6/2013 Total Withdrawn = $75.00
5/7/2013 Total Withdrawn = $170.00
How It Works
Notice that Entity Framework expects the stored procedure to return a collection of scalar values. In our example, our store procedure returns just one decimal value. We use the FirstOrDefault() method to extract this scalar from the collection.
10-4. Returning a Complex Type from a Stored Procedure
Problem
You want to use a stored procedure that returns a complex type in the model.
Solution
Let’s say that you have a model with an Employee entity. Employee contains the employee’s ID, name, and a complex address type that holds the address, city, state, and ZIP code for the employee. The name of the complex type is EmployeeAddress. The property in the Employee entity is simply Address. The Employee entity is shown in Figure 10-3.
Figure 10-3. An Employee entity with an Address property of type EmployeeAddress, which is a complex type
You want to use a stored procedure to return a collection of instances of the EmployeeAddress complex type. The stored procedure that returns the addresses might look like the one shown in Listing 10-11.
Listing 10-11. A Stored Procedure to Return the Addresses for Employees in a Given City
create procedure [Chapter10].[GetEmployeeAddresses]
(@city varchar(50))
as
begin
select [address], city, [state], ZIP
from Chapter10.Employee where city = @city
end
To use the stored procedure in Listing 10-11 in the model, do the following.
Listing 10-12. Querying the Model Using the GetEmployeeAddresses Stored Procedure via the GetEmployeeAddresses() Method
using (var context = new EF6RecipesContext())
{
var emp1 = new Employee { Name = "Lisa Jefferies",
Address = new EmployeeAddress {
Address = "100 E. Main",
City = "Fort Worth", State = "TX",
ZIP = "76106" } };
var emp2 = new Employee { Name = "Robert Jones",
Address = new EmployeeAddress {
Address = "3920 South Beach",
City = "Fort Worth", State = "TX",
ZIP = "76102" } };
var emp3 = new Employee { Name = "Steven Chue",
Address = new EmployeeAddress {
Address = "129 Barker",
City = "Euless", State = "TX",
ZIP = "76092" } };
var emp4 = new Employee { Name = "Karen Stevens",
Address = new EmployeeAddress {
Address = "108 W. Parker",
City = "Fort Worth", State = "TX",
ZIP = "76102" } };
context.Employees.Add(emp1);
context.Employees.Add(emp2);
context.Employees.Add(emp3);
context.Employees.Add(emp4);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
Console.WriteLine("Employee addresses in Fort Worth, TX");
foreach (var address in context.GetEmployeeAddresses("Fort Worth"))
{
Console.WriteLine("{0}, {1}, {2}, {3}", address.Address,
address.City, address.State, address.ZIP);
}
}
The following is the output of the code in Listing 10-12:
Employee addresses in Fort Worth, TX
100 E. Main, Fort Worth, TX, 76106
3920 South Beach, Fort Worth, TX, 76102
108 W. Parker, Fort Worth, TX, 76102
How It Works
Complex types offer a convenient way to refactor repeated groups of properties into a single type that can be reused across many entities. In this recipe, we created a stored procedure that returned the address information for employees in a given city. In the model, we mapped these returned columns to the fields of the EmployeeAddress complex type. The GetEmployeeAdresses() method is defined by the Function Import Wizard to return a collection of instances of the EmployeeAddress type.
Complex types are often used to hold arbitrarily shaped data returned from a stored procedure. The data is not required to map to any entity in the model. Because complex types are not tracked by the object context, they are both a lightweight and efficient alternative to handling shaped data in the model.
10-5. Defining a Custom Function in the Storage Model
Problem
You want to define a custom function inside the model rather than a stored procedure in the database.
Solution
Let’s say that you have a database that keeps track of members and the messages they have sent. Figure 10-4 shows one representation of this database.
Figure 10-4. A simple database of members and their messages
It may be the case that, as an entry-level programmer, you have not been granted access to the database to create stored procedures. However, being wise and productive, you want to encapsulate the query logic for finding the members with the highest number of messages into a reusable custom function in the storage model procedure. The model looks like the one shown in Figure 10-5.
Figure 10-5. The model for members and their messages
To define the custom function in the storage model, do the following:
Add the code in Listing 10-13 into the <Schema> element. This defines the custom function.
Listing 10-13. The Definition of the Custom Function MembersWithTheMostMessages
<Function Name="MembersWithTheMostMessages" IsComposable="false">
<CommandText>
select m.*
from chapter10.member m
join
(
select msg.MemberId, count(msg.MessageId) as MessageCount
from chapter10.message msg where datesent = @datesent
group by msg.MemberId
) temp on m.MemberId = temp.MemberId
order by temp.MessageCount desc
</CommandText>
<Parameter Name="datesent" Type="datetime" />
</Function>
Listing 10-14. Using the MembersWithTheMostMessages Function via the MembersWithTheMostMessages() method
DateTime today = DateTime.Parse("5/7/2013");
using (var context = new EF6RecipesContext())
{
var mem1 = new Member { Name = "Jill Robertson" };
var mem2 = new Member { Name = "Steven Rhodes" };
mem1.Messages.Add(new Message { DateSent = today,
MessageBody = "Hello Jim",
Subject = "Hello" });
mem1.Messages.Add(new Message { DateSent = today,
MessageBody = "Wonderful weather!",
Subject = "Weather" });
mem1.Messages.Add(new Message { DateSent = today,
MessageBody = "Meet me for lunch",
Subject = "Lunch plans" });
mem2.Messages.Add(new Message { DateSent = today,
MessageBody = "Going to class today?",
Subject = "What's up?" });
context.Members.Add(mem1);
context.Members.Add(mem2);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
Console.WriteLine("Members by message count for {0}",
today.ToShortDateString());
var members = context.MembersWithTheMostMessages(today);
foreach (var member in members)
{
Console.WriteLine("Member: {0}", member.Name);
}
}
Following is the output of the code in Listing 10-14:
Members by message count for 5/7/2013
Member: Jill Robertson
Member: Steven Rhodes
How It Works
A custom function is different from a model-defined function (see Chapter 11) in that a custom function is defined in the storage model. This makes the custom function much more like a traditional stored procedure in a database. Just like a DefiningQuery in the storage model defines a “virtual” table that doesn’t really exist in the database, a custom function in the storage model is like a “virtual” stored procedure. Some in the Entity Framework community refer to custom functions as native functions. The Microsoft documentation uses the term “custom function,” so we’ll go with that.
The code in Listing 10-13 defines our custom function. We put this in the storage model section of the .edmx file by directly editing the file using the XML editor. Note that if you use the Update From Database Wizard to update the model with new objects from your database, the wizard will overwrite this section. So be careful to save out any changes that you’ve made to the storage model before you use the Update From Database Wizard.
Just like with the stored procedures in the previous recipes, we used the Function Import Wizard to map the custom function to a CLR method. This defines the name of the CLR method and the expected return type. In our case, the Custom Function returns a collection of instances of the Member entity.
In Listing 10-14, the code uses the MembersWithTheMostMessages() method to invoke the custom function. This is the same pattern we used with stored procedures.
Custom functions can be helpful in the following scenarios:
10-6. Populating Entities in a Table per Type Inheritance Model
Problem
You want to use a stored procedure to populate entities in a Table per Type inheritance model.
Solution
Let’s say the model looks like the one shown in Figure 10-6. In this model, the entities Magazine and DVD extend the base entity Media. In the underlying database, we have a table for each of these entities. We have modeled these tables using Table per Type inheritance. We want to use a stored procedure to obtain the data for this model from the database.
Figure 10-6. A model using Table per Type inheritance. The model represents some information about magazines and DVDs
Tip Need to brush up on Table per Type modeling and its performance implications? Check out Recipe 2-8 in Chapter 2.
To create and use a stored procedure that returns these entities, do the following.
Listing 10-15. The GetAllMedia Stored Procedure That Returns a Rowset with a Discriminator Column
create procedure [Chapter10].[GetAllMedia]
as
begin
select m.MediaId,c.Title,m.PublicationDate, null PlayTime,'Magazine' MediaType
from chapter10.Media c join chapter10.Magazine m on c.MediaId = m.MediaId
union
select d.MediaId,c.Title,null,d.PlayTime,'DVD'
from chapter10.Media c join chapter10.DVD d on c.MediaId = d.MediaId
end
Listing 10-16. This FunctionImportMapping Conditionally Maps the Returned Rows to Either the Magazine or the DVD Entity.
<FunctionImportMapping FunctionImportName="GetAllMedia"
FunctionName="EF6RecipesModel.Store.GetAllMedia">
<ResultMapping>
<EntityTypeMapping TypeName="EF6RecipesModel.Magazine">
<ScalarProperty ColumnName="PublicationDate" Name="PublicationDate"/>
<Condition ColumnName="MediaType" Value="Magazine"/>
</EntityTypeMapping>
<EntityTypeMapping TypeName="EF6RecipesModel.DVD">
<ScalarProperty ColumnName="PlayTime" Name="PlayTime"/>
<Condition ColumnName="MediaType" Value="DVD"/>
</EntityTypeMapping>
</ResultMapping>
</FunctionImportMapping>
Listing 10-17. Using the GetAllMedia Stored Procedure via the GetAllMedia() Method
Using (var context = new EF6RecipesContext())
{
context.MediaSet.Add(new Magazine { Title = "Field and Stream",
PublicationDate = DateTime.Parse("6/12/1945") });
context.MediaSet.Add(new Magazine { Title = "National Geographic",
PublicationDate = DateTime.Parse("7/15/1976") });
context.MediaSet.Add(new DVD { Title = "Harmony Road",
PlayTime = "2 hours, 30 minutes" });
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
var allMedia = context.GetAllMedia();
Console.WriteLine("All Media");
Console.WriteLine("=========");
foreach (var m in allMedia)
{
if (m is Magazine)
Console.WriteLine("{0} Published: {1}", m.Title,
((Magazine)m).PublicationDate.ToShortDateString());
else if (m is DVD)
Console.WriteLine("{0} Play Time: {1}", m.Title, ((DVD)m).PlayTime);
}
}
The following is the output of the code in Listing 10-17:
All Media
=========
Field and Stream Published: 6/12/1945
National Geographic Published: 7/15/1976
Harmony Road Play Time: 2 hours, 30 minutes
How It Works
The two key parts to the solution are the discriminator column injected into the result set by the stored procedure and the conditional mapping of the results to the Magazine and DVD entities.
Note The discriminator column is a metadata column that specifies the type of object represented by the database record.
The stored procedure in Listing 10-15 forms a union of rows from the Magazine and DVD tables, and it injects the strings Magazine or DVD into the MediaType discriminator column. For each select, we join to the Media table, which is represented in the model by the base entity, to include the Title column. All of the rows from all three tables are now in the result set with each row tagged to indicate the table from where it came.
With each row tagged with either Magazine or DVD, we conditionally map the rows either to the Magazine or DVD entities based on the tag or value in the discriminator column. This is done in the <FunctionImportMapping> section.
In Listing 10-17, we call the CLR method GetAllMedia(), which we mapped to the GetAllMedia stored procedure when we added the Function Import. When we call GetAllMedia(), the entire object graph is materialized with the inheritance hierarchy intact. We iterate through the collection, alternately printing out the Magazine and DVD entities.
10-7. Populating Entities in a Table per Hierarchy Inheritance Model
Problem
You want to use a stored procedure to populate entities in a Table per Hierarchy inheritance model.
Solution
Suppose you have a model like the one shown in Figure 10-7. We have two derived entities: Instructor and Student. Because this model is using Table per Hierarchy inheritance, we have just one table in the database. The Person table has a discriminator column that is used to map the table to the derived entities. You want to populate the entities with a stored procedure.
Figure 10-7. A model for instructors and students
To create and use a stored procedure that returns these entities, do the following:
Listing 10-18. The GetAllPeople Stored Procedure, Which Returns All the People, Both Students and Instructors, in the Model
create procedure [Chapter10].[GetAllPeople]
as
begin
select * from chapter10.Person
end
Listing 10-19. The FunctionImportMapping Conditionally Maps Rows to Either the Instructor or Student Entity
<FunctionImportMapping FunctionImportName="GetAllPeople"
FunctionName="EF6RecipesModel.Store.GetAllPeople">
<ResultMapping>
<EntityTypeMapping TypeName="EFRecipesModel.Student">
<ScalarProperty Name="Degree" ColumnName="Degree" />
<Condition ColumnName="PersonType" Value="Student"/>
</EntityTypeMapping>
<EntityTypeMapping TypeName="EF6RecipesModel.Instructor">
<ScalarProperty Name="Salary" ColumnName="Salary"/>
<Condition ColumnName="PersonType" Value="Instructor"/>
</EntityTypeMapping>
</ResultMapping>
</FunctionImportMapping>
Listing 10-20. Querying the Model Using the GetAllPeople Stored Procedure via the GetAllPeople() Method.
using (var context = new EF6RecipesContext())
{
context.People.Add(new Instructor { Name = "Karen Stanford",
Salary = 62500M });
context.People.Add(new Instructor { Name = "Robert Morris",
Salary = 61800M });
context.People.Add(new Student { Name = "Jill Mathers",
Degree = "Computer Science" });
context.People.Add(new Student { Name = "Steven Kennedy",
Degree = "Math" });
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
Console.WriteLine("Instructors and Students");
var allPeople = context.GetAllPeople();
foreach (var person in allPeople)
{
if (person is Instructor)
Console.WriteLine("Instructor {0} makes {1}/year",
person.Name,
((Instructor)person).Salary.ToString("C"));
else if (person is Student)
Console.WriteLine("Student {0}'s major is {1}",
person.Name, ((Student)person).Degree);
}
}
The following is the output of the code in Listing 10-20:
Instructors and Students
Instructor Karen Stanford makes $62,500.00/year
Instructor Robert Morris makes $61,800.00/year
Student Jill Mathers's major is Computer Science
Student Steven Kennedy's major is Math
How It Works
Using a stored procedure to populate entities in a Table per Hierarchy inheritance model turns out to be a little easier than for Table per Type (see Recipe 10-6). Here the stored procedure just selected all rows in the Person table. The PersonType column contains the discriminator value that we use in <FunctionImportMapping> in Listing 10-19 to map the rows conditionally either to the Student or to the Instructor entity. In Recipe 10-6, the stored procedure had to create the column. In this recipe as well as in Recipe 10-6, the key part is the conditional mapping in the <FunctionImportMapping> tag.
10-8. Mapping the Insert, Update, and Delete Actions to Stored Procedures
Problem
You want to map the Insert, Update, and Delete actions to stored procedures.
Solution
Let’s say you have a model with the Athlete entity shown in Figure 10-8. The underlying database has the Athlete table shown in Figure 10-9. You want to use stored procedures for the Insert, Update, and Delete actions.
Figure 10-8. The Athlete entity in the model
Figure 10-9. The Athlete table with some basic information about athletes
To map stored procedures to the Insert, Update, and Delete actions for the Athlete entity, do the following:
Listing 10-21. The Stored Procedures for the Insert, Update, and Delete Actions
create procedure [chapter10].[InsertAthlete]
(@Name varchar(50), @Height int, @Weight int)
as
begin
insert into Chapter10.Athlete values (@Name, @Height, @Weight)
select SCOPE_IDENTITY() as AthleteId
end
go
create procedure [chapter10].[UpdateAthlete]
(@AthleteId int, @Name varchar(50), @Height int, @Weight int)
as
begin
update Chapter10.Athlete set Name = @Name, Height = @Height, [Weight] = @Weight
where AthleteId = @AthleteId
end
go
create procedure [chapter10].[DeleteAthlete]
(@AthleteId int)
as
begin
delete from Chapter10.Athlete where AthleteId = @AthleteId
end
Figure 10-10. Mapping the stored procedures, parameters, and return values for the Insert, Update, and Delete actions
How It Works
We updated the model with the stored procedures we created in the database. This makes the stored procedures available for use in the model. Once we have the stored procedures available in the model, we mapped them to the Insert, Update, and Delete actions for the entity.
In this recipe, the stored procedures are about as simple as you can get. They take in properties as parameters and perform the action. For the Insert stored procedure, we need to return the stored generated key for the entity. In this recipe, the stored generated key is just an identity column. We need to return this from the stored procedure for the Insert action and map this returned value to the AthleteId property. This is an important step. Without this, Entity Framework would not be able to get the entity key for the instance of the Athlete entity just inserted.
You may ask, “When do I map stored procedures to the actions?” In most cases, Entity Framework will generate efficient code for the Insert, Update, and Delete actions. You may also be wondering, “When would I ever need to replace this with my own stored procedures?” Here are the best-practice answers to this question.
The code in Listing 10-22 demonstrates inserting, deleting, and updating in the model. The code isn’t any different because of the mapping of the actions, and that’s fine. The fact that we have replaced the code that Entity Framework would have dynamically generated with our own stored procedures will not affect the code that uses the entity.
Listing 10-22. Executing the Insert, Update, and Delete Actions
using (var context = new EF6RecipesContext())
{
context.Athletes.Add(new Athlete { Name = "Nancy Steward",
Height = 167, Weight = 53 });
context.Athletes.Add(new Athlete { Name = "Rob Achers",
Height = 170, Weight = 77 });
context.Athletes.Add(new Athlete { Name = "Chuck Sanders",
Height = 171, Weight = 82 });
context.Athletes.Add(new Athlete { Name = "Nancy Rodgers",
Height = 166, Weight = 59 });
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
// do a delete and an update
var all = context.Athletes;
context.Delete(all.First(o => o.Name == "Nancy Steward"));
all.First(o => o.Name == "Rob Achers").Weight = 80;
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
Console.WriteLine("All Athletes");
Console.WriteLine("============");
foreach (var athlete in context.Athletes)
{
Console.WriteLine("{0} weighs {1} Kg and is {2} cm in height",
athlete.Name, athlete.Weight, athlete.Height);
}
}
The following is the output of the code in Listing 10-22:
All Athletes
============
Rob Achers weighs 80 Kg and is 170 cm in height
Chuck Sanders weighs 82 Kg and is 171 cm in height
Nancy Rodgers weighs 59 Kg and is 166 cm in height
10-9. Using Stored Procedures for the Insert and Delete Actions in a Many-to-Many Association
Problem
You want to use stored procedures for the Insert and Delete actions in a payload-free, many-to-many association. These stored procedures affect only the link table in the association and not the associated entities.
Solution
Let’s say that you have a many-to-many relationship between an Author table and a Book table. The link table, AuthorBook, is used as part of the relationship, as shown in Figure 10-11.
Figure 10-11. A payload-free, many-to-many relationship between an Author and a Book
When you import these tables into a model, you get a model that looks like the one shown in Figure 10-12.
Figure 10-12. The model created by importing the tables in Figure 10-11
To use stored procedures for the Insert and Delete actions, do the following.
Listing 10-23. The stored Procedures for the Insert and Delete Actions
create procedure [chapter10].[InsertAuthorBook]
(@AuthorId int,@BookId int)
as
begin
insert into chapter10.AuthorBook(AuthorId,BookId) values (@AuthorId,@BookId)
end
go
create procedure [chapter10].[DeleteAuthorBook]
(@AuthorId int,@BookId int)
as
begin
delete chapter10.AuthorBook where AuthorId = @AuthorId and BookId = @BookId
end
Listing 10-24. Mapping the Stored Procedures to the Insert and Delete Actions for the Many-to-Many Association
<ModificationFunctionMapping>
<InsertFunction FunctionName="EF6RecipesModel.Store.InsertAuthorBook">
<EndProperty Name="Author">
<ScalarProperty Name="AuthorId" ParameterName="AuthorId" />
</EndProperty>
<EndProperty Name="Book">
<ScalarProperty Name="BookId" ParameterName="BookId" />
</EndProperty>
</InsertFunction>
<DeleteFunction FunctionName="EF6RecipesModel.Store.DeleteAuthorBook">
<EndProperty Name="Author">
<ScalarProperty Name="AuthorId" ParameterName="AuthorId" />
</EndProperty>
<EndProperty Name="Book">
<ScalarProperty Name="BookId" ParameterName="BookId" />
</EndProperty>
</DeleteFunction>
</ModificationFunctionMapping>
The code in Listing 10-25 demonstrates inserting into and deleting from the model. As you can see from the SQL Profiler output that follows, our InsertAuthorBook and DeleteAuthorBook stored procedures are called when Entity Framework updates the many-to-many association.
Listing 10-25. Inserting into the Model
using (var context = new EF6RecipesContext())
{
var auth1 = new Author { Name = "Jane Austin"};
var book1 = new Book { Title = "Pride and Prejudice",
ISBN = "1848373104" };
var book2 = new Book { Title = "Sense and Sensibility",
ISBN = "1440469563" };
auth1.Books.Add(book1);
auth1.Books.Add(book2);
var auth2 = new Author { Name = "Audrey Niffenegger" };
var book3 = new Book { Title = "The Time Traveler's Wife",
ISBN = "015602943X" };
auth2.Books.Add(book3);
context.Authors.Add(auth1);
context.Authors.Add(auth2);
context.SaveChanges();
context.Delete(book1);
context.SaveChanges();
}
Here is the output of the SQL Profiler showing the SQL statements that are executed by the code in Listing 10-25:
exec sp_executesql N'insert [Chapter10].[Author]([Name])
values (@0)
select [AuthorId]
from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',
@0='Jane Austin'
exec sp_executesql N'insert [Chapter10].[Author]([Name])
values (@0)
select [AuthorId]
from [Chapter10].[Author]
where @@ROWCOUNT > 0 and [AuthorId] = scope_identity()',N'@0 varchar(50)',
@0='Audrey Niffenegger'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='Pride and Prejudice',@1='1848373104'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='Sense and Sensibility',@1='1440469563'
exec sp_executesql N'insert [Chapter10].[Book]([Title], [ISBN])
values (@0, @1)
select [BookId]
from [Chapter10].[Book]
where @@ROWCOUNT > 0 and [BookId] = scope_identity()',N'@0 varchar(50),
@1 varchar(50)',@0='The Time Traveler''s Wife',@1='015602943X'
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=1
exec [Chapter10].[InsertAuthorBook] @AuthorId=1,@BookId=2
exec [Chapter10].[InsertAuthorBook] @AuthorId=2,@BookId=3
exec [Chapter10].[DeleteAuthorBook] @AuthorId=1,@BookId=1
exec sp_executesql N'delete [Chapter10].[Book]
where ([BookId] = @0)',N'@0 int',@0=7
How It Works
To map the stored procedures to the Insert and Delete actions for the many-to-many association, we created the stored procedures in our database and then updated the model with the stored procedures.
Because Entity Framework’s designer does not currently support mapping stored procedures to the Insert and Delete actions for associations, we need to edit the .edmx file directly. In the Mappings section, we added a <ModificationFunctionMapping> tag that maps the Insert and Delete actions for the association to our stored procedures. In this tag, we refer to the InsertAuthorBook and DeleteAuthorBook stored procedures, which are defined in the Store model because we updated the model with these stored procedures from the database.
In the trace from Listing 10-25, we can see not only the expected inserts for the Author and Book tables, but we can also see that our stored procedures are used to insert and delete the association.
10-10. Mapping the Insert, Update, and Delete Actions to Stored Procedures for Table per Hierarchy Inheritance
Problems
You have a model that uses Table per Hierarchy inheritance, and you want to map the Insert, Update, and Delete actions to stored procedures.
Solution
Let’s say that your database contains a Product table that describes a couple of different kinds of products (see Figure 10-14). You have created a model with derived types for each of the product types represented in the Product table. The model looks like the one shown in Figure 10-14.
Figure 10-13. A Product table with a discriminator column, ProductType, that indicates the type of product described by the row in the table
Figure 10-14. A model using Table per Hierarchy inheritance with a derived type for each of the products
To map stored procedures to the Insert, Update, and Delete actions for this model, do the following:
Listing 10-26. The Stored Procedure We Map to the Insert, Update, and Delete Actions for the Model
create procedure [chapter10].[InsertBook]
(@Title varchar(50), @Publisher varchar(50))
as
begin
insert into Chapter10.Product (Title, Publisher, ProductType) values
(@Title,@Publisher, 'Book')
select SCOPE_IDENTITY() as ProductId
end
go
create procedure [chapter10].[UpdateBook]
(@Title varchar(50), @Publisher varchar(50), @ProductId int)
as
begin
update Chapter10.Product set Title = @Title, Publisher = @Publisher
where ProductId = @ProductId
end
go
create procedure [chapter10].[DeleteBook]
(@ProductId int)
as
begin
delete from Chapter10.Product where ProductId = @ProductId
end
go
create procedure [chapter10].[InsertDVD]
(@Title varchar(50), @Rating varchar(50))
as
begin
insert into Chapter10.Product (Title, Rating, ProductType) values
(@Title, @Rating, 'DVD')
select SCOPE_IDENTITY() as ProductId
end
go
create procedure [chapter10].[DeleteDVD]
(@ProductId int)
as
begin
delete from Chapter10.Product where ProductId = @ProductId
end
go
create procedure [chapter10].[UpdateDVD]
(@Title varchar(50), @Rating varchar(50), @ProductId int)
as
begin
update Chapter10.Product set Title = @Title, Rating = @Rating
where ProductId = @ProductId
end
Figure 10-15. Mapping the stored procedures to the Insert, Update, and Delete actions for the Book entity. Be particularly careful to map the Result Column Binding to the ProductId property for the Insert action
Figure 10-16. Mapping the stored procedures to the Insert, Update, and Delete actions for the DVD entity
How It Works
We created the stored procedures for the Insert, Update, and Delete actions for both the Book and DVD entities and imported them into the model. Once we have these stored procedures in the model, we mapped them to the corresponding actions, being careful to map the Result Column Binding for the Insert action to the ProductId property. This ensures that the store generated key for the Product is mapped to the ProductId property.
The Table per Hierarchy inheritance is supported by the implementation of the Insert stored procedures. Each of them inserts the correct ProductType value. Given these values in the tables, Entity Framework can correctly materialize the derived entities.
The code in Listing 10-27 demonstrates inserting, updating, deleting, and querying the model.
Listing 10-27. Exercising the Insert, Update, and Delete Actions
using (var context = new EF6RecipesContext())
{
var book1 = new Book { Title = "A Day in the Life",
Publisher = "Colorful Press" };
var book2 = new Book { Title = "Spring in October",
Publisher = "AnimalCover Press" };
var dvd1 = new DVD { Title = "Saving Sergeant Pepper", Rating = "G" };
var dvd2 = new DVD { Title = "Around The Block", Rating = "PG-13" };
context.Products.Add(book1);
context.Products.Add(book2);
context.Products.Add(dvd1);
context.Products.Add(dvd2);
context.SaveChanges();
// update a book and delete a dvd
book1.Title = "A Day in the Life of Sergeant Pepper";
context.Delete(dvd2);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
Console.WriteLine("All Products");
Console.WriteLine("============");
foreach (var product in context.Products)
{
if (product is Book)
Console.WriteLine("'{0}' published by {1}",
product.Title, ((Book)product).Publisher);
else if (product is DVD)
Console.WriteLine("'{0}' is rated {1}",
product.Title, ((DVD)product).Rating);
}
}
The following is the output of the code in Listing 10-27:
All Products
============
'Spring in October' published by AnimalCover Press
'A Day in the Life of Sergeant Pepper' published by Colorful Press
'Saving Sergeant Pepper' is rated G