using Microsoft.EntityFrameworkCore.Query.Associations.Navigations;
using Microsoft.EntityFrameworkCore.TestUtilities;
using System.Threading.Tasks;
using Xunit;
using Xunit.Abstractions;

namespace EntityFrameworkCore.Jet.FunctionalTests.Query.Associations.Navigations;

public class NavigationsIncludeJetTest(NavigationsJetFixture fixture, ITestOutputHelper testOutputHelper)
    : NavigationsIncludeRelationalTestBase<NavigationsJetFixture>(fixture, testOutputHelper)
{
    public override async Task Include_required(bool async)
    {
        await base.Include_required(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_optional(bool async)
    {
        await base.Include_optional(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_collection(bool async)
    {
        await base.Include_collection(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_required_optional_and_collection(bool async)
    {
        await base.Include_required_optional_and_collection(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_nested(bool async)
    {
        await base.Include_nested(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_nested_optional(bool async)
    {
        await base.Include_nested_optional(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_nested_collection(bool async)
    {
        await base.Include_nested_collection(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_nested_collection_on_optional(bool async)
    {
        await base.Include_nested_collection_on_optional(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    public override async Task Include_nested_collection_on_collection(bool async)
    {
        await base.Include_nested_collection_on_collection(async);

        AssertSql(
            """
SELECT `r`.`Id`, `r`.`Name`, `r`.`OptionalAssociateId`, `r`.`RequiredAssociateId`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`CollectionRootId`, `s`.`Int`, `s`.`Ints`, `s`.`Name`, `s`.`OptionalNestedAssociateId`, `s`.`RequiredNestedAssociateId`, `s`.`String`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `s`.`CollectionAssociateId`, `s`.`Int0`, `s`.`Ints0`, `s`.`Name0`, `s`.`String0`, `s`.`CollectionAssociateId0`, `s`.`Int1`, `s`.`Ints1`, `s`.`Name1`, `s`.`String1`, `s`.`CollectionAssociateId1`, `s`.`Int2`, `s`.`Ints2`, `s`.`Name2`, `s`.`String2`, `a`.`CollectionRootId`, `a`.`Int`, `a`.`Ints`, `a`.`Name`, `a`.`OptionalNestedAssociateId`, `a`.`RequiredNestedAssociateId`, `a`.`String`, `n6`.`Id`, `n6`.`CollectionAssociateId`, `n6`.`Int`, `n6`.`Ints`, `n6`.`Name`, `n6`.`String`, `n`.`CollectionAssociateId`, `n`.`Int`, `n`.`Ints`, `n`.`Name`, `n`.`String`, `n0`.`CollectionAssociateId`, `n0`.`Int`, `n0`.`Ints`, `n0`.`Name`, `n0`.`String`, `a0`.`CollectionRootId`, `a0`.`Int`, `a0`.`Ints`, `a0`.`Name`, `a0`.`OptionalNestedAssociateId`, `a0`.`RequiredNestedAssociateId`, `a0`.`String`, `n7`.`Id`, `n7`.`CollectionAssociateId`, `n7`.`Int`, `n7`.`Ints`, `n7`.`Name`, `n7`.`String`, `n1`.`CollectionAssociateId`, `n1`.`Int`, `n1`.`Ints`, `n1`.`Name`, `n1`.`String`, `n2`.`CollectionAssociateId`, `n2`.`Int`, `n2`.`Ints`, `n2`.`Name`, `n2`.`String`
FROM ((((((((`RootEntity` AS `r`
LEFT JOIN `AssociateType` AS `a` ON `r`.`OptionalAssociateId` = `a`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n` ON `a`.`OptionalNestedAssociateId` = `n`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n0` ON `a`.`RequiredNestedAssociateId` = `n0`.`Id`)
INNER JOIN `AssociateType` AS `a0` ON `r`.`RequiredAssociateId` = `a0`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n1` ON `a0`.`OptionalNestedAssociateId` = `n1`.`Id`)
LEFT JOIN `NestedAssociateType` AS `n2` ON `a0`.`RequiredNestedAssociateId` = `n2`.`Id`)
LEFT JOIN (
    SELECT `a1`.`Id`, `a1`.`CollectionRootId`, `a1`.`Int`, `a1`.`Ints`, `a1`.`Name`, `a1`.`OptionalNestedAssociateId`, `a1`.`RequiredNestedAssociateId`, `a1`.`String`, `n3`.`Id` AS `Id0`, `n4`.`Id` AS `Id1`, `n5`.`Id` AS `Id2`, `n5`.`CollectionAssociateId`, `n5`.`Int` AS `Int0`, `n5`.`Ints` AS `Ints0`, `n5`.`Name` AS `Name0`, `n5`.`String` AS `String0`, `n3`.`CollectionAssociateId` AS `CollectionAssociateId0`, `n3`.`Int` AS `Int1`, `n3`.`Ints` AS `Ints1`, `n3`.`Name` AS `Name1`, `n3`.`String` AS `String1`, `n4`.`CollectionAssociateId` AS `CollectionAssociateId1`, `n4`.`Int` AS `Int2`, `n4`.`Ints` AS `Ints2`, `n4`.`Name` AS `Name2`, `n4`.`String` AS `String2`
    FROM ((`AssociateType` AS `a1`
    LEFT JOIN `NestedAssociateType` AS `n3` ON `a1`.`OptionalNestedAssociateId` = `n3`.`Id`)
    INNER JOIN `NestedAssociateType` AS `n4` ON `a1`.`RequiredNestedAssociateId` = `n4`.`Id`)
    LEFT JOIN `NestedAssociateType` AS `n5` ON `a1`.`Id` = `n5`.`CollectionAssociateId`
) AS `s` ON `r`.`Id` = `s`.`CollectionRootId`)
LEFT JOIN `NestedAssociateType` AS `n6` ON `a`.`Id` = `n6`.`CollectionAssociateId`)
LEFT JOIN `NestedAssociateType` AS `n7` ON `a0`.`Id` = `n7`.`CollectionAssociateId`
WHERE `a0`.`RequiredNestedAssociateId` IS NOT NULL AND `n2`.`Id` IS NOT NULL
ORDER BY `r`.`Id`, `a`.`Id`, `n`.`Id`, `n0`.`Id`, `a0`.`Id`, `n1`.`Id`, `n2`.`Id`, `s`.`Id`, `s`.`Id0`, `s`.`Id1`, `s`.`Id2`, `n6`.`Id`
""");
    }

    [ConditionalFact]
    public virtual void Check_all_tests_overridden()
        => TestHelpers.AssertAllMethodsOverridden(GetType());
}
