After upgrading from EF 6.0.1 to EF 6.0.2: broken query generation when using aggregate

[farlop]

Having some code that generates this query in EF 6.0.1:

 SELECT [t].[Code] AS [CustomerCode], [t].[Description] AS [CustomerDescription], [o].[Year], [o].[WeekNumber], [o].[FamilyCode], MAX([t0].[ArrivalDate]) AS [MaxArrivalDate]
      FROM [OrderRequirements] AS [o]
      INNER JOIN (
          SELECT [c].[Id], [c].[Code], [c].[Description]
          FROM [Customers] AS [c]
          WHERE [c].[Enabled] = CAST(1 AS bit)
      ) AS [t] ON [o].[CustomerId] = [t].[Id]
      INNER JOIN (
          SELECT [o0].[ArrivalDate], [o0].[OrderRequirementId]
          FROM [OrderRequirementDetails] AS [o0]
          INNER JOIN (
              SELECT [d].[Id], [d].[Enabled]
              FROM [Depots] AS [d]
              WHERE [d].[Enabled] = CAST(1 AS bit)
          ) AS [t1] ON [o0].[DepotId] = [t1].[Id]
          WHERE [t1].[Enabled] = CAST(1 AS bit)
      ) AS [t0] ON [o].[Id] = [t0].[OrderRequirementId]
      WHERE ((([o].[Enabled] = CAST(1 AS bit)) AND [t].[Code] IN (N'10', N'12')) AND [o].[PublishedAt] IS NOT NULL) AND EXISTS (
          SELECT 1
          FROM [dbo].[fGrowerAssignments](NULL, NULL, NULL, NULL, NULL, @__published_7) AS [f]
          WHERE ([f].[GrowerCode] = @__query_GrowerCode_8) AND (((([f].[CustomerCode] = [t].[Code]) AND ([f].[Year] = [o].[Year])) AND ([f].[WeekNumber] = [o].[WeekNumber])) AND (([f].[FamilyCode] = [o].[FamilyCode]) OR ([f].[FamilyCode] IS NULL AND [o].[FamilyCode] IS NULL))))
      GROUP BY [t].[Code], [t].[Description], [o].[Year], [o].[WeekNumber], [o].[FamilyCode]
      ORDER BY MAX([t0].[ArrivalDate]) DESC, [t].[Description], [o].[FamilyCode]
      OFFSET @__p_9 ROWS FETCH NEXT @__p_10 ROWS ONLY

After upgrading to EF 6.0.2, the query generated is broken when trying to get MAX([t0].[ArrivalDate]) AS [MaxArrivalDate], adding a new subquery that also is broken and fails as wrong table identifiers (f3) is used in every [fGrowerAssignments] use:

 SELECT [t].[Code] AS [CustomerCode], [t].[Description] AS [CustomerDescription], [o].[Year], [o].[WeekNumber], [o].[FamilyCode], (
-- BEGIN WRONG SUBQUERY FOR 'MAXARRIVALDATE' AGGREGATE
          SELECT MAX([t4].[ArrivalDate])
          FROM [OrderRequirements] AS [o2]
          INNER JOIN (
              SELECT [c1].[Id], [c1].[ArrivalsEmailNotification], [c1].[Code], [c1].[CreateLoadingOrderClosed], [c1].[CustomsManagement], [c1].[Description], [c1].[Enabled], [c1].[ProgramByFamily], [c1].[ProgramDuration], [c1].[ProgramStartDay], [c1].[UpdatedAt], [c1].[UpdatedBy]
              FROM [Customers] AS [c1]
              WHERE [c1].[Enabled] = CAST(1 AS bit)
          ) AS [t3] ON [o2].[CustomerId] = [t3].[Id]
          INNER JOIN (
              SELECT [o3].[ArrivalDate], [o3].[Id], [t5].[Id] AS [Id0], [o3].[OrderRequirementId]
              FROM [OrderRequirementDetails] AS [o3]
              INNER JOIN (
                  SELECT [d0].[Id], [d0].[Code], [d0].[DepotType], [d0].[Description], [d0].[Enabled], [d0].[TransitDays], [d0].[UnloadingOrder], [d0].[UpdatedAt], [d0].[UpdatedBy]
                  FROM [Depots] AS [d0]
                  WHERE [d0].[Enabled] = CAST(1 AS bit)
              ) AS [t5] ON [o3].[DepotId] = [t5].[Id]
              WHERE [t5].[Enabled] = CAST(1 AS bit)
          ) AS [t4] ON [o2].[Id] = [t4].[OrderRequirementId]
          WHERE (((([o2].[Enabled] = CAST(1 AS bit)) AND [t3].[Code] IN (N'10', N'12')) AND ([o2].[PublishedAt] IS NOT NULL)) AND EXISTS (
              SELECT 1
              FROM [dbo].[fGrowerAssignments](NULL, NULL, NULL, NULL, NULL, @__published_7) AS [f3]
              WHERE ([f2].[GrowerCode] = @__query_GrowerCode_8) AND (((([f2].[CustomerCode] = [t3].[Code]) AND ([f2].[Year] = [o2].[Year])) AND ([f2].[WeekNumber] = [o2].[WeekNumber])) AND (([f2].[FamilyCode] = [o2].[FamilyCode]) OR (([f2].[FamilyCode] IS NULL) AND ([o2].[FamilyCode] IS NULL)))))) AND ((((([t].[Code] = [t3].[Code]) AND ([t].[Description] = [t3].[Description])) AND ([o].[Year] = [o2].[Year])) AND ([o].[WeekNumber] = [o2].[WeekNumber])) AND (([o].[FamilyCode] = [o2].[FamilyCode]) OR (([o].[FamilyCode] IS NULL) AND ([o2].[FamilyCode] IS NULL))))) AS [MaxArrivalDate]
-- END
      FROM [OrderRequirements] AS [o]
      INNER JOIN (
          SELECT [c].[Id], [c].[Code], [c].[Description]
          FROM [Customers] AS [c]
          WHERE [c].[Enabled] = CAST(1 AS bit)
      ) AS [t] ON [o].[CustomerId] = [t].[Id]
      WHERE ((([o].[Enabled] = CAST(1 AS bit)) AND [t].[Code] IN (N'10', N'12')) AND ([o].[PublishedAt] IS NOT NULL)) AND EXISTS (
          SELECT 1
          FROM [dbo].[fGrowerAssignments](NULL, NULL, NULL, NULL, NULL, @__published_7) AS [f3] -- ERROR: named [f3], but used as [f] in line below
          WHERE ([f].[GrowerCode] = @__query_GrowerCode_8) AND (((([f].[CustomerCode] = [t].[Code]) AND ([f].[Year] = [o].[Year])) AND ([f].[WeekNumber] = [o].[WeekNumber])) AND (([f].[FamilyCode] = [o].[FamilyCode]) OR (([f].[FamilyCode] IS NULL) AND ([o].[FamilyCode] IS NULL)))))
      GROUP BY [t].[Code], [t].[Description], [o].[Year], [o].[WeekNumber], [o].[FamilyCode]
      ORDER BY (
-- WRONG ORDER BY MaxArrivalDate
          SELECT MAX([t4].[ArrivalDate])
          FROM [OrderRequirements] AS [o2]
          INNER JOIN (
              SELECT [c1].[Id], [c1].[ArrivalsEmailNotification], [c1].[Code], [c1].[CreateLoadingOrderClosed], [c1].[CustomsManagement], [c1].[Description], [c1].[Enabled], [c1].[ProgramByFamily], [c1].[ProgramDuration], [c1].[ProgramStartDay], [c1].[UpdatedAt], [c1].[UpdatedBy]
              FROM [Customers] AS [c1]
              WHERE [c1].[Enabled] = CAST(1 AS bit)
          ) AS [t3] ON [o2].[CustomerId] = [t3].[Id]
          INNER JOIN (
              SELECT [o3].[ArrivalDate], [o3].[Id], [t5].[Id] AS [Id0], [o3].[OrderRequirementId]
              FROM [OrderRequirementDetails] AS [o3]
              INNER JOIN (
                  SELECT [d0].[Id], [d0].[Code], [d0].[DepotType], [d0].[Description], [d0].[Enabled], [d0].[TransitDays], [d0].[UnloadingOrder], [d0].[UpdatedAt], [d0].[UpdatedBy]
                  FROM [Depots] AS [d0]
                  WHERE [d0].[Enabled] = CAST(1 AS bit)
              ) AS [t5] ON [o3].[DepotId] = [t5].[Id]
              WHERE [t5].[Enabled] = CAST(1 AS bit)
          ) AS [t4] ON [o2].[Id] = [t4].[OrderRequirementId]
          WHERE (((([o2].[Enabled] = CAST(1 AS bit)) AND [t3].[Code] IN (N'10', N'12')) AND ([o2].[PublishedAt] IS NOT NULL)) AND EXISTS (
              SELECT 1
              FROM [dbo].[fGrowerAssignments](NULL, NULL, NULL, NULL, NULL, @__published_7) AS [f3] -- ERROR: named [f3], but used as [f2] in line below
              WHERE ([f2].[GrowerCode] = @__query_GrowerCode_8) AND (((([f2].[CustomerCode] = [t3].[Code]) AND ([f2].[Year] = [o2].[Year])) AND ([f2].[WeekNumber] = [o2].[WeekNumber])) AND (([f2].[FamilyCode] = [o2].[FamilyCode]) OR (([f2].[FamilyCode] IS NULL) AND ([o2].[FamilyCode] IS NULL)))))) AND ((((([t].[Code] = [t3].[Code]) AND ([t].[Description] = [t3].[Description])) AND ([o].[Year] = [o2].[Year])) AND ([o].[WeekNumber] = [o2].[WeekNumber])) AND (([o].[FamilyCode] = [o2].[FamilyCode]) OR (([o].[FamilyCode] IS NULL) AND ([o2].[FamilyCode] IS NULL))))) DESC, [t].[Description], [o].[FamilyCode]
      OFFSET @__p_9 ROWS FETCH NEXT @__p_10 ROWS ONLY

This is failing with message:

 Microsoft.Data.SqlClient.SqlException (0x80131904): El identificador formado por varias partes 'f.GrowerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f.CustomerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f.Year' no se pudo enlazar.
      El identificador formado por varias partes 'f.WeekNumber' no se pudo enlazar.
      El identificador formado por varias partes 'f.FamilyCode' no se pudo enlazar.
      El identificador formado por varias partes 'f.FamilyCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.GrowerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.CustomerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.Year' no se pudo enlazar.
      El identificador formado por varias partes 'f2.WeekNumber' no se pudo enlazar.
      El identificador formado por varias partes 'f2.FamilyCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.FamilyCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.GrowerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.CustomerCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.Year' no se pudo enlazar.
      El identificador formado por varias partes 'f2.WeekNumber' no se pudo enlazar.
      El identificador formado por varias partes 'f2.FamilyCode' no se pudo enlazar.
      El identificador formado por varias partes 'f2.FamilyCode' no se pudo enlazar.

Here is more information about Model:

public class OrderRequirementConfiguration : IEntityTypeConfiguration<OrderRequirement>
{
    public void Configure(EntityTypeBuilder<OrderRequirement> builder)
    {
        ...
        builder.HasOne(x => x.Customer).WithMany().OnDelete(DeleteBehavior.Restrict);

        builder.HasMany(x => x.OrderRequirementDetails).WithOne(x => x.OrderRequirement).OnDelete(DeleteBehavior.Cascade);
        ...
    }
}

public class OrderRequirementDetailConfiguration : IEntityTypeConfiguration<OrderRequirementDetail>
{
    public void Configure(EntityTypeBuilder<OrderRequirementDetail> builder)
    {
        builder.ToTable("OrderRequirementDetails");
        ...
        builder.HasOne(x => x.Depot).WithMany().OnDelete(DeleteBehavior.Restrict);

        builder.HasOne(x => x.OrderRequirement).WithMany(x => x.OrderRequirementDetails).OnDelete(DeleteBehavior.Cascade);

        builder.HasQueryFilter(x => x.Depot.Enabled);
        ...
    }
}

public class GrowerAssignmentViewConfiguration : IEntityTypeConfiguration<GrowerAssignmentView>
{
    public void Configure(EntityTypeBuilder<GrowerAssignmentView> builder)
    {
        builder.HasNoKey();
        ...
    }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ....
        modelBuilder.ApplyConfiguration(new OrderRequirementConfiguration());
        modelBuilder.ApplyConfiguration(new OrderRequirementDetailConfiguration());
        modelBuilder.ApplyConfiguration(new GrowerAssignmentViewConfiguration());

        modelBuilder.HasDbFunction(() => GrowerAssignmentsView(default, default, default, default, default, default))
            .HasName("fGrowerAssignments");
        ....
    }

EF Core version: 6.0.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.2 Preview 1

[farlop]

Just noted that it also fails on EF Core 6.0.2. Will update issue

[farlop]

Reviewing changes in EF 6.0.2, I suspect the issue #26592 could be related

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[farlop]

I created a repro repository here: https://github.com/farlop/EFCore_Issue27621

Just running the project will launch the issue. Downgrading to EF 6.0.1 will run the query correctly.

Please let me know if the repro is good enough, or if you need more work on it.

[maumar]

problem is caused by #27102

@farlop you can work around the problem by using quirk (I tested it on the sample you provided and it does work):

AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue27102", true);

[maumar]

@farlop alternatively, as workaround you can break the query into two parts - fetch the relevant OrderRequirement ids in first query (using Any, which is the problematic part when combined with group by) and then perform grouping based on the ids in the second one, like so:

        var gas = db.GrowerAssignmentsView(null, null, null, null).Where(x => x.GrowerCode == "GRW");
        var orIds = await db.OrderRequirements
            .Where(x => gas.Any(y => y.CustomerCode == x.Customer.Code && y.Year == x.Year && y.WeekNumber == x.WeekNumber && y.FamilyCode == x.FamilyCode))
            .Select(x => x.Id)
            .ToListAsync();

        var workaround = await db.OrderRequirements
            .Where(x => orIds.Contains(x.Id))
            .GroupBy(x => new { CustomerCode = x.Customer.Code, CustomerDescription = x.Customer.Description, x.Year, x.WeekNumber, x.FamilyCode },
                (x, gr) => new { x.CustomerCode, x.CustomerDescription, x.Year, x.WeekNumber, x.FamilyCode, MaxArrivalDate = gr.SelectMany(o => o.OrderRequirementDetails.Select(d => d.ArrivalDate)).Max() })
            .ToListAsync();

[maumar]

dupe of #27433

[farlop]

Thank you @maumar for the workarounds. For now we will stick with version 6.0.1 or will apply the recommended Switch, as the query is far more complex that what's in the repro.