public IEnumerable<ScheduleStop> GetStopsById(Guid scheduleId)
{
const string sql = @"
SELECT
[ScheduleTrainStop].[ScheduleId]
,[ScheduleTrainStop].[StopNumber]
,[ScheduleTrainStop].[Arrival]
,[ScheduleTrainStop].[Departure]
,[ScheduleTrainStop].[Pass]
,[ScheduleTrainStop].[PublicArrival]
,[ScheduleTrainStop].[PublicDeparture]
,[ScheduleTrainStop].[Line]
,[ScheduleTrainStop].[Path]
,[ScheduleTrainStop].[Platform]
,[ScheduleTrainStop].[EngineeringAllowance]
,[ScheduleTrainStop].[PathingAllowance]
,[ScheduleTrainStop].[PerformanceAllowance]
,[ScheduleTrainStop].[Origin]
,[ScheduleTrainStop].[Intermediate]
,[ScheduleTrainStop].[Terminate]
,[Tiploc].[TiplocId]
,[Tiploc].[Tiploc]
,[Tiploc].[Nalco]
,[Tiploc].[Description]
,[Tiploc].[Stanox]
,[Tiploc].[CRS]
,[Station].[StationName]
,[Station].[Location].[Lat] AS [Lat]
,[Station].[Location].[Long] AS [Lon]
FROM [ScheduleTrainStop]
INNER JOIN [Tiploc] ON [ScheduleTrainStop].[TiplocId] = [Tiploc].[TiplocId]
LEFT JOIN [Station] ON [Station].[TiplocId] = [Tiploc].[TiplocId]
WHERE [ScheduleId] = @scheduleId
ORDER BY [ScheduleTrainStop].[StopNumber]";
using (DbConnection dbConnection = CreateAndOpenConnection())
{
return dbConnection.Query<ScheduleStop, StationTiploc, ScheduleStop>(
sql,
(st, t) =>
{
st.Tiploc = t;
return st;
},
new { scheduleId },
splitOn: "TiplocId");
}
}
