CODEWITHSUNDEEP
c#sqllinqoop
DeniCevap
DeniCevap

Reputation: 25

How to convert this query string into LINQ to SQL query?

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.

Any help?

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))";

I thought of first getting all rooms by

var querySearch = from q in db.Suites
                  where q.SuiteType == suitetype
                  select q;

and then checking it against another query that checks if suite number is in table Bookings and if it is between the dates specified.

But I get lost everytime.

Upvotes: 0

Views: 777

Answers (1)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93434

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.Contains(q.SuiteNumber)
              select q;

Upvotes: 1

Related Questions