linq queries in train database

Question

I have a train database, with all required fields such as Train, Route, Station, Passenger, Reservation etc. I want to get trains between a pair of stations using LINQ(I know the SQL Query, but I am unable to convert it to LINQ). Please anyone help me.

One more thing, I want the Extension method approach of LINQ Queries(not the keywords like from, in, select), like

var trains = _context.Trains.FirstOrDefault(a => a.Id == text);

The SQL Query

SELECT Route.TrainId, Train.TrainName 
FROM Route 
INNER JOIN Train ON Route.TrainId=Train.TrainId 
WHERE 
    (Route.StationId IN 
        (SELECT Source.StationId 
         FROM Route AS Source 
         INNER JOIN Route AS Destination ON Source.TrainId=Destination.TrainId 
         WHERE 
             (Source.StopNumber - Destination.StopNumber < 0) 
              AND 
             (Source.StationId = @Source) 
              AND
             (Destination.StationId = @Destination)
         )
     )

Please comment me if you want any more details like table structures. I got the table structure and queries from an online slide.

Welcome to StackOverflow. What have you tried? Where are you stuck? This is not a code writing service. Please review the tour and [how to ask questions pages](https://stackoverflow.com/help/how-to-ask). — Jason Armstrong, Dec 01 '18 at 19:45
Sorry, but the problem is that i want to convert my sql code to linq. I have tried but I'm not getting the desired output — Sourodeep Chatterjee, Dec 01 '18 at 20:34
Why not share what you have tried? You'll find that folks are more likely to help if they see that you've put some effort into it and ultimately you'll probably learn more in the process. — Jason Armstrong, Dec 01 '18 at 23:06

score 0 · Answer 1 · answered Dec 01 '18 at 23:30

I used classes to model your database. Try something like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Train> trains = new List<Train>();
            string source = "abc";
            string destination = "xyz";


            var results = trains.Where(x => x.Routes.Any(y => y.StationId == source) && x.Routes.Any(y => y.StationId == destination))
                .Select(x => new {
                    source = x.Routes.Where(y => y.StationId == source).FirstOrDefault(),
                    destination = x.Routes.Where(y => y.StationId == destination).FirstOrDefault()
                })
                .Where(x => x.destination.StopNumber > x.source.StopNumber)
                .ToList();
        }
    }
    public class Train
    {
        public string TrainName { get; set; }
        public List<Route> Routes { get; set; }
    }
    public class Route
    {
        public string TrainId { get; set; }
        public string StationId { get; set; }
        public int StopNumber { get; set; }
    }
}

linq queries in train database

1 Answers1