Calculated column in EF Code First

Question

I need to have one column in my database calculated by database as (sum of rows) - (sum of rowsb). I'm using code-first model to create my database.

Here is what I mean:

public class Income {       [Key]       public int UserID { get; set; }       public double inSum { get; set; } }  public class Outcome {       [Key]       public int UserID { get; set; }       public double outSum { get; set; } }  public class FirstTable {       [Key]       public int UserID { get; set; }       public double Sum { get; set; }        // This needs to be calculated by DB as        // ( Select sum(inSum) FROM Income WHERE UserID = this.UserID)        // - (Select sum(outSum) FROM Outcome WHERE UserID = this.UserID) } 

How can I achieve this in EF CodeFirst?

Answer 1

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() 

Answer 2

public string ChargePointText { get; set; }  public class FirstTable  {     [Key]     public int UserID { get; set; }      [DatabaseGenerated(DatabaseGeneratedOption.Computed)]           public string Summ      {         get { return /* do your sum here */ }         private set { /* needed for EF */ }     } } 

References:

• Bug in EF 4.1 DatabaseGeneratedOption.Computed
• Calculated Columns in Entity Framework Code First Migrations
• Working with Computed Columns