Efcore: Wrong code generation (regression in 3.1.0)

[AlexBAV]

I have a following table:

CREATE TABLE [dbo].[ABTesting](
    [AttemptId] [uniqueidentifier] NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Type] [int] NOT NULL,
    [DateStart] [datetime] NOT NULL,
    [DateFinish] [datetime] NULL,
    [Outcome] [int] NOT NULL,
    [Param1] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [AttemptId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And the following query:

```C#
var query = db.Abtesting.Where(a => a.Category == campaign);

var results = await query
.GroupBy(a => a.Type)
.Select(g => new { g.Key, starts = g.Count() })
.Join(
query
.Where(a => a.Outcome == (int)Outcome.Successful)
.GroupBy(a => a.Type)
.Select(g => new { g.Key, successes = g.Count() }),
o => o.Key,
i => i.Key,
(o, i) => new { o.Key, o.starts, i.successes })
.ToListAsync();

EF Core generates the following SQL:

```SQL
SELECT [t].[Type] AS [Key], [t].[c] AS [starts], [t0].[c] AS [successes]
FROM (
    SELECT [a].[Type], COUNT(*) AS [c]
    FROM [ABTesting] AS [a]
    WHERE [a].[Category] = @__campaign_0
    GROUP BY [a].[Type]
) AS [t]
INNER JOIN (
    SELECT [a0].[Type], [t].[c]    <--- This identifier is not correct, correct should be "COUNT(*) AS [c]"
    FROM [ABTesting] AS [a0]
    WHERE ([a0].[Category] = @__campaign_0) AND ([a0].[Outcome] = 1)
    GROUP BY [a0].[Type]
) AS [t0] ON [t].[Type] = [t0].[Type]

Which results in the following exception:

SqlException: The multi-part identifier "t.c" could not be bound.

Apparently the SQL request generator is broken. The same code used to work correctly in 3.0.1.

Further technical details

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 1909 (18363.476)
IDE: Visual Studio 2019 16.4

[smitpatel]

Possible incorrect match of projection replacing it.

[AlexBAV]

Any update on the issue?

Maybe you guys can offer a workaround (preferably without executing query on the client)?

[jaumemilian]

Any update on the issue? Is there a plan to have that fixed in EF Core 3?

[ajcvickers]

@jaumemilian It'as not currently planned for a 3.1 patch release, but that could change based on feedback.

@smitpatel Workarounds?

[AlexBAV]

Any update? Any workaround?

[AlexBAV]

Implemented a dirty workaround:

```c#
public sealed class QueryFixInterceptor : DbCommandInterceptor
{
private static AsyncLocal FixQuery = new AsyncLocal();

public static void FixNextQuery()
{
    FixQuery.Value = true;
}

public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
    if (FixQuery.Value)
    {
        FixQuery.Value = false;
        command.CommandText = command.CommandText.Replace("[a0].[Type], [t].[c]", "[a0].[Type], COUNT(*) AS [c]");
    }
    return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
}

}

In `Startup.cs` added 

```c#
services.AddDbContext<MyContext>(options=>options
   .UseSqlServer(...)
   .AddInterceptors(new QueryFixInterceptor())
);

And immediately before executing a faulting query, add

c# QueryFixInterceptor.FixQuery();

If you have several faulting queries (I don't), you can replace FixQuery boolean with an enumeration.

I know this is a very dirty hack, but until the bug in framework is fixed, this is the best I can think of.

[smitpatel]

You could also use TagWith API to put a comment on executing DbCommand and search for that in command text and do replace. That would avoid logic around AsyncLocal.

[AlexBAV]

You could also use TagWith API to put a comment on executing DbCommand and search for that in command text and do replace. That would avoid logic around AsyncLocal.

Nice catch! Thank you for the hint. I hate global solutions to local problems :)