10-4. 从存储过程返回一个复杂类型
问题
想在方法中使用一个返回复杂类型的存储过程
解决方案
假设我们已经有如Figure 10-3.所示的模型，该Employee （雇员）模型包含EmployeeId,Name和一个复杂类型的Address属性，Address包含Employee地址信息：它是包含city, state, 和ZIP code的复杂类型EmployeeAddress
Figure 10-3. 一个Employee entity实体含有一个名为EmployeeAddress 复杂类型的属性
我们想要用一个存储过程返回复杂类型EmployeeAddress的地址集合
存储过程代码如Listing 10-11所示:
Listing 10-11. 通过指定City 返回所有Employee的所有Addresse信息的存储过程
create procedure [Chapter10].[GetEmployeeAddresses]
(@city varchar(50))
as
begin
select [address], city, [state], ZIP
from Chapter10.Employee where city = @city
end
接下来使用这个存储过程：
1. 1. 右击模型的设计视图,选择“从数据库更新模型”.在对话框中“存储过程和函数”下勾选Chapter10/
GetEmployeeAddresses.点击“完成”.
2.（ 译注：我的环境是win10+vs2013+ef6.1.3,是不需要这步的，第1步已经把这步也完成了,只是最后的“返回以下内容的集合”里的名称也是它自动创建的，必须修改为EmployeeAddress）右击模型的设计视图, 选择“新增“ ➤ 函数导入. 从“存储过程/函数名称”下拉框中选择GetEmployeeAddresses. 在“函数导入名称“文本框中输入：GetEmployeeAddresses. 这个就是在模型中的方法名称.在“返回以下内容的集合“里勾选“复杂”，在下拉框里选择EmployeeAddress.单击“确定”.
3. 接下来就是使用GetEmployeeAddresses存储过程，代码如 Listing 10-12 所示:
Listing 10-12.模型中的GetEmployeeAddresses()方法使用GetEmployeeAddresses存储过程
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 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 EFRecipesEntities())
{
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);
}
}
Console.WriteLine("\nPress any key to exit...");
Console.ReadKey();
}
}
输出结果如下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
=================================================================================
它是如何工作的
复杂类型提供一种便利的方式重构重复的一组属性到一个单一的类型，而且这类型可重用于多个实体. 在本小节, 我们创建了一个根据给定城市返回所有员工的所有地址信息的存储过程,我们把存储过程中的列映射到复杂类型EmployeeAddress里的字段. 方法GetEmployeeAdresses() 是通过“函数导入”向导创建的,它返回一个EmployeeAddress类型的实例集合.
复杂类型通常用在返回任意类型数据的存储过程上. 而且这些数据不是必须映射到任何模型里的实体. 即使复杂类型不会被object contex 跟踪, 但它也是一个轻量级并且有效率的处理模型里的数据的工具。