ExecuteUpdate: Duplicate table alias in generated Update query

[indrajitjadeja]

EF Core query result in Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name

Issue #30358 might be similar, for which fix #30486 is merged to release/7.0 and also available in EF Core 8.0.0-preview.3.23174.2 NuGet version link

But ExecuteUpdate method is still generating duplicate alias in UPDATE statement ( issue is replicating in EF Core 8 preview 3 version and EF core 7.0.5 version)

Generated Update query:

      UPDATE [c]
      SET [c].[CreditId] = (
          SELECT TOP(1) [c].[Id]
          FROM [Credits] AS [c]
          WHERE [c].[Reference] = [c].[CreditReference])
      FROM [Contestations] AS [c]
      WHERE [c].[Id] = CAST(1 AS bigint)

Sample code

   public static void Main(string[] args)
    {  
        using var db = sp.GetRequiredService<DemoDbContext>();
        {
          var credits = db.Credits.ToList();
          try
          {
              var r = db.Contestations.Where(c => c.Id == 1)
                  .Select(c => new
                   {
                       contestation = c,
                       credit = db.Credits
                          .First(d => d.Reference == c.CreditReference)
                   })
                   .ExecuteUpdate(calls => calls.SetProperty(
                      c => c.contestation.CreditId,
                      c => c.credit.Id));
          }
          catch (Exception exception)
          {
             Console.WriteLine(exception);
          }            
        }
    }

public class Contestation
{
    public long Id { get; set; }

    public string Reference { get; set; }

    public string DeclarationReference { get; set; }

    public string CreditReference { get; set; }

    public long? CreditId { get; set; }

    public Credit? Credit { get; set; }

    public long? DeclarationId { get; set; }

    public Declaration? Declaration { get; set; }
}

public class Credit
{
    public long Id { get; set; }

    public string Reference { get; set; }

    public string DeclarationReference { get; set; }
    
    public long? DeclarationId { get; set; }

    public Declaration? Declaration { get; set; }
}

public class Declaration
{
    public long Id { get; set; }

    public string Reference { get; set; }
}

public class DemoDbContext : DbContext
{
    public DemoDbContext(DbContextOptions<DemoDbContext> options) : base(options)
    {
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
            {
              options.UseSqlServer(
                  "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=TESTDB2;Integrated Security=True;Connect Timeout=30");
              options.LogTo(Console.WriteLine);
            }
     }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Declaration>()
            .HasIndex(d => d.Reference)
            .IsUnique();
        modelBuilder.Entity<Credit>()
            .HasIndex(c => new {c.DeclarationId, c.DeclarationReference})
            .IsUnique();
        modelBuilder.Entity<Credit>()
            .HasIndex(c => c.Reference)
            .IsUnique();
        modelBuilder.Entity<Contestation>()
            .HasIndex(c => c.Reference)
            .IsUnique();
        modelBuilder.Entity<Contestation>()
            .HasIndex(c => new {c.DeclarationId, c.DeclarationReference})
            .IsUnique();
        modelBuilder.Entity<Contestation>()
            .HasIndex(c => new {c.CreditId, c.CreditReference})
            .IsUnique();
    }

    public virtual DbSet<Credit> Credits { get; set; }

    public virtual DbSet<Declaration> Declarations { get; set; }
    
    public virtual DbSet<Contestation> Contestations { get; set; }
}

Stack trace

dbug: 5/9/2023 01:44:29.859 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      UPDATE [c]
      SET [c].[CreditId] = (
          SELECT TOP(1) [c].[Id]
          FROM [Credits] AS [c]
          WHERE [c].[Reference] = [c].[CreditReference])
      FROM [Contestations] AS [c]
      WHERE [c].[Id] = CAST(1 AS bigint)
fail: 5/9/2023 01:44:29.950 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
      Failed executing DbCommand (89ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      UPDATE [c]
      SET [c].[CreditId] = (
          SELECT TOP(1) [c].[Id]
          FROM [Credits] AS [c]
          WHERE [c].[Reference] = [c].[CreditReference])
      FROM [Contestations] AS [c]
      WHERE [c].[Id] = CAST(1 AS bigint)
dbug: 5/9/2023 01:44:29.975 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection)
      Closing connection to database 'TESTDB2' on server '(localdb)\MSSQLLocalDB'.
dbug: 5/9/2023 01:44:29.977 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection)
      Closed connection to database 'TESTDB2' on server '(localdb)\MSSQLLocalDB' (2ms).
fail: 5/9/2023 01:44:30.032 RelationalEventId.ExecuteUpdateFailed[20507] (Microsoft.EntityFrameworkCore.Query)
      An exception occurred while executing an 'ExecuteUpdate' operation for context type 'ExecuteUpdateDemo.Data.DemoDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CreditReference'.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
         at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.<>c.<NonQueryResult>b__13_0(DbContext _, ValueTuple`3 state)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.NonQueryResult(RelationalQueryContext relationalQueryContext, RelationalCommandCache relationalCommandCache, Type contextType, CommandSource commandSource, Boolean threadSafetyChecksEnabled)
      ClientConnectionId:f155166f-8e71-4b71-a724-6216fb603547
      Error Number:207,State:1,Class:16
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CreditReference'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.<>c.<NonQueryResult>b__13_0(DbContext _, ValueTuple`3 state)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.NonQueryResult(RelationalQueryContext relationalQueryContext, RelationalCommandCache relationalCommandCache, Type contextType, CommandSource commandSource, Boolean threadSafetyChecksEnabled)
   at lambda_method6(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdate[TSource](IQueryable`1 source, Expression`1 setPropertyCalls)
   at ExecuteUpdateDemo.Program.ExecuteUpdateWithSelectNew2(DemoDbContext db) in C:\Users\ijadeja\source\repos\samples\efcore\OracleDbDemo-main - Copy\ExecuteUpdateDemo\Program.cs:line 66

provider and version information

EF Core version: 7.0.5 & 8.0.0-preview.3.23174.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11
IDE: (e.g. Visual Studio 2022 17.5.2)

[indrajitjadeja]

@ajcvickers Please look into this bug.

[roji]

Simplified repro:

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = ctx.Contestations
    .Select(c => new
    {
        Contestation = c,
        Credit = ctx.Credits.First(d => d.Reference == c.CreditReference)
    })
    .ExecuteUpdate(calls => calls.SetProperty(c => c.Contestation.CreditId, c => c.Credit.Id));

public class BlogContext : DbContext
{
    public virtual DbSet<Credit> Credits { get; set; }
    public virtual DbSet<Contestation> Contestations { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Contestation
{
    public long Id { get; set; }
    public string Reference { get; set; }
    public string CreditReference { get; set; }
    public long? CreditId { get; set; }
}

public class Credit
{
    public long Id { get; set; }
    public string Reference { get; set; }
}

SQL:

UPDATE [c]
SET [c].[CreditId] = (
    SELECT TOP(1) [c].[Id]
    FROM [Credits] AS [c]
    WHERE [c].[Reference] = [c].[CreditReference])
FROM [Contestations] AS [c]

@maumar are you the best person to take a look?

[indrajitjadeja]

@ajcvickers , @AndriySvyryd Any plan to fix this in next EF 7 patch release?

[roji]

@indrajitjadeja we have to first investigate this and understand what the fix would entail.

[maumar]

this is now fixed (this must have had the same root cause as one of the other issues around alias uniquification that we fixed) - repro provided by @roji now yields the following sql:

UPDATE [c]
SET [c].[CreditId] = (
    SELECT TOP(1) [c0].[Id]
    FROM [Credits] AS [c0]
    WHERE [c0].[Reference] = [c].[CreditReference] OR ([c0].[Reference] IS NULL AND [c].[CreditReference] IS NULL))
FROM [Contestations] AS [c]

[roji]

Duplicate of #31078

[Magnona]

we have the same issue for Select and OrderBy (x => x.NestedProperty.ToLower()) query, is any work around exists since we could not update EF to v7 because of spanner database