Linq表达式检查某些日期的房间可用性。 [英] Linq expression to check availability of room of certain dates.

问题描述

嘿那里



我正在尝试建立一个系统，用户将输入到达日期和出发日期以及他们希望拥有的房间类型然后程序将检查是否有任何类型的房间可用然后如果是这样它将返回房间号码。



这是我的视图模型：

[查看模型]



这是我的房间表：

[房间]



这是我的预订表

[ RES ERVATIONS ]



这是我的观点

[ CheckView ]



问题是我不确定如何使用LINQ表达式来获取房间的房间号码，房间类型与所述的房间类型相同但不属于任何预留期间选定的日期。



我最接近的是使用我在创建预订时使用的相同LINQ表达式来检查它是否在管理CRUD中的那些日期被占用。该表达式为：

< pre> 
 
  var  currentBooking = db.Reservations 
 
 .Where（b = >  b.RoomId == CurrentReservation.RoomId）
。选择（b = > （b.Arrival <  = CurrentReservation.Arrival&& 
 b.Depature >  = CurrentReservation.Arrival）|| 
（b.Arrival <  CurrentReservation.Depature&& b.Depature >  = 
 CurrentReservation.Depature）|| 
（CurrentReservation.Arrival <  = b.Arrival 
&& CurrentReservation.Depature < span class =code-keyword>>  = b.Arrival））
 .FirstOrDefault（）; 

然而，att我试图修改它以适应我的情况，这让我感到非常难过，任何帮助都是适当的。



我尝试过的事情：



我试图使用SQL到linq转换器并失败。

许多尝试都是纯粹的原始SQl只会导致错误或错误返回。 div class =h2_lin>解决方案

根据实体的设置方式，这样的事情应该有效：

  string  desiredRoomType = ...; 
 DateTime arrivalDate = ...; 
 DateTime departureDate = ...; 
 
  var  matchingRooms = db.Rooms 
 .Where（room = >  room.RoomType == desiredRoomType）
 .Where（room = >  room.Reservations.All（res = >  res.Departure <  arrivalDate || res.Arrival >  departureDate））
; 

（你也可以写成！room.Reservations.Any（res => res.Departure> = arrivalDate&& res.Arrival< = departureDate），但我认为使用全部更干净。 ）



SQL中的等价物是：

  SELECT  
 Id，
 RoomCosts，
 Room_Type，
 RoomNumber 
  FROM  
 dbo.Rooms 作为 R 
  WHERE  
 Room_Type =  @ RoomType  
 和 
 不 存在 
（
  SELECT   1  
  FROM  dbo.Reservations 作为 B 
  WHERE  B.RoomId = R.Id 
 并且 B.Departure> =  @ ArrivalDate  
 和 B.Arrival< ; =  @ DepartureDate  
）

Hey there

I am trying to make a system where a user will input an Arrival Date and a Departure Date and a Room Type they wish to have then the program will check if it any rooms of that type are available then if so it will return the room numbers.

This is my view model:
[View Model]

This is my rooms table:
[ROOMS]

This is my Reservation Table
[RESERVATIONS]

And this is my View
[CheckView]

The problem is that I am unsure how to use an LINQ expression to obtain the Room Numbers of the rooms with the same RoomTypes as stated but that are not part of any reservation during the selected dates.

The closest I have gotten is to using the same LINQ expression I use when creating a Reservation to check if it is occupied during those dates in the admin CRUD. That Expression is:

<pre>  
          
var currentBooking = db.Reservations

.Where(b => b.RoomId == CurrentReservation.RoomId)
.Select(b => (b.Arrival <= CurrentReservation.Arrival &&
               b.Depature >= CurrentReservation.Arrival) ||
               (b.Arrival < CurrentReservation.Depature && b.Depature >= 
                                           CurrentReservation.Depature)||
               (CurrentReservation.Arrival <= b.Arrival                              
                                      && CurrentReservation.Depature >= b.Arrival))
.FirstOrDefault();

Yet attempting to modify this to fit my situation has left me absolutely stumped and any help is appriciated.

What I have tried:

I have tried to use a SQL to linq converter and failed.
Many attempts are pure Raw SQl have just lead to errors or false returns.

解决方案

Depending on how your entities are set up, something like this should work:

string desiredRoomType = ...;
DateTime arrivalDate = ...;
DateTime departureDate = ...;

var matchingRooms = db.Rooms
    .Where(room => room.RoomType == desiredRoomType)
    .Where(room => room.Reservations.All(res => res.Departure < arrivalDate || res.Arrival > departureDate))
;

(You could also write that as !room.Reservations.Any(res => res.Departure >= arrivalDate && res.Arrival <= departureDate), but I think it's cleaner to use All instead.)

The equivalent in SQL would be:

SELECT
    Id,
    RoomCosts,
    Room_Type,
    RoomNumber
FROM
    dbo.Rooms As R
WHERE
    Room_Type = @RoomType
And
    Not Exists
    (
        SELECT 1
        FROM dbo.Reservations As B
        WHERE B.RoomId = R.Id
        And B.Departure >= @ArrivalDate
        And B.Arrival <= @DepartureDate
    )

这篇关于Linq表达式检查某些日期的房间可用性。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！