C# LINQ State of DeleteOnSubmit()

Question

I am working on a web service for creating, changing and removing hotel reservations (bookings). One booking can contain several stays (a stay is a link between a room, the services ordered, etc.). Each stay has it's own time period, so you can create 3 stays in the same booking, for 3 different weeks.

My problem arises when bookings need to be changed. The input is the booking id (as seen above) as well as the new data on the wanted stays. So if the original stays were 2 single rooms and 1 double room, each with their own start and end date, and the customer wished to change one of the single rooms to a double room, he would send: 1 single, 2 double, and it's up to me to figure out what has changed (this is not going to change, so I'm afraid any solutions suggesting to change this is out).

I use LINQ to pick out the current stays and set them for deletion

var oldStays = (from stay in persistentStorage.DataContext.Stays 
                where stays.booking_id == bookingId 
                select stay);
persistentStorage.DataContext.Stays.DeleteAllOnSubmit(oldStays);

Before I can delete them, however, I need to make sure that there is available rooms for the new stays. An easy way out would be if LINQ automatically assumes these records as deleted when making new queries (meaning I would not have to worry about dates, existing rooms, etc.). If that is the case, the problem is already solved.

If not, I need some way of checking for new available reservations while at the same time ignoring stays that have not changed (in the above case, I would only need to check if a double room was available in the given period, since the other double and single are already booked). Is there some smart way LINQ can assist in this, and if so, how?

Answer 1

You should be able to get the intended behaviour by using transactions:

• create a transaction
• delete your bookings
• check if the new rooms are available and book them
• if everything is ok, commit
• otherwise rollback the transaction

EDIT - Sample (using SQL transaction, you could also use a TransactionContext):

try
{
  persistentStorage.DataContext.Transaction=persistentStorage.DataContext.Connection.BeginTransaction();

  var oldStays = (from stay in persistentStorage.DataContext.Stays 
                where stays.booking_id == bookingId 
                select stay);
  persistentStorage.DataContext.Stays.DeleteAllOnSubmit(oldStays);
  persistentStorage.DataContext.SubmitChanges();

  // do you select/insert/etc.

  if (ok)
    persistentStorage.DataContext.Transaction.Commit();
}
finally
{
  if (persistentStorage.DataContext.Transaction!=null) persistentStorage.DataContext.Transaction.Dispose();
  persistentStorage.DataContext.Transaction=null;
}