LINQ select from join table where the same foreign key has records for two different IDs

Question

Ok, so the title is a little bit confusing I guess. Basically I have those 3 tables:

Line

id | Name
---------
1  | "A-B"
2  | "A-D"


Stop

id | Name
---------
1  | A
2  | B
3  | C
4  | D

LineStop

Id | LineId | StopId | Order
----------------------------
1  | 1      | 1      | 0
2  | 1      | 2      | 1
3  | 2      | 1      | 0
4  | 2      | 2      | 1
5  | 2      | 3      | 3
4  | 2      | 4      | 4

So this is some sort of bus ticketing system which I work on of personal improvement.

As an input I get the departure StopId (Stop.Id) and the arrival StopId (Stop.Id). I want to select all lines that has those two stops in their routes (This would mean that in LineSop table for the same LineId I'll have records with both the departuring and arrival stops, ultimately I would also like to consider the Order column which tells in what order the bus is going through those Stops, because even if the line have the two stops I'm interested in, if they are in reversed order I'm still not interested.

I know that is highly desirable to show what I've done so far but I struggle with the where conditions which seems to be the key factor here. For some reason I decided to join Line with LineStop:

var lines = _context.Lines.Join(
            _context.LineStop,
            line => line.Id,
            lineStop => lineStop.LineId,
            (line, lineStop) => lineStop)

But then.. I need to check if for the same LineId I have records in LineStop table with the start and end StopId and ultimately when I found such records the the starting StopId Order i less than the end StopId Order.

Answer 1

I hope this can help you out:

First I get the trip from the traveler: "I want go from Stop: 2 to Stop:4". Once I know the line that has both stops I build the stops and its order.

var lines = new List<Line>() 
{ 
    new Line() { Id = 1, Name = "A-B" },
    new Line() { Id = 2, Name = "A-D" }
}; 

var stops = new List<Stop>() {
    new Stop() { Id = 1, Name = "A" },
    new Stop() { Id = 2, Name = "B" },
    new Stop() { Id = 3, Name = "C" },
    new Stop() { Id = 4, Name = "D" }
};

var lineStops = new List<LineStop>() 
{
    new LineStop() { Id = 1, LineId = 1, StopId = 1, Order = 0 },
    new LineStop() { Id = 2, LineId = 1, StopId = 2, Order = 1 },
    new LineStop() { Id = 3, LineId = 2, StopId = 1, Order = 0 },
    new LineStop() { Id = 4, LineId = 2, StopId = 2, Order = 1 },
    new LineStop() { Id = 5, LineId = 2, StopId = 3, Order = 3 },
    new LineStop() { Id = 4, LineId = 2, StopId = 4, Order = 4 },
};  

var result =  (from trip in (from l  in lines
              join d in lineStops on l.Id equals d.LineId
              join a in lineStops on l.Id equals a.LineId
              where d.StopId == 2 && a.StopId == 4
              select new { d.LineId })
              join l in lines on trip.LineId equals l.Id
              join ls in lineStops on l.Id equals ls.LineId
              select new { l.Name, ls.StopId, ls.Order }).OrderBy(x => x.Order);

Expected result

Name StopId Order
A-D       1    0
A-D       2    1
A-D       3    3
A-D       4    4