Computed Column being used in update query on savechanges

[AdrianoAE-Surface]

I have a computed column in a OwnedEntity (it's being mapped using the following implementation #15681 (comment))

percentageConfiguration.Property(v => v.Multiplier)
    .HasColumnType("decimal(3,2)")
    .HasColumnName(nameof(TaxesPercentage.Multiplier))
    .HasComputedColumnSql("(CONVERT([decimal](3,2),CONVERT([decimal](3,0),[Percentage])/(100.00)+(1))) PERSISTED");

If I change for example the percentage everything works fine and it updates as expected. The problem is, when I delete I want a soft delete so in savechanges I execute the following

foreach (var entry in ChangeTracker.Entries().Where(e => e.Properties.Any(p => p.Metadata.Name == "IsDeleted") && e.State == EntityState.Deleted))
{
    entry.State = EntityState.Modified;
    entry.CurrentValues["IsDeleted"] = true;
}

EF is generating the following (removed a few fields for brevity)

SET NOCOUNT ON;
UPDATE [Taxes].[Iva] SET [IsDeleted] = @p3, [LastModified] = @p4, [Multiplier] = @p7, [Percentage] = @p8
WHERE [Id] = @p9;
SELECT @@ROWCOUNT;

The Multiplier column should have been ignored.

Stack trace

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The column "Multiplier" cannot be modified because it is either a computed column or is the result of a UNION operator.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:2ce9a2ce-9e4c-4eff-9df6-8fa32ec12be3
Error Number:271,State:1,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Catalog.Persistence.CatalogContext.SaveChangesAsync(CancellationToken cancellationToken) in C:\Users\AdrianoEscalante\source\repos\adminbeta\Catalog.Persistence\CatalogContext.cs:line 55
   at Catalog.Persistence.CatalogContext.SaveEntitiesAsync(CancellationToken cancellationToken) in C:\Users\AdrianoEscalante\source\repos\adminbeta\Catalog.Persistence\CatalogContext.cs:line 62
   at Catalog.Application.Taxes.IvaAggregate.Commands.DeleteIva.DeleteIvaCommandHandler.Handle(DeleteIvaCommand request, CancellationToken cancellationToken) in C:\Users\AdrianoEscalante\source\repos\adminbeta\Catalog.Application\Taxes\IvaAggregate\Commands\DeleteIva\DeleteIvaCommandHandler.cs:line 30
   at Catalog.API.Application.Common.Behaviours.TransactionBehaviour`2.<>c__DisplayClass3_0.<<Handle>b__0>d.MoveNext() in C:\Users\AdrianoEscalante\source\repos\adminbeta\Catalog.API\Application\Common\Behaviours\TransactionBehaviour.cs:line 55
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c.<<ExecuteAsync>b__3_0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Catalog.API.Application.Common.Behaviours.TransactionBehaviour`2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate`1 next) in C:\Users\AdrianoEscalante\source\repos\adminbeta\Catalog.API\Application\Common\Behaviours\TransactionBehaviour.cs:line 46

EF Core 3.1
Best regards.

[ajcvickers]

@Abcdma I have not been able to reproduce this--see my code below. Please post a small, runnable project or complete code listing like below that demonstrates the behavior you are seeing.

public class Taxes
{
    public int Id { get; set; }
    public decimal Multiplier { get; set; }
    public decimal Percentage { get; set; }
    public DateTime LastModified { get; set; }
    public bool IsDeleted { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger 
        = LoggerFactory.Create(c => c.AddConsole());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Taxes>(b =>
        {
            b.Property(v => v.Multiplier)
                .HasColumnType("decimal(3,2)")
                .HasColumnName("Multiplier")
                .HasComputedColumnSql(
                    "(CONVERT([decimal](3,2),CONVERT([decimal](3,0),[Percentage])/(100.00)+(1))) PERSISTED");
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

    public override int SaveChanges()
    {
        foreach (var entry in ChangeTracker.Entries()
            .Where(e => e.Properties.Any(p => p.Metadata.Name == "IsDeleted") && e.State == EntityState.Deleted))
        {
            entry.State = EntityState.Modified;
            entry.CurrentValues["IsDeleted"] = true;
        }
        
        return base.SaveChanges();
    }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Taxes
            {
                Percentage = 10.0m,
                LastModified = DateTime.Now
            });

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var taxes = context.Set<Taxes>().Single();

            context.Remove(taxes);

            context.SaveChanges();
        }
    }
}

Log

warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data, this mode should only be enabled during development.
warn: Microsoft.EntityFrameworkCore.Model.Validation[30000]
      No type was specified for the decimal column 'Percentage' on entity type 'Taxes'. This will cause values to be silently truncated if they do not fit in the default precision and scal
e. Explicitly specify the SQL server column type that can accommodate all the values using 'HasColumnType()'.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (301ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [Test];
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (84ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [Test] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Taxes] (
          [Id] int NOT NULL IDENTITY,
          [Multiplier] AS (CONVERT([decimal](3,2),CONVERT([decimal](3,0),[Percentage])/(100.00)+(1))) PERSISTED,
          [Percentage] decimal(18,2) NOT NULL,
          [LastModified] datetime2 NOT NULL,
          [IsDeleted] bit NOT NULL,
          CONSTRAINT [PK_Taxes] PRIMARY KEY ([Id])
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (38ms) [Parameters=[@p0='False', @p1='2019-12-05T15:06:12', @p2='10.0'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Taxes] ([IsDeleted], [LastModified], [Percentage])
      VALUES (@p0, @p1, @p2);
      SELECT [Id], [Multiplier]
      FROM [Taxes]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.0 initialized 'BloggingContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [t].[Id], [t].[IsDeleted], [t].[LastModified], [t].[Multiplier], [t].[Percentage]
      FROM [Taxes] AS [t]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@p3='1', @p0='True', @p1='2019-12-05T15:06:12', @p2='10.00'], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [Taxes] SET [IsDeleted] = @p0, [LastModified] = @p1, [Percentage] = @p2
      WHERE [Id] = @p3;
      SELECT [Multiplier]
      FROM [Taxes]
      WHERE @@ROWCOUNT = 1 AND [Id] = @p3;

Process finished with exit code 0.

[AdrianoAE-Surface]

@ajcvickers https://github.com/abcdma/EFCoreComputedColumnProblem

[ajcvickers]

Note for triage: The key thing here appears to be that the mapping to a computed column is in an owned entity.

/cc @AndriySvyryd

[AdrianoAE]

I did a cleanup in the entire code to remove all the unnecessary stuff.

[jainshubham97]

This isssue is occuring with SQL Server IdentityColumn as well.

[AdrianoAE-Surface]

There is another problem with the OwnedEntities not configuring the Required, in the same project I posted above this configuration

tp.Property(p => p.Value)
    .HasColumnName(nameof(Taxes.Percentage))
    .IsRequired();

Is generating a nullable column Percentage = table.Column<int>(nullable: true),

Already saw that it's a known thing #12100

[lakeman]

I'm still seeing this exception in 5.0, but only when a table has multiple instances of the same owned type, each with a computed property.

[AndriySvyryd]

@lakeman Please open a new issue with a small repro project or code snippet