Entity Framework 6 Recipes 2nd Edition（10-3）译 -> 返回结果是一个标量值

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(" {0} Total Withdrawn={1}",yesterday.ToShortDateString(),forYesterday.Value.ToString("C"));

                Console.WriteLine(" {0} Total Withdrawn={1}",today.ToShortDateString(),forToday.Value.ToString("C"));

            }

            Console.WriteLine(" press 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()方法把它从集合中提取出来

附：创建示例用到的数据库的脚本文件