c# - How can I make a migration with a new field that will be dynamically populated, for example by multiplying two other already existing fields in EF

Question

I have entity ProductItem and the database is already updated by migration with this template:

public class ProductItem
    {
        public ProductItem()
        {
            Price = 0;
            Discount = 0;
        }
        public string Name { get; set; }

        public double Price { get; set; }

        public int Discount { get; set; }
    }

My database is already populated with some entities. I would like to add one more field TotalPrice:

public class ProductItem
    {
        public ProductItem()
        {
            Price = 0;
            Discount = 0;
            TotalPrice = 0;
        }
        public string Name { get; set; }

        public double Price { get; set; }

        public int Discount { get; set; }

        public double TotalPrice { get; set; }
    }

When adding a field TotalPrice , it will be filled with default values, but I need it to be filled using a simple formula Price*(1 - Discount * 0.01) for each field.

Here is my code for new migration with field TotalPrice:

    public partial class _addedTotalPrice : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<double>(
                name: "TotalPrice",
                table: "ProductItems",
                type: "float",
                nullable: false,
                defaultValueSql: "ROUND(Price*(1 - Discount * 0.01),0)");

            migrationBuilder.UpdateData(
                table: "AspNetRoles",
                keyColumn: "Id",
                keyValue: "96ba8f64-053a-4574-a0bb-b73ecc88c761",
                column: "ConcurrencyStamp",
                value: "431db11e-8531-48bc-9483-5b13c0759d4e");

            migrationBuilder.UpdateData(
                table: "AspNetUsers",
                keyColumn: "Id",
                keyValue: "4c6e757b-ecb2-40a8-94fe-a2d3ecca28ca",
                columns: new[] { "ConcurrencyStamp", "PasswordHash" },
                values: new object[] { "390090d0-226e-43f6-af69-c3a139d602c0", "AQAAAAEAACcQAAAAEEcQUTyWcGF4TKR/qdtnDij7UjPt9NBtkwYw8WJX6CcVKnGBM8BuOj+YJVhYdgrA7A==" });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "TotalPrice",
                table: "ProductItems");

            migrationBuilder.UpdateData(
                table: "AspNetRoles",
                keyColumn: "Id",
                keyValue: "96ba8f64-053a-4574-a0bb-b73ecc88c761",
                column: "ConcurrencyStamp",
                value: "734fc36f-7481-4782-942f-61ec56112815");

            migrationBuilder.UpdateData(
                table: "AspNetUsers",
                keyColumn: "Id",
                keyValue: "4c6e757b-ecb2-40a8-94fe-a2d3ecca28ca",
                columns: new[] { "ConcurrencyStamp", "PasswordHash" },
                values: new object[] { "3209c2fd-5fc0-46a9-9689-b73b3b7617af", "AQAAAAEAACcQAAAAEMLDk9vuX+uj98bjcAPnzRvfHw37gfJttkgeQ+vkpXzoIbOdf0tyNcJ38/uzuNCyhA==" });
        }
    }
}

I tried to do it with defaultValueSql but it doesn't work:

defaultValueSql: "ROUND(Price*(1 - Discount * 0.01),0)");

and

defaultValueSql: "SELECT ROUND(Price*(1 - Discount * 0.01),0) FROM dbo.ProductItems");

How can this be solved? Here is Terminal:

PM> dotnet ef database update 20210205151510__addedTotalPrice
Build started...
Build succeeded.
The Entity Framework tools version '5.0.1' is older than that of the runtime '5.0.2'. Update the tools for the latest features and bug fixes.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE [ProductItems] ADD [TotalPrice] float NOT NULL DEFAULT (ROUND(Price*(1 - Discount * 0.01),0));
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [ProductItems] ADD [TotalPrice] float NOT NULL DEFAULT (ROUND(Price*(1 - Discount * 0.01),0));
Microsoft.Data.SqlClient.SqlException (0x80131904): РР?С? "Price" Р?Рμ С?азС?РμС€РμР?Р? Р? Р?Р°Р?Р?Р?Р? Р?Р?Р?С?РμР?С?С?Рμ. Р”Р?Р?С?С?С?РёР?С?Р?Рё Р?С?С?Р°Р?РμР?РёС?Р?Рё С?Р?Р?С?С?С?С?С? Р?Р?Р?С?С?Р°Р?С?С?, Р?Р?Р?С?С?Р°Р?С?Р?С?Рμ Р?С?С?Р°Р?РμР?РёС? Рё (Р? Р?РμР?Р?С?Р?С?С?С… Р?Р?Р?С?РμР?С?С?ах) Р?РμС?РμР?РμР?Р?С?Рμ. РР?РμР?Р° С?С?Р?Р?Р±С?Р?Р? Р?Рμ С?азС?РμС€РμР?С?.
   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.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:9d817364-061a-4ade-b122-b50ca873140f
Error Number:128,State:1,Class:15
РР?С? "Price" Р?Рμ С?азС?РμС€РμР?Р? Р? Р?Р°Р?Р?Р?Р? Р?Р?Р?С?РμР?С?С?Рμ. Р”Р?Р?С?С?С?РёР?С?Р?Рё Р?С?С?Р°Р?РμР?РёС?Р?Рё С?Р?Р?С?С?С?С?С? Р?Р?Р?С?С?Р°Р?С?С?, Р?Р?Р?С?С?Р°Р?С?Р?С?Рμ Р?С?С?Р°Р?РμР?РёС? Рё (Р? Р?РμР?Р?С?Р?С?С?С… Р?Р?Р?С?РμР?С?С?ах) Р?РμС?РμР?РμР?Р?С?Рμ. РР?РμР?Р° С?С?Р?Р?Р±С?Р?Р? Р?Рμ С?азС?РμС€РμР?С?.
PM> 

question from:https://stackoverflow.com/questions/66066875/how-can-i-make-a-migration-with-a-new-field-that-will-be-dynamically-populated

Answer 1

It looks like TotalPrice column should be computed column. Here is the link that will point you how to do this:

How to add computed column using migrations in code first?

Also, table columns are not allowed in defaultValueSql parameter