Linq使用dateFrom和dateTo连接两个实体

huangapple
go评论104阅读模式
英文:

Linq join 2 entities by dateFrom and dateTo

问题

我有以下实体：

  1. flights: [
  2.   {
  3.     ...
  4.     "flightId": 1,
  5.     "dateFrom": "2023-03-01",
  6.     "dateTo": "2023-03-01",
  7.     ...
  8.   },
  9.   {
  10.     ...
  11.     "flightId": 2,
  12.     "dateFrom": "2023-03-03",
  13.     "dateTo": "2023-03-03",
  14.     ...
  15.   }
  16. ]
  18. hotels: [
  19.   {
  20.     ...
  21.     "bookId": 1,
  22.     "dateFrom": "2023-03-01",
  23.     "dateTo": "2023-03-03",
  24.     ...
  25.   }
  26. ]

我的目标是得到类似以下的结果：

  1. [
  2.  {
  3.    date : "2023-03-01",
  4.    flights: [
  5.        {
  6.           "flightId": 1
  7.        }
  8.    ], 
  9.    hotels: [
  10.        {
  11.           "bookId": 1,
  12.        }
  13.    ]
  14.  },
  15.  {
  16.    date : "2023-03-02",
  17.    flights: [], 
  18.    hotels: [
  19.        {
  20.           "bookId": 1,
  21.        }
  22.    ]
  23.  },
  24.  {
  25.    date : "2023-03-03",
  26.    flights: [
  27.        {
  28.           "flightId": 2
  29.        }
  30.    ], 
  31.    hotels: [
  32.        {
  33.           "bookId": 1,
  34.        }
  35.    ]
  36.  }
  37. ]

请问我如何在 C# 中使用 LINQ 或其他方法将它们联接/分组，包括使用 dateFrom 和 dateTo，同时也包括那些日期不相等且介于其中的情况？

这样将显示每个日期上的航班/酒店信息。

我对 C#/.NET 相当新，请帮忙，谢谢。

英文:

Lets say I have these entities :

  1. flights: [
  2.   {
  3.     ...
  4.     "flightId": 1,
  5.     "dateFrom": "2023-03-01",
  6.     "dateTo": "2023-03-01",
  7.     ...
  8.   },
  9.   {
  10.     ...
  11.     "flightId": 2,
  12.     "dateFrom": "2023-03-03",
  13.     "dateTo": "2023-03-03",
  14.     ...
  15.   }
  16. ]
  18. hotels: [
  19.   {
  20.     ...
  21.     "bookId": 1,
  22.     "dateFrom": "2023-03-01",
  23.     "dateTo": "2023-03-03",
  24.     ...
  25.   }
  26. ]

And my goal is to have something like :

  1. [
  2.  {
  3.    date : "2023-03-01",
  4.    flights: [
  5.        {
  6.           "flightId": 1
  7.        }
  8.    ], 
  9.    hotels: [
  10.        {
  11.           "bookId": 1,
  12.        }
  13.    ]
  14.  },
  15.  {
  16.    date : "2023-03-02",
  17.    flights: [], 
  18.    hotels: [
  19.        {
  20.           "bookId": 1,
  21.        }
  22.    ]
  23.  },
  24.  {
  25.    date : "2023-03-03",
  26.    flights: [
  27.        {
  28.           "flightId": 2
  29.        }
  30.    ], 
  31.    hotels: [
  32.        {
  33.           "bookId": 1,
  34.        }
  35.    ]
  36.  }
  37. ]

May I know how I can join / group those 2 using LINQ or any method in C#, using both dateFrom & dateTo, including those that dates aren't equal and in between as well ?

So it will show every date with either flights / hotels on it.

I'm fairly new to c# / .net so any help is appreciated. Thank you.

Edit : Here's my table

  1. ------User------
  2. | Id | Name    
  3. ----------------
  4. | 1  | John Doe
  5. ----------------
  7. Flight---------------------------------
  8. | Id | DateFrom   | DateTo     | UserId
  9. ---------------------------------------
  10. | 1  | 2023-03-01 | 2023-03-01 | 1
  11. ---------------------------------------
  12. | 2  | 2023-03-03 | 2023-03-03 | 1
  15. Hotels---------------------------------
  16. | Id | DateFrom   | DateTo     | UserId
  17. ---------------------------------------
  18. | 1  | 2023-03-01 | 2023-03-03 | 1

答案1

得分: 1

这是使用MoreLINQ的FullJoin()扩展的解决方案：

  1. var flights = new[] {
  2.     new Flight(1, From: DateTime.Parse("2023-01-12"), To: DateTime.Parse("2023-01-15")),
  3.     new Flight(2, From: DateTime.Parse("2023-01-14"), To: DateTime.Parse("2023-01-17"))
  4. };
  5. var hotels = new[] {
  6.     new Hotel(3, From: DateTime.Parse("2023-01-10"), To: DateTime.Parse("2023-01-13")),
  7.     new Hotel(4, From: DateTime.Parse("2023-01-11"), To: DateTime.Parse("2023-01-14"))
  8. };
  10. var flightsByDay = flights
  11.     .SelectMany(f => Days(f.From, f.To), (flight, day) => (flight, day))
  12.     .GroupBy(f => f.day, f => f.flight);
  14. var hotelsByDay = hotels
  15.     .SelectMany(h => Days(h.From, h.To), (hotel, day) => (hotel, day))
  16.     .GroupBy(h => h.day, h => h.hotel);
  17.     
  18. var result = flightsByDay
  19.     .FullJoin(
  20.         hotelsByDay,
  21.         f => f.Key,
  22.         h => h.Key,
  23.         fs => (day: fs.Key, flights: fs.ToArray(), hotels: Array.Empty<Hotel>()),
  24.         hs => (day: hs.Key, flights: Array.Empty<Flight>(), hotels: hs.ToArray()),
  25.         (fs, hs) => (day: fs.Key, flights: fs.ToArray(), hotels: hs.ToArray()))
  26.     .OrderBy(x => x.day);
  28. static IEnumerable<DateTime> Days(DateTime from, DateTime to) => Enumerable
  29.     .Range(0, 1 + to.Subtract(from).Days)
  30.     .Select(offset => from.AddDays(offset));
  32. public record Flight(int Id, DateTime From, DateTime To);
  33. public record Hotel(int Id, DateTime From, DateTime To);

如果你在LINQPad中运行result.Dump()，你会看到以下结果：

Linq使用dateFrom和dateTo连接两个实体

英文:

Here's a solution using MoreLINQ's FullJoin() extension:

  1. var flights = new[] {
  2.     new Flight(1, From: DateTime.Parse(&quot;2023-01-12&quot;), To: DateTime.Parse(&quot;2023-01-15&quot;)),
  3.     new Flight(2, From: DateTime.Parse(&quot;2023-01-14&quot;), To: DateTime.Parse(&quot;2023-01-17&quot;))
  4. };
  5. var hotels = new[] {
  6.     new Hotel(3, From: DateTime.Parse(&quot;2023-01-10&quot;), To: DateTime.Parse(&quot;2023-01-13&quot;)),
  7.     new Hotel(4, From: DateTime.Parse(&quot;2023-01-11&quot;), To: DateTime.Parse(&quot;2023-01-14&quot;))
  8. };
  10. var flightsByDay = flights
  11.     .SelectMany(f =&gt; Days(f.From, f.To), (flight, day) =&gt; (flight, day))
  12.     .GroupBy(f =&gt; f.day, f =&gt; f.flight);
  14. var hotelsByDay = hotels
  15.     .SelectMany(h =&gt; Days(h.From, h.To), (hotel, day) =&gt; (hotel, day))
  16.     .GroupBy(h =&gt; h.day, h =&gt; h.hotel);
  17.     
  18. var result = flightsByDay
  19.     .FullJoin(
  20.         hotelsByDay,
  21.         f =&gt; f.Key,
  22.         h =&gt; h.Key,
  23.         fs =&gt; (day: fs.Key, flights: fs.ToArray(), hotels: Array.Empty&lt;Hotel&gt;()),
  24.         hs =&gt; (day: hs.Key, flights: Array.Empty&lt;Flight&gt;(), hotels: hs.ToArray()),
  25.         (fs, hs) =&gt; (day: fs.Key, flights: fs.ToArray(), hotels: hs.ToArray()))
  26.     .OrderBy(x =&gt; x.day);
  28. static IEnumerable&lt;DateTime&gt; Days(DateTime from, DateTime to) =&gt; Enumerable
  29.     .Range(0, 1 + to.Subtract(from).Days)
  30.     .Select(offset =&gt; from.AddDays(offset));
  32. public record Flight(int Id, DateTime From, DateTime To);
  33. public record Hotel(int Id, DateTime From, DateTime To);

If you got LINQPad this is what you'll see for result.Dump():

Linq使用dateFrom和dateTo连接两个实体

huangapple
  • 本文由 发表于 2023年3月1日 16:03:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/75600935.html
  • .net
  • c#
  • linq
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定