Stored Procedures - Entity Framework 6 Recipes - page 341

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