代码之家 › 专栏 › 技术社区 › Leron

LINQ2实体查询以查找给定混合最大范围的记录

linq-to-entities c#

Leron · 技术社区 · 6 年前

我有这两个实体:

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; }
}

商业案例是,我正在实施一个公共汽车票预订系统(主要用于学习目的),我希望找到重叠的车票。

LineId ScheduleId + ForDate

我的问题是,给定起点和终点位置,确定一站或多站的两张车票是否重叠。

这个 LineStopStation StopId 和 Order 顺序

所以我得到的是 , 调度ID , StartId EndId 其中starId和endId对应于 LineStopStation.StopId 所以最终我能像这样从他们那里得到订单。

        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;

因此,我非常确信,如果有这些信息,我应该能够找到 TicketStop 工作原理是这样的-如果有人买了一张3站的车票,我会有三张同样的记录 TicketId 三种不同的 LineStopStationId .

我觉得这个问题的范围超出了需要。所以基本上我有:

 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

 }

LINQ

0 回复 | 直到 6 年前

NetMage 6 年前

如果不深入分析你的模型,也许这样的东西可以给你一个想法?

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);

你也可以先过滤,然后做一个空的 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);

SeatId s、您只需要从组中选择它们。

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));