Linq2Entities query to find records given mix-max range

Question

I have those two entities:

public class Ticket
{
    public int Id { get; set; }
    public int ScheduleId { get; set; }
    public int SeatId { get; set; }

    [DataType(DataType.Date)]
    [Column(TypeName = "Date")]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    public DateTime ForDate { get; set; }

    public Seat Seat { get; set; }
    public Schedule Schedule { get; set; }
    public ICollection<TicketStop> TicketStops { get; set; }
}

public class TicketStop
{
    public int Id { get; set; }
    public int TicketId { get; set; }
    public int LineStopStationId { get; set; }

    public Ticket Ticket { get; set; }
    public LineStopStation LineStopStation { get; set; }
}

public class LineStopStation
{
    public int Id { get; set; }
    public int LineId { get; set; }
    public int StopId { get; set; }
    public int Order { get; set; }
    public bool IsLastStop { get; set; }

    public Line Line { get; set; }
    public Stop Stop { get; set; }
}

The business case is that I am implementing a bus ticket reservation system (for learning purposes mostly) and I want to find overlapping tickets.

The combination of LineId + ScheduleId + ForDate identifies uniquely that that a ticket is for certain bus at a certain date and departure time.

The problem I have is to identify, given starting and end location whether or not two tickets overlap for one or more stops.

The LineStopStation entity holds information about the StopId and the Order in which it is visited during the bus trip. So basically, overlapping tickets will have same Order number at some point (unless it is the last stop, which means that the passenger is leaving the bus).

So what I have is LineId, ScheduleId, StartId and EndId where starId and endId correspond to LineStopStation.StopId so eventually I am able to get the Order out of them like this.

        int startStationOrder = _context.LineStopStations
            .First(l => l.LineId == lineId && l.StopId == startId).Order;

        int endStationOrder = _context.LineStopStations
            .First(l => l.LineId == lineId && l.StopId == endId).Order;

So I am pretty convinced that having all this information I should be able to find if in TicketStop table I have ticket which overlaps with the data in question. TicketStop works this way - if someone bought a ticket for 3 stops I will have three records there with the same TicketId and three different LineStopStationId.

I feel that this question got a bigger than needed. So basically I have this:

 public List<Seat> GetAvailableSeats(int lineId, int scheduleId, int startId, int endId, DateTime forDate)
 {
   int startStationOrder = _context.LineStopStations
       .First(l => l.LineId == lineId && l.StopId == startId).Order;

   int endStationOrder = _context.LineStopStations
       .First(l => l.LineId == lineId && l.StopId == endId).Order;

   var reservedSeats = _context.TicketStops
       .Where(t => t.Ticket.ScheduleId == scheduleId)
       .Where(t => t.Ticket.ForDate == forDate)
       //basically I don't know how to proceed here.
       //In pseudo code it should be something like:
       .Where(t => t.Min(Order) >= endStationOrder || t.Max(Order) <= startStationOrder

 }

But obs. this is not how LINQ works. So how can I find all tickets where this range overlaps?

Answer 1

Without an in-depth analysis of your model, perhaps something like this could give you an idea?

var reservedSeats = _context.TicketStops
                            .GroupBy(t => new { t.Ticket.ScheduleId, t.Ticket.ForDate })
                            .Where(tg => tg.Key == new { ScheduleId = scheduleId, ForDate = forDate })
                            .Where(tg => tg.Min(t => t.LineStopStation.Order) >= endStationOrder || tg.Max(t => t.LineStopStation.Order) <= startStationOrder);

You could also filter first and do an empty GroupBy:

var reservedSeats = _context.TicketStops
                            .Where(t => t.Ticket.ScheduleId == scheduleId && t.Ticket.ForDate == forDate)
                            .GroupBy(t => 1)
                            .Where(tg => tg.Min(t => t.LineStopStation.Order) >= endStationOrder || tg.Max(t => t.LineStopStation.Order) <= startStationOrder);

To return all the SeatIds, you just need to select them from the group.

var reservedSeats = _context.TicketStops
                            .Where(t => t.Ticket.ScheduleId == scheduleId && t.Ticket.ForDate == forDate)
                            .GroupBy(t => 1)
                            .Where(tg => tg.Min(t => t.LineStopStation.Order) >= endStationOrder || tg.Max(t => t.LineStopStation.Order) <= startStationOrder);
                            .SelectMany(tg => tg.Select(t => t.Ticket.SeatId));