I am doing a final project in a C# class where I have to build a Hotel Management system, but I am currently stuck on checking if a room is already booked between two dates. I have tried to figure it out on my own, but I do not know how to convert this query string into LINQ.
query = "SELECT * FROM Suites WHERE SuiteType = " + suitetype + " AND NumberOfAdultsAllowed >= " + adults + " AND NumberOfChildrenAllowed >= " + children + " AND ";
query += "Suites.SuiteNumber NOT IN (SELECT SuiteNumber FROM Bookings WHERE ";
query += "(StartDate BETWEEN @_start AND @_end) OR ";
query += "(EndDate BETWEEN @_start AND @_end) OR ";
query += "(@_start BETWEEN StartDate and EndDate) OR ";
query += "(@_end BETWEEN StartDate and EndDate) OR ";
query += "(@_start <= StartDate and @_end >= EndDate) OR ";
query += "(@_start >= @_end))";
var querySearch = from q in db.Suites
where q.SuiteType == suitetype
This is pretty basic. The only wrinkle is the sub-select, which you can do as follows.
Some of your bookings logic is a bit redundant, it's impossible for a date that is in between both start and end to not also have either the start or end be between the start/end date. So you can reduce this a bit.
Also, the last or condition invalidates almost all of the previous logic, as it includes any date that has the end come after the start.
Note: This is untested, but should work.
var bookings = from b in db.Bookings where (b.StartDate >= start b.StartDate <= end) || (b.EndDate >= start && b.EndDate <= end) select b.SuiteNumber; var querySearch = from q in db.Suites where q.SuiteType == suitetype && q.NumberOfAdultsAllowed >= adults && q.NumberOfChildrenAllowed >= children && bookings.Except(q.SuiteNumber) select q;