I want to get specific data by comparing two Id's out of different model classes. The following picture is from the "DeskController" and I want to get the "DeskId" from the "Booking"-Model class. I don't know how to reach the Booking class from the DeskController.

DeskController:

[HttpGet("available/{start}&{end}")]
    public async Task<IEnumerable<Desk>> GetAvailableDesks(DateTime start, DateTime end)
    {
        var availableDesks = new List<Desk>();
        foreach (var desk in _context.Desk)
        {
            var bookings = await _context.Booking.FindAsync( == desk.Id); /* Compare Booking.DeskId with desk.Id */
            bool available = true;
            foreach (var booking in bookings)
            {
                if ((booking.End >= start) && (booking.Start < end))
                {
                    available = false;
                    break;
                }
                
            }
            if (available)
            {
                availableDesks.Add(desk);
            }
        }
        return availableDesks;

Booking class:

public class Booking
{
    public Guid Id { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }

    public Guid DeskId { get; set; }
    public Desk Desk { get; set; }

    public Guid PersonId { get; set; }
    public Person Person { get; set; }
}

Desk class:

public class Desk
{
    public Guid Id { get; set; }
    public string Description { get; set; }
    public ICollection<Person> Bookings { get; set; }
}

CodePudding user response：

I guess you need to use .Where LINQ method:

var bookings = await _context.Booking
   .Where(booking => booking.DeskId == desk.Id)
   .Where(booking => booking.End >= start)
   .Where(booking => booking.Start < end)
   .ToListAsync();

UPD: As @Young Shun suggested it's better to filter items on db side

CodePudding user response：

Would suggest performing the inner join and filtering operation on the database side.

var availableDesks = await (
    from a in _context.Desk
    join b in _context.Booking on a.Id equals b.DeskId
    where !(b.End >= start 
        && b.Start < end)
    select a
).ToListAsync();

The reason is you are firing m time from the Desk record(s) for querying Book record(s), hence it potentially overloads the database server performance.