Язык программирования C#9 и платформа .NET5

  Assert.Equal(4, cars.Count);
  cars.ForEach(c =>
  {
  Assert.NotNull(c.MakeNavigation);
  Assert.NotNull(c.Orders.ToList[0].CustomerNavigation);
  });
 }

Метод ToQueryString возвращает только первый запрос, поэтому последующие запросы были получены с применением профилировщика SQL Server:

 SELECT [i].[Id], [i].[Color], [i].[IsDrivable], [i].[MakeId],

[i].[PetName], [i]. [TimeStamp], [m].[Id], [m].[Name], [m].[TimeStamp]

 FROM [dbo].[Inventory] AS [i]
 INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
 WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS (
 SELECT 1
  FROM [Dbo].[Orders] AS [o]
  INNER JOIN (
  SELECT [i0].[Id], [i0].[Color], [i0].[IsDrivable], [i0].[MakeId],

[i0].[PetName], [i0].[TimeStamp]

  FROM [dbo].[Inventory] AS [i0]
  WHERE [i0].[IsDrivable] = CAST(1 AS bit)
  ) AS [t] ON [o].[CarId] = [t].[Id]
  WHERE ([t].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o].[CarId]))
 ORDER BY [i].[Id], [m].[Id]

 SELECT [t0].[Id], [t0].[CarId], [t0].[CustomerId], [t0].[TimeStamp],

[t0].[Id1], [t0]. [TimeStamp1], [t0].[FirstName], [t0].[FullName],

  [t0].[LastName], [i].[Id], [m].[Id]
 FROM [dbo].[Inventory] AS [i]
 INNER JOIN [dbo].[Makes] AS [m] ON [i].[MakeId] = [m].[Id]
 INNER JOIN (
  SELECT [o].[Id], [o].[CarId], [o].[CustomerId], [o].[TimeStamp],

[c].[Id] AS [Id1], [c]. [TimeStamp] AS [TimeStamp1], [c].[FirstName],

  [c].[FullName], [c].[LastName]
  FROM [Dbo].[Orders] AS [o]
  INNER JOIN (
  SELECT [i0].[Id], [i0].[IsDrivable]
  FROM [dbo].[Inventory] AS [i0]
  WHERE [i0].[IsDrivable] = CAST(1 AS bit)
  ) AS [t] ON [o].[CarId] = [t].[Id]
  INNER JOIN [Dbo].[Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
  WHERE [t].[IsDrivable] = CAST(1 AS bit)
 ) AS [t0] ON [i].[Id] = [t0].[CarId]
 WHERE ([i].[IsDrivable] = CAST(1 AS bit)) AND EXISTS (
  SELECT 1
  FROM [Dbo].[Orders] AS [o0]
  INNER JOIN (
  SELECT [i1].[Id], [i1].[Color], [i1].[IsDrivable], [i1].[MakeId],

[i1].[PetName], [i1].[TimeStamp]

  FROM [dbo].[Inventory] AS [i1]
  WHERE [i1].[IsDrivable] = CAST(1 AS bit)
  ) AS [t1] ON [o0].[CarId] = [t1].[Id]
  WHERE ([t1].[IsDrivable] = CAST(1 AS bit)) AND ([i].[Id] = [o0].[CarId]))
 ORDER BY [i].[Id], [m].[Id]

Будете вы разделять свои запросы или нет, зависит от существующих бизнес-требований.

Фильтрация связанных данных

В версии EF Core 5 появилась возможность фильтрации при включении навигационных свойств типа коллекций. До выхода EF Core 5 единственным способом получения отфильтрованного списка для навигационного свойства типа коллекций было использование явной загрузки. Добавьте в MakeTests.cs следующий тест, который демонстрирует получение записей производителей, выпускающих автомобили желтого цвета:

 [Fact]
 public void ShouldGetAllMakesAndCarsThatAreYellow
 {
  var query = Context.Makes.IgnoreQueryFilters
   .Include(x => x.Cars.Where(x => x.Color == "Yellow"));
  var qs = query.ToQueryString;
  var makes = query.ToList;
  Assert.NotNull(makes);
  Assert.NotEmpty(makes);
  Assert.NotEmpty(makes.Where(x => x.Cars.Any));
  Assert.Empty(makes.First(m => m.Id == 1).Cars);
  Assert.Empty(makes.First(m => m.Id == 2).Cars);
  Assert.Empty(makes.First(m => m.Id == 3).Cars);
  Assert.Single(makes.First(m => m.Id == 4).Cars);
  Assert.Empty(makes.First(m => m.Id == 5).Cars);
 }

Ниже показан сгенерированный код SQL:

 SELECT [m].[Id], [m].[Name], [m].[TimeStamp], [t].[Id], [t].[Color],

[t].[IsDrivable], [t].[MakeId], [t].[PetName], [t].[TimeStamp]

 FROM [dbo].[Makes] AS [m]
 LEFT JOIN (
  SELECT [i].[Id], [i].[Color], [i].[IsDrivable], [i].[MakeId],

[i].[PetName], [i].[TimeStamp]

  FROM [dbo].[Inventory] AS [i]
  WHERE [i].[Color] = N'Yellow') AS [t] ON [m].[Id] = [t].[MakeId]
 ORDER BY [m].[Id], [t].[Id]

Изменение запроса на разделенный приводит к выдаче такого кода SQL (получен с использованием профилировщика SQL Server):

 SELECT [m].[Id], [m].[Name], [m].[TimeStamp]
 FROM [dbo].[Makes] AS [m]
 ORDER BY [m].[Id]
 SELECT [t].[Id], [t].[Color], [t].[IsDrivable], [t].[MakeId],

[t].[PetName], [t]. [TimeStamp], [m].[Id]