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

Question

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.

What is your reason for converting a perfectly good SQL statement to Linq? Are you required to use Linq for your Final project? — Michael Z.
– Michael Z., Commented May 20, 2016 at 17:02
@MichaelZ. Yes, we have to use LINQ to SQL as one of the requirements... otherwise I would go for simple SQL. — DeniCevap
– DeniCevap, Commented May 20, 2016 at 17:42

Erik Funkenbusch · Accepted Answer · 2016-05-20 21:06:00Z

1

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;

edited May 20, 2016 at 21:06

answered May 20, 2016 at 16:45

Erik Funkenbusch

93.7k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Matthew Whited Over a year ago

Shouldn't he use .Except(...)

Michael Z. Over a year ago

If you have to break a SQL statement into multiple Linq queries then it's probably not worth it. You're actually doing more processing than you would if you just used the SQL. Maybe run the SQL and then use Linq against the returned DataTable.

Robert Noack Over a year ago

@MichaelZ. maybe, but as far as I know unless you call ToList or something similar, a query won't be executed. Multiple lines of code can still result a single query unless I'm wrong about this ? Even both lines of code listed won't actually execute any SQL until the results are fetched/iterated?

Erik Funkenbusch Over a year ago

@MichaelZ. - I disagree with your assessment, you can break linq queries up in to many pieces for readability. I could have just as easily use a let statement to make it all one query, but choose to break it apart to be easier to read.

GendoIkari Over a year ago

I often follow the pattern of building up an IQueryable across multiple statements, especially when the where clause depends on various parameters that are passed in. No actual SQL is used against the database until you attempt to read data from the IQueryable.

|

Collectives™ on Stack Overflow

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

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related