linq查询列车数据库

Question

我有一个火车数据库，包含所有必填字段，如train、Route、Station、Passenger、Reservation等。我想使用LINQ在两个车站之间获取火车(我知道SQL查询，但无法将其转换为LINQ(。请任何人帮帮我。

还有一件事，我想要LINQ查询的扩展方法方法(而不是关键字，如from，in，select(，如

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

SQL查询

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

如果你想要更多的细节，比如表格结构，请评论我。我从一张在线幻灯片中得到了表格结构和查询。

Answer 1

我使用类对数据库进行建模。试试这个

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