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