Custom logic in code-first EF6 SqlServerMigrationSqlGenerator not working

Question

I am trying to set the default value SQL for a computed column called 'Duration' in a table 'dbo.Table1', in code-first Entity Framework 6 migration through SqlServerMigrationSqlGenerator class.

I tried setting this in Generate methods for AddColumnOperation as well as for CreateTableOperation. While the code under Generate method for column never fires, but the code under Generate table fires and throws an error saying the following. (the column EndTime is a column in table dbo.Table1 and so is StartTime)

The name "EndTime" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Question: How could I make this work in either of the Generate methods in code below?

        internal class CustomImplForSqlServerMigration: SqlServerMigrationSqlGenerator {
        protected override void Generate(AlterColumnOperation alterColumnOperation) {

            base.Generate(alterColumnOperation);
        }
        protected override void Generate(AddColumnOperation addColumnOperation) {


            if (addColumnOperation.Table == "dbo.Table1" && addColumnOperation.Column.Name == "Duration") {
                addColumnOperation.Column.DefaultValueSql = "(CAST(CAST(EndTime AS DATETIME) - CAST(StartTime AS DATETIME) AS TIME))";
            }
            base.Generate(addColumnOperation);
        }

        protected override void Generate(CreateTableOperation createTableOperation) {


            if (createTableOperation.Name == "dbo.Table1") {
                foreach(ColumnModel cm in createTableOperation.Columns) {
                    if (cm.Name == "Duration") {
                        cm.DefaultValueSql = "(CAST(CAST(EndTime AS DATETIME) - CAST(StartTime AS DATETIME) AS TIME))";
                    }
                }
            }
            base.Generate(createTableOperation);
        }

    }

UPDATE 1:

I used another simple approach to add my custom logic for modifying database objects using ExecuteSqlCommand. Just follow the steps below to use this in your situation.

• come up with the custom script for modifying or creating a database object
• execute a command in Seed method for every custom script

• make sure that the ExecuteSqlCommand statement is placed at end of Seed method and also context.SaveChanges( ) method is called before the code for custom scripts in case there is a dependency on seed data

  protected override void Seed(EfCodeFirst.ShiftsDb context)
  {
     //Write your seed data statements
  
     //call SaveChanges in case your custom script depends
     //on some seed data
     context.SaveChanges();
  
      //include your custom scripts like ALTER TABLE 
      //or CREATE PROCEDURE or anything else
      //use a ExecuteSqlCommand for every custom script
      context.Database.ExecuteSqlCommand(@"ALTER TABLE ShiftTypes DROP COLUMN Duration; 
       ALTER TABLE TABLE1 ADD Duration AS (CAST(CAST(EndTime AS DATETIME) - 
        CAST(StartTime AS DATETIME) AS TIME)); ");
  
  }
  

Answer 1

This is not a limitation of EF but of the database itself - you cannot refer to other columns within Default value specification. What I would recommend you instead is to write stored procedure for inserting new Table1 entities and then map with fluent api according to article.