如何从存储过程结果实体框架创建列表

Question

我正在使用 ASP.NET MVC 和 EF 创建一个车辆预订应用程序，如果需要，用户可以在一个日期时间预订多辆车辆。我创建了一个存储过程来防止重复预订车辆，但是在弄清楚如何将结果添加到列表中时遇到了麻烦。

示例：我想为 2018 年 12 月 18 日的 12:00 到 13:00 保留 Vehicle#1 和 Vehicle#2....存储过程转到 db 以发现 Vehicle#1 已经从 12:00 保留至 13:00，但未预留车辆#2。由于foreachthat 运行，alreadyReservedVehicle返回结果，.Count() = 0因为它看到最后一个结果，即 Vehicle#2 未被保留。它应该显示一条错误消息，说不允许重复预订，因为车辆#1 已经预订，但它不计算该预订。

有没有办法收集这两个结果并告诉应用程序因为这 2 辆车中的一辆被预订，所以这两辆车都不能被预订？

public ActionResult Create([Bind(Include = "ID,StartDate,EndDate,RequestorID,Destination,PurposeOfTrip,TransportStudentsFG,LastChangedBy,LastChangedDate,VehicleList,ThemeColor")] Reservations reservation)

{

    if (ModelState.IsValid)

    {

        var selectedVehicles = reservation.VehicleList.Where(x => x.IsChecked == true).ToList();

        List<usp_PreventDoubleBooking_Result> alreadyReservedVehicle = null;

        // get each vehicle that was selected to be reserved then check db to make sure it is available

        foreach (var selectedVehicle in selectedVehicles) 

        {             

            using (VehicleReservationEntities db = new VehicleReservationEntities())

            {

                alreadyReservedVehicle = db.usp_PreventDoubleBooking(selectedVehicle.ID, reservation.StartDate, reservation.EndDate).ToList();                             

            }

        }

        if (alreadyReservedVehicle.Count() == 0) // create a new reservation if the vehicle is available at the selected date and time

        {

            db.Reservations.Add(reservation);

            reservation.LastChangedDate = DateTime.Now;

            db.SaveChanges();

        

        }

    }

}

以上是我到目前为止所拥有的，我知道我已经接近了，因为假设用户只是试图预订一辆已经预订的车辆，我所拥有的将起作用。当他们尝试为多辆车创建预订时，代码仅计算列表中的最后一辆车，并将其用作确定是否保存预订的结果。

我考虑将条件语句移动到 foreach 中，但我不希望为每辆选择的车辆保存预订......这没有任何意义，因为只有 1 个预订要保存，它只是有多个可以与之关联的车辆。

那么如何才能alreadyReservedVehicle List将每个结果添加到列表中，以便确定List实际的 a是否Count为 0？

Answer 1

public ActionResult Create(/*Bind attribute omitted*/ Reservations reservation){&nbsp; &nbsp; if (ModelState.IsValid)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; // Is 'IsChecked' nullable? If not, "== true" is redundant.&nbsp; &nbsp; &nbsp; &nbsp; var selectedVehicles = reservation.VehicleList.Where(x => x.IsChecked == true).ToList();&nbsp; &nbsp; &nbsp; &nbsp; // get each vehicle that was selected to be reserved then check db to make sure it is available&nbsp; &nbsp; &nbsp; &nbsp; using (VehicleReservationEntities db = new VehicleReservationEntities())&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (var selectedVehicle in selectedVehicles)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // 'alreadyReservedVehicle' can be declared here because you don't need to let it&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // out of its cage, I mean the loop.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<usp_PreventDoubleBooking_Result> alreadyReservedVehicle =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.usp_PreventDoubleBooking(selectedVehicle.ID, reservation.StartDate, reservation.EndDate).ToList();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (alreadyReservedVehicle.Count() > 0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //return error message on page if vehicle is already reserved&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TempData["Error"] = "Double booking of vehicles is not allowed. Please choose another vehicle/time. Check the availability timeline before reserving to ensure there are no errors. Thank you.";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return RedirectToAction("Create");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // create a new reservation if the vehicle is available at the selected date and time&nbsp; &nbsp; &nbsp; &nbsp; db.Reservations.Add(reservation);&nbsp; &nbsp; &nbsp; &nbsp; reservation.LastChangedDate = DateTime.Now;&nbsp; &nbsp; &nbsp; &nbsp; db.SaveChanges();&nbsp; &nbsp; }}

Answer 2

您可以执行以下操作：var&nbsp;preventDoubleBookingList=&nbsp;await&nbsp;context.Database.SqlQuery<usp_PreventDoubleBooking>("sproc_name",&nbsp;prams_here).ToListAsync();您必须创建一个与存储过程匹配的模型。