Linq join 2 entities by dateFrom and dateTo

Question

Lets say I have these entities :

flights: [
  {
    ...
    "flightId": 1,
    "dateFrom": "2023-03-01",
    "dateTo": "2023-03-01",
    ...
  },
  {
    ...
    "flightId": 2,
    "dateFrom": "2023-03-03",
    "dateTo": "2023-03-03",
    ...
  }
]

hotels: [
  {
    ...
    "bookId": 1,
    "dateFrom": "2023-03-01",
    "dateTo": "2023-03-03",
    ...
  }
]

And my goal is to have something like :

[
 {
   date : "2023-03-01",
   flights: [
       {
          "flightId": 1
       }
   ], 
   hotels: [
       {
          "bookId": 1,
       }
   ]
 },
 {
   date : "2023-03-02",
   flights: [], 
   hotels: [
       {
          "bookId": 1,
       }
   ]
 },
 {
   date : "2023-03-03",
   flights: [
       {
          "flightId": 2
       }
   ], 
   hotels: [
       {
          "bookId": 1,
       }
   ]
 }
]

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

------User------
| Id | Name    
----------------
| 1  | John Doe
----------------

Flight---------------------------------
| Id | DateFrom   | DateTo     | UserId
---------------------------------------
| 1  | 2023-03-01 | 2023-03-01 | 1
---------------------------------------
| 2  | 2023-03-03 | 2023-03-03 | 1


Hotels---------------------------------
| Id | DateFrom   | DateTo     | UserId
---------------------------------------
| 1  | 2023-03-01 | 2023-03-03 | 1

Answer 1

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

var flights = new[] {
    new Flight(1, From: DateTime.Parse("2023-01-12"), To: DateTime.Parse("2023-01-15")),
    new Flight(2, From: DateTime.Parse("2023-01-14"), To: DateTime.Parse("2023-01-17"))
};
var hotels = new[] {
    new Hotel(3, From: DateTime.Parse("2023-01-10"), To: DateTime.Parse("2023-01-13")),
    new Hotel(4, From: DateTime.Parse("2023-01-11"), To: DateTime.Parse("2023-01-14"))
};

var flightsByDay = flights
    .SelectMany(f => Days(f.From, f.To), (flight, day) => (flight, day))
    .GroupBy(f => f.day, f => f.flight);

var hotelsByDay = hotels
    .SelectMany(h => Days(h.From, h.To), (hotel, day) => (hotel, day))
    .GroupBy(h => h.day, h => h.hotel);
    
var result = flightsByDay
    .FullJoin(
        hotelsByDay,
        f => f.Key,
        h => h.Key,
        fs => (day: fs.Key, flights: fs.ToArray(), hotels: Array.Empty<Hotel>()),
        hs => (day: hs.Key, flights: Array.Empty<Flight>(), hotels: hs.ToArray()),
        (fs, hs) => (day: fs.Key, flights: fs.ToArray(), hotels: hs.ToArray()))
    .OrderBy(x => x.day);

static IEnumerable<DateTime> Days(DateTime from, DateTime to) => Enumerable
    .Range(0, 1 + to.Subtract(from).Days)
    .Select(offset => from.AddDays(offset));

public record Flight(int Id, DateTime From, DateTime To);
public record Hotel(int Id, DateTime From, DateTime To);

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