Stored Procedures - Entity Framework 6 Recipes - page 346

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.

1. Right-click the design surface, and select Update Model From Database. In the dialog

box, select the GetEmployeeAddresses stored procedure. Click Finish to add the stored
procedure to the model.

2. Right-click the design surface, and select Add ➤ Function Import. Select the

GetEmployeeAddresses stored procedure from the Stored Procedure Name drop-down.
In the Function Import Name text box, enter GetEmployeeAddresses . This will be the
name used for the method in the model. Select the Complex Return Type, and select
EmployeeAddress in the drop-down. Click OK.

Follow the pattern in Listing 10-12 to use the GetEmployeeAddresses stored procedure. 3.

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",