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]
FROM [dbo].[Makes] AS [m]
INNER 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]
