10-3. 返回结果是一个标量值
问题
想取得存储过程返回的一个标量值.
解决方案
假设我们有如Figure 10-2所示的ATM机和ATM机取款记录的模型
Figure 10-2. 一个ATM机和ATM机取款记录的模型
我们想要用一个存储过程在指定机器和日期内返回所有的取款总额
Listing 10-9 是这个存储过程的代码
Listing 10-9. GetWithdrawals存储过程，获取在指定机器和日期内所有的取款总额
create procedure [Chapter10].[GetWithdrawals]
(@ATMId int, @WithdrawalDate date)
as
begin
select SUM(amount) TotalWithdrawals
from Chapter10.ATMWithdrawal
where ATMId = @ATMId and [date] = @WithdrawalDate
end
接下来就是调用这个存储过程的方法：
1. 右击模型的设计视图,选择“从数据库更新模型”.在对话框中“存储过程和函数”下勾选Chapter10/
GetWithdrawals.点击“完成”.
2.（ 译注：我的环境是win10+vs2013+ef6.1.3,是不需要这步的，第1步已经把这步也完成了）右击模型的设计视图, 选择“新增“ ➤ 函数导入. 从“存储过程/函数名称”下拉框中选择GetWithdrawals. 在“函数导入名称“文本框中输入：GetWithdrawals. 这个就是在模型中的方法名称.在“返回以下内容的集合“里勾选“标量”，在下拉框里选择Decimal.单击“确定”.
3.接下来用 Listing 10-10所示代码使用这个GetWithdrawals 存储过程
Listing 10-10. 用模型GetWithdrawals()方法查询存储过程 GetWithdrawals
static void Main(string[] args)
{
DateTime today = DateTime.Parse("5/7/2013");
DateTime yesterday = DateTime.Parse("5/6/2013");
using (var context=new EFRecipesEntities())
{
context.Database.ExecuteSqlCommand("delete from chapter10.ATMWithdrawal");
context.Database.ExecuteSqlCommand("delete from chapter10.ATMMachine");
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 EFRecipesEntities())
{
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("ATMWithdrawals for ATM at {0} at {1}",atm.ATMId,atm.Location);
Console.WriteLine("\t{0} Total Withdrawn={1}",yesterday.ToShortDateString(),forYesterday.Value.ToString("C"));
Console.WriteLine("\t{0} Total Withdrawn={1}",today.ToShortDateString(),forToday.Value.ToString("C"));
}
Console.WriteLine("\npress any key to exit...");
Console.Read();
}
输出结果如下面的 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
==============================================================
它是如何工作的？
注意：EF会把从存储过程取回的标量值认为是一个集合.在我们的例子里,我们的存储过程返回一个单一的decimal值，我们用FirstOrDefault()方法把它从集合中提取出来