efcore: Slow query with multiple include when upgrade from Microsoft.EntityFrameworkCore.SqlServer 2.0.0 to 7.0.3

Hello,

I’m upgrading my project from Microsoft.EntityFrameworkCore.SqlServer 2.0.0 with netcoreapp2.0 to 7.0.0 with net7.0

My code benchmark:

For 2.0.0:

    [MemoryDiagnoser]
    public class EF2BenchMark
    {
        [Benchmark]
        public async Task RunAsync()
        {
            using (var context = new Context(""))
            {
                var query = context.Customers.Where(x => !x.Deleted.HasValue);
                query = query.Where(x => x.PrimaryEmail == "abc@gmail.com");
                query = query
                .Include(x => x.Permission)
                .Include(x => x.UserIds);

                query = query
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.Address)
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.Index)
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.CustomerValue);

                query = query.Include(x => x.ClassProfile).ThenInclude(x => x.Bags)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubShaftLength)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubShaftFlex)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubCategoryType)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.FaceLoftAdjustment)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.FaceLieAdjustment)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubLoft)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubCategory)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.Brand)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.Model);

                query = query.Include(x => x.ClassProfile.TimePlayingGolf)
                    .Include(x => x.ClassProfile.NewHandicap)
                    .Include(x => x.ClassProfile.CurrentIronLength)
                    .Include(x => x.ClassProfile.CurrentDriverFlex)
                    .Include(x => x.ClassProfile.DriverLoft)
                    .Include(x => x.ClassProfile.CurrentIronLie)
                    .Include(x => x.ClassProfile.DesiredDriverFlex)
                    .Include(x => x.ClassProfile.ClubHand);

                var customer = await query.FirstOrDefaultAsync();
            }
        }
    }

Result:

For 7.0.3:

    [MemoryDiagnoser]
    public class EF7BenchMark
    {
        [Benchmark]
        public async Task RunAsync()
        {
            var builder = new DbContextOptionsBuilder();
            builder.UseModel(ApplicationDbContextModel.Instance);
            builder.EnableSensitiveDataLogging();
            builder.UseSqlServer("")
            .UseLoggerFactory(new LoggerFactory(new[] {
                    new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider()
            }));
            using (var context = new ApplicationDbContext(builder.Options))
            {
                var query = context.Customers.Where(x => !x.Deleted.HasValue);
                query = query.Where(x => x.PrimaryEmail == "abc@gmail.com");
                query = query
                .Include(x => x.Permission)
                .Include(x => x.UserIds);

               query = query
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.Address)
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.Index)
                    .Include(x => x.CustomerProfiles).ThenInclude(x => x.CustomerValue);

                query = query.Include(x => x.ClassProfile).ThenInclude(x => x.Bags)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubShaftLength)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubShaftFlex)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubCategoryType)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.FaceLoftAdjustment)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.FaceLieAdjustment)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubLoft)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.ClubCategory)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.Brand)
                    .Include(x => x.ClassProfile.Bags)
                    .ThenInclude(x => x.Model);

                query = query.Include(x => x.ClassProfile.TimePlayingGolf)
                    .Include(x => x.ClassProfile.NewHandicap)
                    .Include(x => x.ClassProfile.CurrentIronLength)
                    .Include(x => x.ClassProfile.CurrentDriverFlex)
                    .Include(x => x.ClassProfile.DriverLoft)
                    .Include(x => x.ClassProfile.CurrentIronLie)
                    .Include(x => x.ClassProfile.DesiredDriverFlex)
                    .Include(x => x.ClassProfile.ClubHand);

                    var customer = await query.AsSplitQuery().AsNoTrackingWithIdentityResolution().FirstOrDefaultAsync();
            }
        }
    }

Result:

I attach benchmark result in file: for 7.0.3 publish.v7.zip for 2.0.0 publish.v2.zip

The query built to SQL is the same when I use SQL Profiler. What did I do wrong with performance? It’s the same query. What can I do to improve performance for ef core 7.0.3