TrainNotifier.Service.TrainMovementRepository.NearestTrains C# (CSharp) Method

        public IEnumerable<TrainMovementResult> NearestTrains(double lat, double lon, int limit)
        {
            DateTime endDate = DateTime.Now;
            DateTime startDate = endDate.AddMinutes(-15);

            var tiplocs = _tiplocRepository.GetByLocation(lat, lon, 5)
                .Select(t => t.TiplocId)
                .ToList();
            if (!tiplocs.Any())
            {
                return Enumerable.Empty<TrainMovementResult>();
            }

            const string sql = @"
                SELECT DISTINCT TOP({0})
                    [LiveTrain].[ScheduleTrain]
                    ,[LiveTrainStop].[ActualTimestamp]
                FROM [LiveTrainStop]
                INNER JOIN [LiveTrain] ON [LiveTrainStop].[TrainId] = [LiveTrain].[Id]
                WHERE [LiveTrainStop].[ReportingTiplocId] IN @tiplocs
                AND [LiveTrainStop].[ActualTimestamp] BETWEEN @startDate AND @endDate
                AND [LiveTrain].[ScheduleTrain] IS NOT NULL
                ORDER BY [LiveTrainStop].[ActualTimestamp] DESC";

            var schedules = Query<NearestStationResult>(string.Format(sql, limit), new { startDate, endDate, tiplocs });

            var allSchedules = GetSchedules(schedules.Select(s => s.ScheduleTrain).Distinct(), startDate.Date);

            // need to get live running data between these dates
            startDate = startDate.Date;
            endDate = endDate.Date.AddDays(1);
            var allActualData = GetActualSchedule(allSchedules.SelectMany(s => s.ScheduleIds).Distinct(), startDate, endDate);

            IEnumerable<ExtendedCancellation> cancellations = null;
            IEnumerable<Reinstatement> reinstatements = null;
            IEnumerable<ChangeOfOrigin> changeOfOrigins = null;

            if (allActualData.Any())
            {
                using (DbConnection connection = CreateAndOpenConnection())
                {
                    cancellations = GetCancellations(allActualData.Select(s => s.Id), connection)
                        .ToList();
                    reinstatements = GetReinstatements(allActualData.Select(s => s.Id), connection)
                        .ToList();
                    changeOfOrigins = GetChangeOfOrigins(allActualData.Select(s => s.Id), connection)
                        .ToList();
                }
            }
            else
            {
                cancellations = Enumerable.Empty<ExtendedCancellation>();
                reinstatements = Enumerable.Empty<Reinstatement>();
                changeOfOrigins = Enumerable.Empty<ChangeOfOrigin>();
            }

            ICollection<TrainMovementResult> results = new List<TrainMovementResult>(allSchedules.Count());
            foreach (var schedule in allSchedules)
            {
                var actual = allActualData.FirstOrDefault(a => schedule.ScheduleIds.Contains(a.ScheduleId));
                var can = actual != null ?
                    cancellations.Where(c => c.TrainId == actual.Id).ToList() :
                    Enumerable.Empty<ExtendedCancellation>();
                var rein = actual != null ?
                    reinstatements.Where(c => c.TrainId == actual.Id).ToList() :
                    Enumerable.Empty<Reinstatement>();
                var coo = actual != null ?
                    changeOfOrigins.Where(c => c.TrainId == actual.Id).ToList() :
                    Enumerable.Empty<ChangeOfOrigin>();
                results.Add(new TrainMovementResult
                {
                    Schedule = schedule,
                    Actual = actual,
                    Cancellations = can,
                    Reinstatements = rein,
                    ChangeOfOrigins = coo
                });
            }

            return results
                .OrderBy(s => s.Schedule.DateFor)
                .ThenBy(s =>
                {
                    if (s.Schedule.Stops == null || !s.Schedule.Stops.Any())
                        return default(TimeSpan?);

                    var tiplocStops = s.Schedule.Stops.Where(stop => tiplocs.Contains(stop.Tiploc.TiplocId));
                    if (!tiplocStops.Any())
                        return default(TimeSpan?);

                    var firstStop = tiplocStops.First();
                    return firstStop.PublicArrival ?? firstStop.Arrival ?? firstStop.Departure ?? firstStop.PublicDeparture ?? firstStop.Pass;
                });
        }