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

返回输出参数

问题

　　您要从存储过程的一个或多个输出参数中检索值。

解决方案

假设你有如下关系的表模型

　　对于一个给定的日期，你想知道的总租赁次数，总租金支付，和租用车辆。以下存储过程是获得你想要的信息的一种方式。

create procedure [dbo].[GetVehiclesWithRentals]
(@date date,
@TotalRentals int output,
@TotalPayments decimal(18,2) output)
as
begin
  select @TotalRentals = COUNT(*), @TotalPayments = SUM(Payment)
  from Rental
  where RentalDate = @date
select distinct v.*
  from Vehicle v join Rental r
  on v.VehicleId = r.VehicleId
end

1、新建Console Manager.添加Ado.Net 实体数据模型，选择Vehicle和Rental表及GetVehiclesWithRentals存储过程或者在模型设计界面，右击选择添加函数导入如下图所示

在DbContext中生成方法函数

public virtual ObjectResult<Vehicle> GetVehiclesWithRentals(Nullable<System.DateTime> date, ObjectParameter totalRentals, ObjectParameter totalPayments)
        {
            var dateParameter = date.HasValue ?
                new ObjectParameter("date", date) :
                new ObjectParameter("date", typeof(System.DateTime));
    
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Vehicle>("GetVehiclesWithRentals", dateParameter, totalRentals, totalPayments);
        }

3、在Program类中进行测试

class Program
    {
        static void Main(string[] args)
        {
            using (var context = new School5Entities1())
            {
                var car1 = new Vehicle
                {
                    Matufacture = "Toyota",
                    Model = "Camry",
                    Year = 2013
                };
                var car2 = new Vehicle
                {
                    Matufacture = "Chevrolet",
                    Model = "Corvette",
                    Year = 2013
                };
                var r1 = new Rental
                {
                    Vehicle = car1,
                    RentalDate = DateTime.Parse("5/7/2013"),
                    Payment = 59.95M
                };
                var r2 = new Rental
                {
                    Vehicle = car2,
                    RentalDate = DateTime.Parse("5/7/2013"),
                    Payment = 139.95M
                };
                context.Rentals.Add(r1);
                context.Rentals.Add(r2);
                context.SaveChanges();
            }

            using (var context = new School5Entities1())
            {
                string reportDate = "5/7/2013";
                var totalRentals = new ObjectParameter("TotalRentals", typeof(int));
                var totalPayments = new ObjectParameter("TotalPayments", typeof(decimal));
                var vehicles = context.GetVehiclesWithRentals(DateTime.Parse(reportDate),
                                 totalRentals, totalPayments);
                Console.WriteLine("Rental Activity for {0}", reportDate);
                Console.WriteLine("Vehicles Rented");
                foreach (var vehicle in vehicles)
                {
                    Console.WriteLine("{0} {1} {2}", vehicle.Year.ToString(),
                                       vehicle.Matufacture, vehicle.Model);
                }
                Console.WriteLine("Total Rentals: {0}",
                      ((int)totalRentals.Value).ToString());
                Console.WriteLine("Total Payments: {0}", ((decimal)totalPayments.Value).ToString("C"));
            }

        }
    }

运行结果