CodeDemen CodeDemen - 2 years ago 84
C# Question

Calculated column in EF Code First

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download