Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make persisted computed column in EF code first?

How do I get this column as similar to a PERSISTED COMPUTED column in the database?

My current attempt (it loads all CompCol rows with null in seed) :

    public class Call
    {
        public Call()
        {
        }

        [Key]
        public int Id { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string CompCol
        {
            get
            {
                return "ABC-" + Convert.ToString(Id).PadLeft(5, '0');
            }
            protected set {}
        }
}
like image 733
sprocket12 Avatar asked Jan 11 '13 17:01

sprocket12


People also ask

How can we specify computed generated column in Entity Framework?

The Entity Framework Core Fluent API HasComputedColumnSql method is used to specify that the property should map to a computed column. The method takes a string indicating the expression used to generate the default value for a database column.

Is persisted in SQL computed column?

PERSISTED. Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.

Why do we persist a computed column?

Computed columns can be persisted. It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.

How do I create a calculated field in SQL?

Go to your database, right click on tables, select “New Table” option. Create all columns that you require and to mark any column as computed, select that column and go to column Properties window and write your formula for computed column.


1 Answers

The solution I found was to :

  1. Make sure auto migrations are turned off. This is so that VS will generate a script (fluent api code) for us to further customise instead of just running it. So in the configuration class :

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }
    
  2. Add the field to the class and set it as computed like so, the setter is private because we obviously cannot write to a computed field :

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string BreakdownNo { get; private set; }
    
  3. Then do an add-migration [xyz-name] in the Package Manager Console to generate the migration code, which will appear under the migrations folder with the given name.

  4. Inside the migration comment out the code in Up() and add custom SQL like so :

    public override void Up()
    {
        //AddColumn("dbo.Calls", "BreakdownNo", c => c.String());
        Sql("ALTER TABLE dbo.Calls ADD BreakdownNo AS ('BD'+RIGHT('00000'+ CAST(Id AS VARCHAR), 6))");
    }
    
  5. Do an update-database in the PM and it should add the computed column properly.

FURTHER NOTES : If you get the formula wrong then you will have to revert back the migration by doing an update-database -targetMigration: [name of migration to go back to] then do another add-migration name and amend your formula there, finishing off with update-database. There may be a better way but this is what I found and used.

I did not however find a way to make the field persisted yet.

like image 83
sprocket12 Avatar answered Nov 15 '22 21:11

sprocket12