《Entity Framework 6 Recipes》中文翻译——第十章EntityFramework存储过程处理（三）

返回一个标量值结果集

　　问题

　　您希望使用一个存储过程，返回一个包含单个标量值的结果集。

　　解决方案

　　假设你有如下的表单模型

　　您希望使用一个存储过程，返回给定日期从给定的自动取款机上提取的总金额。存储过程代码如下

create procedure [dbo].[GetWithdrawals]
(@ATMId int, @WithdrawalDate date)
as
begin
        select SUM(Amount) TotalWithdrawals
        from ATMWithdrawal
        where ATMId = @ATMId and [date] = @WithdrawalDate
end

1、右键单击ADO.NET数据实体模型的设计界面，选择从数据库中选择更新模型。在对话框中，选择“获取取款”存储过程。单击“完成”将存储过程添加到模型中。

2、右键单击设计图面上，并选择“添加➤函数导入。从所存储的过程名称下拉列表中选择获取存储过程的存储过程。在“函数导入名称”文本框中，输入“GetWithdrawals”。这将是模型中的方法的名称。选择方法的返回类型，并选择在下拉的Decimal 。单击“确定”。

3、在以下代码中使用GetWithdrawals存储函数

DateTime today = DateTime.Parse("5/7/2013");
            DateTime yesterday = DateTime.Parse("5/6/2013");
            using (var context = new School5Entities())
            {
                var atm = new ATMMachine { 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 School5Entities())
            {
                var forToday = context.GetWithdrawals(2, today).FirstOrDefault();
                var forYesterday = context.GetWithdrawals(2, yesterday).FirstOrDefault();
                var atm = context.ATMMachines.Where(o => o.ATMId == 2).FirstOrDefault();
                Console.WriteLine("ATM Withdrawals for ATM at {0} at {1}",
                         atm.ATMId.ToString(), 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"));
            }

调用结果