How to check existing in DB for any matching Room, BookDate and RoomId?

How to check if there exists an entry in the database? Debugging shows that the result returns null.

TimeSlots a Collection. I’m not sure if I’ve correctly done it.

Here is my context:

var result = await context.Bookings
    .SingleOrDefaultAsync(b => 
        b.BookDate == booking.BookDate 
        && b.TimeSlots == booking.TimeSlots 
        && b.RoomId == booking.RoomId);

public class Booking
{
    public int Id { get; set; }

    [Required]
    public DateTime BookDate { get; set; }

    [Required]
    public int RoomId { get; set; }

    public Room Room { get; set; }

    [Required]
    public ICollection<BookingTimeSlot> TimeSlots { get; set; }

    [Required]
    public ICollection<BookingModule> Modules { get; set; }

    public Booking()
    {
        TimeSlots = new Collection<BookingTimeSlot>();
        Modules = new Collection<BookingModule>();
    }
}

public class BookingTimeSlot
{
    public int BookingId { get; set; }    
    public int TimeSlotId { get; set; }
    public Booking Booking { get; set; }
    public TimeSlot TimeSlot { get; set; }
}

This is the input I’m trying to make:

{
    "RoomId": 1,
    "BookDate": "2020-10-27",
    "TimeSlots": [1, 3],
    "Modules": [1]
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Your question is not clear whether you are looking to find duplicates or get the first result.

Second I think you are missing the primary key in your model or are you using a composite key, either way its good read to help you fix that.

//With Linq and EF find and process duplicates     
// assuming Id is your primary key - [please fix this, some info for you][1] 
var duplicateBookings = context.Bookings.GroupBy(i => i.id)
                     .Where(x => x.Count() > 1)
                     .Select(val => val.Key); // or .SelectMany(i => i.ToList());
// do what you need
foreach(var dupes in duplicateBookings )
{
    //process or do what you need
    context.Bookings.DeleteObject(dupes); // for e.g. delete duplicate bookings
}

If you just want the first result, then change the sing to first

var result = await context.Bookings
    .FirstOrDefaultAsync(b =>        //first result
        b.BookDate == booking.BookDate 
        && b.TimeSlots == booking.TimeSlots 
        && b.RoomId == booking.RoomId);

Method 2

Well, for the most part referential integrity in the database does not solve such problems, and in the vast majority of cases RI is not used anyway. The reason is that’s not the job of RI but worse such RI violations tend to occur FAR TOO late for a user interface that informs the user that such a booking cannot be done. In other words you don’t try to make the booking, keep fingers crossed, and the hope the data can be written out.

WHAT you do is provide a UI that when the user selects a booking date, you give feedback that such a booking can’t be made, and as such NO DATABASE writes or updates will have YET occurred – hence this is a UI issue, not really a database RI issue. And even if it was a database RI issue, you would have to attempt to write the data, and often the user is JUST checking and asking for a particular booking date – not necessary ready to actually book.

a booking collision can be found based on this logic:

RequestStartDate <= EndDate 
and 
RequestEndDate >= StartDate

So any overlap or even a full bracketing will be found with the above simple query.

So, with above? Then with a room number and list of booking date ranges for that room, then a collision would be found with this:

@dtRequestStartDate = "Enter start Date" 
@dtRequestEndDate = "Enter end date"
@RoomNum = Room number

strSQL = SELECT * from tblBookings where  
         (@dtRequestStartDate <= RoomEndDate)
         AND
         (@dtRequestEndDate >= RoomStartDate)
         AND
         (@RoomNum = RoomNumber)

 If above row.Count > 0 then 
     message = Sorry, you cannot book that room

So what you do is check before a booking, and if above returns rows, then you don’t allow the booking. As long as you never allow overlaps for bookings, then the above SIMPLE logic will always work and always prevent a booking with collisions.

Method 3

I have found a solution to my problem. I compare the TimeSlotId using Intersect, if there are intersection, it then return true.

public bool BookingExist(Booking booking)
{
    var resultContext = context.Bookings
        .Where(b => b.Room.Id == booking.RoomId && b.BookDate == booking.BookDate)
        .SelectMany(b => b.TimeSlots.Select(bt => bt.TimeSlotId))
        .AsEnumerable();

    var resultInput = booking.TimeSlots.Select(bt => bt.TimeSlotId);

    if (resultContext.Intersect(resultInput).Count() > 0)
        return true;
    else
        return false;
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0