Filtered Navigation Property causes excessive Join for subsequent mapped properties

[Kf-MarianBejenari]

Description

When a navigation property with filter is used in mapping, EF generates a JOIN for each of the subsequent property mapped.

If such property is at start of the mapping like below

new
{
    y.Id,
    OriginalTitle = y.Book.Original.Title,
    y.Book.IsSelling,
    y.Book.Title,
    y.Book.Price,
}

It generates 3 LEFT OUTER JOINS with Book table (one for each property after navigation)

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[AuthorId] AS [AuthorId], 
    [Extent2].[Name] AS [Name], 
    [Element1].[Title] AS [Title], 
    [Extent5].[IsSelling] AS [IsSelling], 
    [Extent6].[Title] AS [Title1], 
    [Extent7].[Price] AS [Price]
    FROM      [dbo].[BookAuthors] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Authors] AS [Extent2] ON [Extent1].[AuthorId] = [Extent2].[Id]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent3].[Id] AS [Id], 
        [Extent3].[Title] AS [Title], 
        [Extent3].[IsSelling] AS [IsSelling], 
        [Extent4].[OriginalId] AS [OriginalId]
        FROM  [dbo].[Books] AS [Extent3]
        INNER JOIN [dbo].[Books] AS [Extent4] ON [Extent4].[OriginalId] = [Extent3].[Id]
        WHERE (([Extent4].[IsSelling] = 1) OR (@DynamicFilterParam_000001 IS NOT NULL)) AND ([Extent1].[BookId] = [Extent4].[Id]) AND (([Extent3].[IsSelling] = 1) OR (@DynamicFilterParam_000001 IS NOT NULL)) ) AS [Element1]
    LEFT OUTER JOIN [dbo].[Books] AS [Extent5] ON (([Extent5].[IsSelling] = 1) OR (@DynamicFilterParam_000001 IS NOT NULL)) AND ([Extent1].[BookId] = [Extent5].[Id])
    LEFT OUTER JOIN [dbo].[Books] AS [Extent6] ON (([Extent6].[IsSelling] = 1) OR (@DynamicFilterParam_000001 IS NOT NULL)) AND ([Extent1].[BookId] = [Extent6].[Id])
    LEFT OUTER JOIN [dbo].[Books] AS [Extent7] ON (([Extent7].[IsSelling] = 1) OR (@DynamicFilterParam_000001 IS NOT NULL)) AND ([Extent1].[BookId] = [Extent7].[Id])

but if we change the order to

new
{
    y.Id,
    y.Book.IsSelling,
    y.Book.Title,
    y.Book.Price,
    OriginalTitle = y.Book.Original.Title,
}

It generates only required JOINS

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[AuthorId] AS [AuthorId], 
    [Project1].[Name] AS [Name], 
    [Project1].[IsSelling] AS [IsSelling], 
    [Project1].[Title] AS [Title], 
    [Project1].[Price] AS [Price], 
    [Element1].[Title] AS [Title1]
    FROM   (SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[BookId] AS [BookId], 
        [Extent1].[AuthorId] AS [AuthorId], 
        [Extent2].[Name] AS [Name], 
        [Extent3].[Title] AS [Title], 
        [Extent3].[IsSelling] AS [IsSelling], 
        [Extent3].[Price] AS [Price]
        FROM   [dbo].[BookAuthors] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Authors] AS [Extent2] ON [Extent1].[AuthorId] = [Extent2].[Id]
        LEFT OUTER JOIN [dbo].[Books] AS [Extent3] ON ([Extent1].[BookId] = [Extent3].[Id]) AND (([Extent3].[IsSelling] = 1) ) ) AS [Project1]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent4].[Id] AS [Id], 
        [Extent4].[Title] AS [Title], 
        [Extent4].[IsSelling] AS [IsSelling], 
        [Extent5].[OriginalId] AS [OriginalId]
        FROM  [dbo].[Books] AS [Extent4]
        INNER JOIN [dbo].[Books] AS [Extent5] ON [Extent5].[OriginalId] = [Extent4].[Id]
        WHERE (([Extent5].[IsSelling] = 1) ) AND ([Project1].[BookId] = [Extent5].[Id]) AND (([Extent4].[IsSelling] = 1) ) ) AS [Element1]

Fiddle or Project

https://dotnetfiddle.net/YJ0uds

Further technical details

• EF version: Both 6.13 and 6.20
• EF DynamicFilters version: Tested on 2.10 and 3.01
• Database Provider: SQL SERVER

[JonathanMagnan]

Hello @Kf-MarianBejenari ,

Thank you for reporting, we will look at your request.

Best Regards,

Jonathan

---

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

[JonathanMagnan]

Hello @Kf-MarianBejenari ,

We looked at it and unfortunately, we didn't found how to fix it.

Entity Framework generate those queries but it might also be caused by some code that is not optimized on our side.

After spending some hours on this, we will need to abandon as it looks to be an issue currently too depth to be able to easily fix it.

[cosminvlad]

@JonathanMagnan I am hitting the same problem with a query adding a CROSS APPLY for each navigation property. Could you take a fresh look at this issue or provide your more detailed view of the issue, so I can attempt investigating?

[JonathanMagnan]

Hello @cosminvlad ,

If I remember correctly, Entity Framework was generating an expression of around 50,000 lines of LINQ expression. Those lines are generally translated in some simple join but in Effort, every of those LINQ method was called which become a very huge performance issue.