Entity Framework 6 Recipes 2nd Edition (10-4), entityrecipes

Entity Framework 6 Recipes 2nd Edition (10-4), entityrecipes

10-4. Return a complex type from the Stored Procedure

Problem

To use a stored procedure that returns a complex type in a method

Solution

Suppose we already have Figure 10-3. the model shown in. The Employee model contains the Employee ID, Name, and a complex Address attribute. The Address contains the Employee Address information, which includes the city, state, and the complex type of ZIP code.

 

Figure 10-3.OneEmployee entityThe object containsEmployeeAddressComplex type attributes

We want to use a stored procedure to return the address set of the complex type of EmployeeAddress.

The Stored Procedure Code is shown in Listing 10-11:

Listing 10-11.By specifyingCity returns the stored procedure of all SSE information of all employees.

Create procedure [Chapter10]. [GetEmployeeAddresses]

(@ City varchar (50 ))

As

Begin

Select [address], city, [state], ZIP

From Chapter10.Employee where city = @ city

End

Next, use the stored procedure:

1. Right-click the design view of the model and select "update model from database". Select Chapter10/Under "Stored Procedure and function" in the dialog box/

GetEmployeeAddresses. Click "finish ".

2. (Note: My environment is win10 + vs2013 + ef6.1.3. This step is not required, and this step has been completed in step 1, only the name in the final "back to the following content set" is also automatically created by the system. It must be changed to EmployeeAddress.) Right-click the design view of the model and select "add" transform function import. select GetEmployeeAddresses from the stored procedure/function name drop-down box. in the "Function Import Name" text box, enter GetEmployeeAddresses. this is the method name in the model. in the "back to the following content set", select "complex" and select "EmployeeAddress" in the drop-down box. click OK ".

3. Use the GetEmployeeAddresses stored procedure. The code is shown in Listing 10-12:

Listing 10-12.The GetEmployeeAddresses () method in the model uses the GetEmployeeAddresses Stored Procedure

Class Program

{

Static void Main (string [] args)

{

Using (var context = new EFRecipesEntities ())

{

Context. Database. ExecuteSqlCommand ("delete from chapter10.Employee ");

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

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 EFRecipesEntities ())

{

Console. WriteLine ("maid 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 );

}

}

Console. WriteLine ("\ nPress any key to exit ...");

Console. ReadKey ();

}

}

The output result is as follows:

========================================================== ======================================

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 provide a convenient way to refactor duplicate attributes to a single type, And this type can be reused for multiple objects. in this section, we create a stored procedure that returns all the addresses of all employees based on the given city. We map the columns in the stored procedure to the fields in the complex type EmployeeAddress. the GetEmployeeAdresses () method is created through the "Function Import" Wizard. It returns a set of instances of the EmployeeAddress type.

Complex types are usually used in the stored procedure that returns any type of data. and the data is not necessarily mapped to any entity in the model. even if complex types are not tracked by object contex, it is also a lightweight and efficient tool for processing data in the model.

Appendix: script file of the database used in the Creation example