Calculated Column in Ef Code First

Calculated column in EF Code First

You can create computed columns in your database tables. In the EF model you just annotate the corresponding properties with the DatabaseGenerated attribute:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public double Summ { get; private set; } 

Or with fluent mapping:

modelBuilder.Entity<Income>().Property(t => t.Summ)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

As suggested by Matija Grcic and in a comment, it's a good idea to make the property private set, because you'd probably never want to set it in application code. Entity Framework has no problems with private setters.

Note: For EF .NET Core you should to use ValueGeneratedOnAddOrUpdate because HasDatabaseGeneratedOption doesnt exists, e.g.:

modelBuilder.Entity<Income>().Property(t => t.Summ)
    .ValueGeneratedOnAddOrUpdate()

How should I access a computed column in Entity Framework Code First?

I have a somewhat of an workaround.

You can only use calculated field on a existing database.

If you add your property to CF object as:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public decimal TotalSources { get; set; }

and if you add a line in your script that will delete information about generation of that database:

DELETE FROM [dbo].[EdmMetadata]

CF will assume it is existing database and it will work, I have just tried.

UPDATE I forgot, if you add property to your Bond entity like this, then in your script you need to alter it to make it calculated, not add it :)
You can even manually "synchronize" database and model - at point where you have everything working without this field, add it in model as computed, and in table as calculated. When you delete hash from edm metadata table CF will work without trying to regenerate model with database.

How to create COMPUTED column in MS SQL by ENTITY FRAMEWORK CORE code first with condition (string + auto increment number)

After a lot of search, finally I got the correct answer.

Model is:-

public partial class BikeParts
    {
        public int ID { get; set; }
        public string BikePartsGuid { get; set; }
        public int BikePartId { get; set; }
        public string BikePartName { get; set; }
    }

And configuration of model is:-

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {

        modelBuilder.HasSequence<int>("BikePartIdHelper", schema: "dbo").StartsAt(1).IncrementsBy(1);

        modelBuilder.Entity<BikeParts>(entity =>
        {
                entity.ToTable("BikeParts1");
                entity.HasKey(e => e.ID);
                entity.Property(e => e.ID).HasColumnName("ID").UseSqlServerIdentityColumn();
                entity.Property(e => e.BikePartId).HasColumnName("BikePart_ID")
                .HasDefaultValueSql("NEXT VALUE FOR dbo.BikePartIdHelper");
                entity.Property(e => e.BikePartName)
                    .HasColumnName("BikePart_Name")
                    .HasMaxLength(100)
                    .IsUnicode(false);
                entity.Property(e => e.BikePartsGuid)
                    .HasColumnName("BikeParts_GUID")
                    .HasMaxLength(13)
                    .IsUnicode(false)
                    .HasComputedColumnSql("('ABCD-'+right(replicate('0',(8))+CONVERT([varchar],[BikePart_ID]),(8)))");
        });        
 }

I used sequences for auto-generate BikePartId with the help of HasDefaultValueSql
and on BikePartsGuid column I used HasComputedColumnSql

How to make persisted computed column in EF code first?

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.

How to add computed column using migrations in code first?

Entity Framework doesn't know how to properly handle migrations for computed columns, so you need to help it out.

Firstly, delete the computed column from the table in the database.

Then create a new migration in the package manager console:

add-migration FullNameComputed

Replace the body of the Up() method in the new migration with the following:

Sql("ALTER TABLE [TableName] ADD [FullName] AS ([FirstName] + ' ' + [LastName])");

Finally, run the migration from the package manager console:

update-database

---

Related Topics