Using '.AsSplitQuery()' doubles results

Question

I'm doing some tests to improve performance for a tool I'm working on, which generates an Excel report. Data is being loaded with ef6 in a net6.0 project. There are many dependencies configured in the data context, using navigation properties. As you will see below, the query times are huge, but that's not what I'd like to ask about here. Before starting to discuss optimizations in the DB with the DB admin, I'd like to have my code as performant as possible.

Logs when not specifying QuerySplittingBehavior:

2022-03-31 09:21:37.9156|INFO|Products.IdmpExporter.Tests.Integration.ManagerTests.ExcelExportManagerTests|Starting test GetByAuthorizationHolderNrAsync_OK_Test with authorizationHolderNr=301; language=German|
2022-03-31 09:21:39.3873|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Start generating excel export by 168 product numbers |
2022-03-31 09:21:39.5490|WARN|Microsoft.EntityFrameworkCore.Query|Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'.|
2022-03-31 09:30:45.8627|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Retrieved 168 prodcuts in 546.4741356s|
2022-03-31 09:30:46.0707|WARN|Microsoft.EntityFrameworkCore.Query|Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'.|
2022-03-31 09:31:01.2852|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Aggregated additional prodcut information in 15.4218421s|
2022-03-31 09:31:01.8898|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Generated excel report in 0,6045199s|
2022-03-31 09:31:01.8898|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Excel report has been generated|

It (correctly) returns 168 products in 546s.

Logs when specifying .AsSplitQuery() (as suggested in the warning above):

2022-03-31 09:46:00.3521|INFO|Products.IdmpExporter.Tests.Integration.ManagerTests.ExcelExportManagerTests|Starting test GetByAuthorizationHolderNrAsync_OK_Test with authorizationHolderNr=301; language=German|
2022-03-31 09:46:01.6031|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Start generating excel export by 168 product numbers |
2022-03-31 09:48:37.3670|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Retrieved 333 prodcuts in 155.7634441s|
2022-03-31 09:48:56.2646|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Aggregated additional prodcut information in 18.8974323s|
2022-03-31 09:48:56.9089|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Generated excel report in 0,644222s|
2022-03-31 09:48:56.9089|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Excel report has been generated|

It returns 333 products in 155s. Almost all (why almost?) products are contained twice; both have the same PK value (of type int)

The code change leading to the second result is adding .AsSplitQuery() in the return statement of this repository method:

public async Task<ICollection<TModel>> GetAllAsync(Expression<Func<TModel, bool>> predicate, IEnumerable<string> includeProperties = null)
{
    var query = Set.Include(includeProperties);

    if (predicate != null)
        query = query.Where(predicate);

    return await query.AsSplitQuery().ToListAsync();
}

Shouldn't the results be exactly the same when using AsSplitQuery()? Respectively why can they be different?

[edit] Tried to fix this with specifying the ordering on the PK column of product but the results still are doubled.

    public override async Task<ICollection<Product>> GetAllAsync(Expression<Func<Product, bool>> predicate, IEnumerable<string> includeProperties = null)
    {
        var query = Set.Include(includeProperties);

        if (predicate != null)
            query = query.Where(predicate);

        return await query.AsSplitQuery()
                          .OrderBy(product => product.ProductNr)
                          .ToListAsync();
    }

[edit2] In case it is relevant, here's how the repository method is being called:

Each navigation property (separated by a dot) will lead to a JOIN when executing a single query:

    private readonly string[] _productPropertiesToInclude =
    {
        nameof(Product.Descriptions),
        $"{nameof(Product.ProductGroupCode)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.QuantityUnit)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Chainings)}.{nameof(ArticleChaining.ChainingCode)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Chainings)}.{nameof(ArticleChaining.ChainedArticle)}.{nameof(Article.Product)}",
        $"{nameof(Product.Components)}.{nameof(Component.GenericTree)}.{nameof(GenericTree.Descriptions)}",
        $"{nameof(Product.Components)}.{nameof(Component.Roas)}.{nameof(ComponentRoa.RoaCode)}.{nameof(Code.Descriptions)}"
    };

The predicate will lead to a clause WHERE ProductNr IN (...)

var products = await _productSupersetProductRepository.GetAllAsync(product => productNumbers.Contains(product.ProductNr), _productPropertiesToInclude);

[edit3] As asked in the comments I add the SQL executed by EF. As don't have a deep enough understanding of how EF should query when using split queries I'm not sure where to look for an issue. The WHERE clause looks good for all split queries; it seems like the object instantiation is at cause.

SingleQuery:

SELECT [v].[ProductNr], [v].[AtcCode], [v].[AuthorizationHolder], [v].[AuthorizationHolderNr], [v].[FiNr], [v].[Orphan], [v].[ItCode], [v].[PiNr], [v].[ProductGroupCodeType], [v].[ProductGroupCode], [v].[SwissmedicCode], [v].[SwissmedicNr], [v].[SwissmedicStrengthNr], 
[v0].[CodeType], [v0].[Code], [p].[ProductNr], [p].[Lang], [p].[Product_Descr], [p].[ProductLong_Descr], [p].[ProductShort_Descr], [p].[Swissmedic_Descr], [v1].[CodeType], [v1].[Code], [v1].[Lang], [v1].[Descr], [t0].[Pharmacode], [t0].[CategoryType], [t0].[CountingUnit], [t0].[NumberOfPieces], [t0].[PackDepth], [t0].[PackHeight], [t0].[PackWeight], [t0].[PackWidth], [t0].[ProductNr], [t0].[QuantityUnitCodeType], [t0].[QuantityUnitCodeValue], [t0].[MaxStorageTemp], [t0].[MinStorageTemp], [t0].[SwissMedicNr], [t0].[CodeType], [t0].[Code], [t0].[Pharmacode0], [t0].[Lang], [t0].[Pharmacode_Descr], [t0].[CodeType0], [t0].[Code0], [t0].[Lang0], [t0].[Descr], [t0].[Pharmacode1], [t0].[ChainedPharmacode], [t0].[ChainingCodeType], [t0].[ChainingCodeValue], [t0].[CodeType1], [t0].[Code1], [t0].[Pharmacode00], [t0].[ProductNr0], [t0].[CodeType00], [t0].[Code00], [t0].[Lang1], [t0].[Descr0], [t0].[CategoryType0], [t0].[CountingUnit0], [t0].[NumberOfPieces0], [t0].[PackDepth0], [t0].[PackHeight0], [t0].[PackWeight0], [t0].[PackWidth0], [t0].[ProductNr00], [t0].[QuantityUnitCodeType0], [t0].[QuantityUnitCodeValue0], [t0].[MaxStorageTemp0], [t0].[MinStorageTemp0], [t0].[SwissMedicNr0], [t0].[AtcCode], [t0].[AuthorizationHolder], [t0].[AuthorizationHolderNr], [t0].[FiNr], [t0].[Orphan], [t0].[ItCode], [t0].[PiNr], [t0].[ProductGroupCodeType], [t0].[ProductGroupCode], [t0].[SwissmedicCode], [t0].[SwissmedicNr00], [t0].[SwissmedicStrengthNr], [t1].[ProductNr], [t1].[ComponentNr], [t1].[GenericTreeNr], [t1].[PharmaDoseFormCode], [t1].[GenericTreeNr0], [t1].[Code], [t1].[GenericTreeNr1], [t1].[Lang], [t1].[Descr], [t1].[ProductNr0], [t1].[ComponentNr0], [t1].[RoaCodeValue], [t1].[RoaCodeType], [t1].[CodeType], [t1].[Code0], [t1].[CodeType0], [t1].[Code00], [t1].[Lang0], [t1].[Descr0]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
LEFT JOIN [Products_Descriptions] AS [p] ON [v].[ProductNr] = [p].[ProductNr]
LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v1] ON ([v0].[CodeType] = [v1].[CodeType]) AND ([v0].[Code] = [v1].[Code])
LEFT JOIN (
    SELECT [v2].[Pharmacode], [v2].[CategoryType], [v2].[CountingUnit], [v2].[NumberOfPieces], [v2].[PackDepth], [v2].[PackHeight], [v2].[PackWeight], [v2].[PackWidth], [v2].[ProductNr], [v2].[QuantityUnitCodeType], [v2].[QuantityUnitCodeValue], [v2].[MaxStorageTemp], [v2].[MinStorageTemp], [v2].[SwissMedicNr], [v3].[CodeType], [v3].[Code], [p0].[Pharmacode] AS [Pharmacode0], [p0].[Lang], [p0].[Pharmacode_Descr], [v4].[CodeType] AS [CodeType0], [v4].[Code] AS [Code0], [v4].[Lang] AS [Lang0], [v4].[Descr], [t].[Pharmacode] AS [Pharmacode1], [t].[ChainedPharmacode], [t].[ChainingCodeType], [t].[ChainingCodeValue], [t].[CodeType] AS [CodeType1], [t].[Code] AS [Code1], [t].[Pharmacode0] AS [Pharmacode00], [t].[ProductNr] AS [ProductNr0], [t].[CodeType0] AS [CodeType00], [t].[Code0] AS [Code00], [t].[Lang] AS [Lang1], [t].[Descr] AS [Descr0], [t].[CategoryType] AS [CategoryType0], [t].[CountingUnit] AS [CountingUnit0], [t].[NumberOfPieces] AS [NumberOfPieces0], [t].[PackDepth] AS [PackDepth0], [t].[PackHeight] AS [PackHeight0], [t].[PackWeight] AS [PackWeight0], [t].[PackWidth] AS [PackWidth0], [t].[ProductNr0] AS [ProductNr00], [t].[QuantityUnitCodeType] AS [QuantityUnitCodeType0], [t].[QuantityUnitCodeValue] AS [QuantityUnitCodeValue0], [t].[MaxStorageTemp] AS [MaxStorageTemp0], [t].[MinStorageTemp] AS [MinStorageTemp0], [t].[SwissMedicNr] AS [SwissMedicNr0], [t].[AtcCode], [t].[AuthorizationHolder], [t].[AuthorizationHolderNr], [t].[FiNr], [t].[Orphan], [t].[ItCode], [t].[PiNr], [t].[ProductGroupCodeType], [t].[ProductGroupCode], [t].[SwissmedicCode], [t].[SwissmedicNr0] AS [SwissmedicNr00], [t].[SwissmedicStrengthNr]
    FROM [v_IdmpExport_Article] AS [v2]
    LEFT JOIN [v_Code] AS [v3] ON ([v2].[QuantityUnitCodeType] = [v3].[CodeType]) AND ([v2].[QuantityUnitCodeValue] = [v3].[Code])
    LEFT JOIN [Pharmacodes_Descriptions] AS [p0] ON [v2].[Pharmacode] = [p0].[Pharmacode]
    LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v4] ON ([v3].[CodeType] = [v4].[CodeType]) AND ([v3].[Code] = [v4].[Code])
    LEFT JOIN (
        SELECT [v5].[Pharmacode], [v5].[ChainedPharmacode], [v5].[ChainingCodeType], [v5].[ChainingCodeValue], [v6].[CodeType], [v6].[Code], [v7].[Pharmacode] AS [Pharmacode0], [v8].[ProductNr], [v9].[CodeType] AS [CodeType0], [v9].[Code] AS [Code0], [v9].[Lang], [v9].[Descr], [v7].[CategoryType], [v7].[CountingUnit], [v7].[NumberOfPieces], [v7].[PackDepth], [v7].[PackHeight], [v7].[PackWeight], [v7].[PackWidth], [v7].[ProductNr] AS [ProductNr0], [v7].[QuantityUnitCodeType], [v7].[QuantityUnitCodeValue], [v7].[MaxStorageTemp], [v7].[MinStorageTemp], [v7].[SwissMedicNr], [v8].[AtcCode], [v8].[AuthorizationHolder], [v8].[AuthorizationHolderNr], [v8].[FiNr], [v8].[Orphan], [v8].[ItCode], [v8].[PiNr], [v8].[ProductGroupCodeType], [v8].[ProductGroupCode], [v8].[SwissmedicCode], [v8].[SwissmedicNr] AS [SwissmedicNr0], [v8].[SwissmedicStrengthNr]
        FROM [v_IdmpExport_ArticleChaining] AS [v5]
        LEFT JOIN [v_Code] AS [v6] ON ([v5].[ChainingCodeType] = [v6].[CodeType]) AND ([v5].[ChainingCodeValue] = [v6].[Code])
        INNER JOIN [v_IdmpExport_Article] AS [v7] ON [v5].[ChainedPharmacode] = [v7].[Pharmacode]
        INNER JOIN [v_IdmpExport_Product] AS [v8] ON [v7].[ProductNr] = [v8].[ProductNr]
        LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v9] ON ([v6].[CodeType] = [v9].[CodeType]) AND ([v6].[Code] = [v9].[Code])
    ) AS [t] ON [v2].[Pharmacode] = [t].[Pharmacode]
) AS [t0] ON [v].[ProductNr] = [t0].[ProductNr]
LEFT JOIN (
    SELECT [v10].[ProductNr], [v10].[ComponentNr], [v10].[GenericTreeNr], [v10].[PharmaDoseFormCode], [g].[GenericTreeNr] AS [GenericTreeNr0], [g].[Code], [g0].[GenericTreeNr] AS [GenericTreeNr1], [g0].[Lang], [g0].[Descr], [t2].[ProductNr] AS [ProductNr0], [t2].[ComponentNr] AS [ComponentNr0], [t2].[RoaCodeValue], [t2].[RoaCodeType], [t2].[CodeType], [t2].[Code] AS [Code0], [t2].[CodeType0], [t2].[Code0] AS [Code00], [t2].[Lang] AS [Lang0], [t2].[Descr] AS [Descr0]
    FROM [v_IdmpExport_Component] AS [v10]
    LEFT JOIN [GenericTree] AS [g] ON [v10].[GenericTreeNr] = [g].[GenericTreeNr]
    LEFT JOIN [GenericTree_Description] AS [g0] ON [g].[GenericTreeNr] = [g0].[GenericTreeNr]
    LEFT JOIN (
        SELECT [v11].[ProductNr], [v11].[ComponentNr], [v11].[RoaCodeValue], [v11].[RoaCodeType], [v12].[CodeType], [v12].[Code], [v13].[CodeType] AS [CodeType0], [v13].[Code] AS [Code0], [v13].[Lang], [v13].[Descr]
        FROM [v_IdmpExport_ComponentRoa] AS [v11]
        LEFT JOIN [v_Code] AS [v12] ON ([v11].[RoaCodeType] = [v12].[CodeType]) AND ([v11].[RoaCodeValue] = [v12].[Code])
        LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v13] ON ([v12].[CodeType] = [v13].[CodeType]) AND ([v12].[Code] = [v13].[Code])
    ) AS [t2] ON ([v10].[ProductNr] = [t2].[ProductNr]) AND ([v10].[ComponentNr] = [t2].[ComponentNr])
) AS [t1] ON [v].[ProductNr] = [t1].[ProductNr]
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code], [p].[ProductNr], [p].[Lang], [v1].[CodeType], [v1].[Code], [v1].[Lang], [t0].[Pharmacode], [t0].[CodeType], [t0].[Code], [t0].[Pharmacode0], [t0].[Lang], [t0].[CodeType0], [t0].[Code0], [t0].[Lang0], [t0].[Pharmacode1], [t0].[ChainedPharmacode], [t0].[CodeType1], [t0].[Code1], [t0].[Pharmacode00], [t0].[ProductNr0], [t0].[CodeType00], [t0].[Code00], [t0].[Lang1], [t1].[ProductNr], [t1].[ComponentNr], [t1].[GenericTreeNr0], [t1].[GenericTreeNr1], [t1].[Lang], [t1].[ProductNr0], [t1].[ComponentNr0], [t1].[RoaCodeValue], [t1].[CodeType], [t1].[Code0], [t1].[CodeType0], [t1].[Code00]

Split query (first 3 queries):

SELECT [v].[ProductNr], [v].[AtcCode], [v].[AuthorizationHolder], [v].[AuthorizationHolderNr], [v].[FiNr], [v].[Orphan], [v].[ItCode], [v].[PiNr], [v].[ProductGroupCodeType], [v].[ProductGroupCode], [v].[SwissmedicCode], [v].[SwissmedicNr], [v].[SwissmedicStrengthNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]

SELECT [p].[ProductNr], [p].[Lang], [p].[Product_Descr], [p].[ProductLong_Descr], [p].[ProductShort_Descr], [p].[Swissmedic_Descr], [v].[ProductNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
INNER JOIN [Products_Descriptions] AS [p] ON [v].[ProductNr] = [p].[ProductNr]
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]

SELECT [v1].[CodeType], [v1].[Code], [v1].[Lang], [v1].[Descr], [v].[ProductNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
INNER JOIN [v_IdmpExport_CodeDescriptions] AS [v1] ON ([v0].[CodeType] = [v1].[CodeType]) AND ([v0].[Code] = [v1].[Code])
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]