EntityFramework-Extensions Microsoft.Data.SqlClient.SqlException: 'The column 'X' was specified multiple times for 'B'.'

When using UpdateFromQuery in combination with TPH inheritance and owned types and a required rowversion property this exception is thrown:

Microsoft.Data.SqlClient.SqlException: 'The column 'RowVersion' was specified multiple times for 'B'.'

Details

Call Stack

[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException exception, bool breakConnection, System.Action<System.Action> wrapCloseInAction)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior runBehavior, Microsoft.Data.SqlClient.SqlCommand cmdHandler, Microsoft.Data.SqlClient.SqlDataReader dataStream, Microsoft.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, Microsoft.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(Microsoft.Data.SqlClient.SqlDataReader ds, Microsoft.Data.SqlClient.RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, bool isAsync, int timeout, out System.Threading.Tasks.Task task, bool asyncWrite, bool inRetry, Microsoft.Data.SqlClient.SqlDataReader ds, bool describeParameterEncryptionRequest)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, System.Threading.Tasks.TaskCompletionSource<object> completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry, string method)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource<object> completion, bool sendToPipe, int timeout, out bool usedCache, bool asyncWrite, bool inRetry, string methodName)	Unknown
[Exception] Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()	Unknown
[Exception] Z.EntityFramework.Extensions.EFCore.dll!Z.EntityFramework.Extensions.BatchUpdate.(System.Data.Common.DbCommand , int? )	Unknown
[Exception] System.Private.CoreLib.dll!System.Threading.Tasks.Task..cctor.AnonymousMethod__272_0(object obj)	Unknown
[Exception] System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(System.Threading.Thread threadPoolThread, System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state)	Unknown
[Exception] System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(System.Threading.Thread threadPoolThread, System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state)	Unknown
[Exception] System.Private.CoreLib.dll!System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task currentTaskSlot, System.Threading.Thread threadPoolThread)	Unknown
[Exception] UpdateFromQuery_Issue.dll!Program.<Main>$(string[] args) Line 25	C#
UpdateFromQuery_Issue.dll!Program.<Main>$(string[] args) Line 29	C#

Stack Trace

   at Microsoft.Data.SqlClient.SqlConnection.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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   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 Z.EntityFramework.Extensions.BatchUpdate.<>c.(DbCommand , Nullable`1 )
   at Z.EntityFramework.Extensions.BatchUpdate.Execute[T](IQueryable`1 query, Expression`1 updateFactory)
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Program.<<Main>$>d__0.MoveNext() in C:\Users\user\UpdateFromQuery_Issue\UpdateFromQuery_Issue\Program.cs:line 25
   at Program.<<Main>$>d__0.MoveNext() in C:\Users\user\UpdateFromQuery_Issue\UpdateFromQuery_Issue\Program.cs:line 29

Project

There is a TPH inheritance structure with Document as the base class. Invoice is implementing Document and has an owned type property from type Money. Document also holds a required Rowversion as a timestamp column.

A call to UpdateFromQuery throws that exception.

await dbContext
        .Invoices
        .Where(d => d.Id < 100)
        .UpdateFromQueryAsync(d => new Invoice { Info = "Updated" }); // <-- Microsoft.Data.SqlClient.SqlException:
                                                                      // 'The column 'RowVersion' was specified multiple times for 'B'.'

As seen in the generated SQL query, provided by SQL Profiler, the column [d.].RowVersion] is inserted multiple times and therefore leads to an exception.

exec sp_executesql N'
UPDATE A 
SET A.[Info] = @zzz_BatchUpdate_0
FROM [Documents] AS A
INNER JOIN ( SELECT [d].[Id], [d].[DocumentType], [d].[Info], [d].[RowVersion], [d].[Amount_Amount], [d].[Amount_CurrencyCode], [d].[RowVersion]
FROM [Documents] AS [d]
WHERE [d].[Id] < 100
           ) AS B ON A.[Id] = B.[Id]
',N'@zzz_BatchUpdate_0 nvarchar(7)',@zzz_BatchUpdate_0=N'Updated'

Entire Project


using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

Console.WriteLine("Hello, Z EF Plus!");

await using (MyDbContext dbContext = new())
{
    await dbContext.Database.EnsureDeletedAsync();
    await dbContext.Database.EnsureCreatedAsync();

    dbContext.Invoices.Add(new()
    {
        Info = "initial",
        Amount = new()
        {
            Amount = 500.40m,
            CurrencyCode = "EUR"
        }
    });
    await dbContext.SaveChangesAsync();
}

await using (MyDbContext dbContext = new())
{
    await dbContext
        .Invoices
        .Where(d => d.Id < 100)
        .UpdateFromQueryAsync(d => new Invoice { Info = "Updated" }); // <-- Microsoft.Data.SqlClient.SqlException:
                                                                      // 'The column 'RowVersion' was specified multiple times for 'B'.'

}

sealed class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("Server=localhost;Database=ZEfPlusTest;Integrated Security=true;TrustServerCertificate=True");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Document>(b =>
        {
            b.Property(d => d.RowVersion)
                .IsConcurrencyToken()
                .HasColumnType("timestamp")
                .ValueGeneratedOnAddOrUpdate();

            b.HasDiscriminator(d => d.DocumentType)
                .HasValue<Invoice>(0);
        });
        modelBuilder.Entity<Invoice>(b =>
        {
            b.OwnsOne(i => i.Amount);
        });
    }

    public DbSet<Document> Documents { get; set; }
    public DbSet<Invoice> Invoices { get; set; }
}

sealed class Money
{
    public decimal Amount { get; set; }
    public string? CurrencyCode { get; set; }
}

abstract class Document
{
    public int Id { get; set; }
    public string? Info { get; set; }
    public byte DocumentType { get; set; }

    [Required] // <--- important
    public byte[]? RowVersion { get; set; }
}

sealed class Invoice : Document
{
    public Money? Amount { get; set; }
}

Further technical details

EF version: 7.0.0-rc.1.22426.7
EF Extensions version: 7.0.0-rc.1.22426.7-01
Database Provider: Microsoft.EntityFrameworkCore.SqlServer

Oct 03 '22 13:10 JakobFerdinand

Hello @JakobFerdinand ,

We will look at it more soon, but at this moment, it happens because we do not always handle correctly the owned type.

You can fix it with this temporary option:

EntityFrameworkManager.TmpUseDistinctTableLogic = true

Which should only select columns once.

Let me know if that worked with this option.

Best Regards,

Jon

Oct 04 '22 15:10 JonathanMagnan

On first sight it seems that setting that property solves our problem. But I could not find any documentation or information about it. Does it cause any side effects or any other behavior?

Oct 06 '22 06:10 JakobFerdinand

Hello @JakobFerdinand ,

Yes, there is some side effect:

Might/Will not work with owned type
Might/Will not work with shadow value

But other then this it should be fine.

Eventually, when we improve/rewrite this feature, we will probably change this option name as having Tmp normally means that we did a temporary quick fix for someone, and the options should have been only temporary...

Oct 13 '22 13:10 JonathanMagnan