主页 / 问题 / 1478060
Accepted
Harry
Asked:2022-12-16 02:18:53 +0000 UTC2022-12-16 02:18:53 +0000 UTC

如何编写从 LINQ 中的多个表收集数据的查询？

  • 772

我还需要扮演请愿人的角色……我需要帮助在 LINQ 中编写查询。

逐字：

使用 LINQ to Object 查找每个机场自 2020 年 1 月 1 日起作为出发机场 (FromAirportId) 的航班数量。至 2020 年 12 月 31 日 （具有数据 AirportId、AirportNam、FlightCount 的对象列表）。按出发次数排序。第 48 行应该输入什么代码片段？

代码在这里：

namespace Application
{
    public class Airport // аеропорт
    {
        public int AirportId { get; set; }
        public string AirportName { get; set; }
    }
    public class Airplane //літак
    {
        public int AirplaneId { get; set; }
        public DateTime DateAirplane { get; set; } //дата виробництва
    }
    public class Flight //рейс
    {
        public int FlightId { get; set; }
        public int AirplaneId { get; set; }
        public int FromAirportId { get; set; } //аеропорт вильоту
        public int ToAirportId { get; set; } //аеропорт призначеня
        public DateTime FlightDate { get; set; } //lдата рейсу
        public int Passengers { get; set; } //кількість пасажирів
    }
    class Program
    {
        static void Main(string[] args)
        {
            var airports = new List<Airport>(){
                new Airport {AirportId = 1, AirportName = "Бориспіль"},
                new Airport {AirportId = 2, AirportName = "Charles de Gaulle"},
                new Airport {AirportId = 3, AirportName = "Schiphol"},
                new Airport {AirportId = 4, AirportName = "El Prat"}
            };
            var airplanes = new List<Airplane>(){
                new Airplane {AirplaneId = 10, DateAirplane = new DateTime(2000, 5, 25)},
                new Airplane {AirplaneId = 20, DateAirplane = new DateTime(2002, 10, 25)},
                new Airplane {AirplaneId = 30, DateAirplane = new DateTime(1989, 12, 30)}
            };
            var flights = new List<Flight>(){
                new Flight {FlightId =  100, AirplaneId  = 10, FromAirportId = 1, ToAirportId = 2, FlightDate = new DateTime(2020, 5, 25), Passengers = 150},
                new Flight {FlightId =  200, AirplaneId  = 10, FromAirportId = 1, ToAirportId = 3, FlightDate = new DateTime(2020, 6, 25), Passengers = 100},
                new Flight {FlightId =  300, AirplaneId  = 10, FromAirportId = 2, ToAirportId = 1, FlightDate = new DateTime(2020, 5, 26), Passengers = 95},
                new Flight {FlightId =  400, AirplaneId  = 20, FromAirportId = 1, ToAirportId = 3, FlightDate = new DateTime(2020, 8, 26), Passengers = 155},
                new Flight {FlightId =  500, AirplaneId  = 20, FromAirportId = 3, ToAirportId = 1, FlightDate = new DateTime(2020, 8, 27), Passengers = 132}
            };

            var query =  ???????????????


            foreach (var x in query)
            {
                Console.WriteLine(x.AirportId + " " + x.AirportName + " " + x.FlightCount);
            }

}
}
}

第 48 行充满了问号。

到目前为止，结果是——通过一个地方（lambda 函数），这已经是 C 级了。但是我不明白如何获得机场的名称。在SQL中，你可以从几个表中收集查询，但是这里好像不行？

同时，您根本无法添加没有航班的机场... :(

var query = from a in airports 
    join g in flights on a.AirportId equals g.FromAirportId
    where g.FlightDate.Year == 2020
    group g by g.FromAirportId into q
    orderby q.Count(), q.Key
    select new { AirportId = q.Key, 
                 AirportName = airports.Find(x=>x.AirportId == q.Key).AirportName, 
                 FlightCount = q.Count() };
c#

1 个回答

  1. Best Answer

    您可以不g.FromAirportId按机场分组a。然后你可以这样写你的 LINQ 查询：

    var query = from a in airports
            join g in flights on a.AirportId equals g.FromAirportId
            where g.FlightDate.Year == 2020
            group g by a into q
            orderby q.Count(), q.Key.AirportId
            select new
            {
                AirportId = q.Key.AirportId,
                AirportName = q.Key.AirportName,
                FlightCount = q.Count()
            };

    更新。结果要选择没有匹配出发的机场，您需要join替换为left join：

    ...
join f in flights on a.AirportId equals f.FromAirportId into gj
from g in gj.DefaultIfEmpty()
...

    在这种情况下，where必须去除该条件，否则我们仍然会丢弃没有离开分组样本的机场记录。那么过滤条件可以，比如放在Count(). 整个请求将如下所示：

    Func<Flight, bool> flightDateIn2020 = x => x?.FlightDate.Year == 2020;
// условие поиска вынес в предикат, чтобы не дублировать код в запросе в двух местах

// плохое решение из-за фильтрации в 2х местах!
// ниже есть исправленное
var query = from a in airports
            join f in flights on a.AirportId equals f.FromAirportId into gj
            from g in gj.DefaultIfEmpty()
            group g by a into q
            orderby q.Count(flightDateIn2020), q.Key.AirportId
            select new
            {
                AirportId = q.Key.AirportId,
                AirportName = q.Key.AirportName,
                FlightCount = q.Count(flightDateIn2020)
            };

    UPD 2（删除了谓词的使用）：两次 过滤flightDateIn2020仍然很糟糕，所以最好先用过滤进行选择，然后在不重新过滤的情况下对数据进行排序：

    // неотсортированный результат:
var query = from a in airports
            join f in flights on a.AirportId equals f.FromAirportId into gj
            from g in gj.DefaultIfEmpty()
            group g by a into q
            select new
            {
                AirportId = q.Key.AirportId,
                AirportName = q.Key.AirportName,
                FlightCount = (from c in q
                               where c?.FlightDate.Year == 2020
                               select c).Count()
            };

// отсортированный результат:
var orderedQuery = from a in query
                   orderby a.FlightCount, a.AirportId
                   select a;

    或者在一个请求中相同：

    var query = from x in
               (from a in airports
                join f in flights on a.AirportId equals f.FromAirportId into gj
                from g in gj.DefaultIfEmpty()
                group g by a into q
                select new
                {
                    AirportId = q.Key.AirportId,
                    AirportName = q.Key.AirportName,
                    FlightCount = (from c in q
                                   where c?.FlightDate.Year == 2020
                                   select c).Count()
                })
            orderby x.FlightCount, x.AirportId
            select x;
    • 2

相关问题