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?

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

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

As of 2019, EF core allows you to have computed columns in a clean way with the fluent API:

Suppose that DisplayName is the computed column you want to define, you have to define the property as usual, possibly with a private property accessor to prevent assigning it

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    // this will be computed
    public string DisplayName { get; private set; }
}

Then, in the model builder, address it with the column definition:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.DisplayName)
        // here is the computed query definition
        .HasComputedColumnSql("[LastName] + ', ' + [FirstName]");
}

For further information, have a look at MSDN.

In EF6, you can just configure the mapping setting to ignore a calculated property, like this:

Define the calculation on the get property of your model:

public class Person
{
    // ...
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName => $"{FirstName} {LastName}";
}

Then set it to ignore on the model configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    //...
    modelBuilder.Entity<Person>().Ignore(x => x.FullName)
}

One way is doing it with LINQ:

var userID = 1; // your ID
var income = dataContext.Income.First(i => i.UserID == userID);
var outcome = dataContext.Outcome.First(o => o.UserID == userID);
var summ = income.inSumm - outcome.outSumm;

You may do it within your POCO object public class FirstTable, but I would not suggest to, because I think it's not good design.

Another way would be using a SQL view. You can read a view like a table with Entity Framework. And within the view code, you may do calculations or whatever you want. Just create a view like

-- not tested
SELECT FirstTable.UserID, Income.inCome - Outcome.outCome
  FROM FirstTable INNER JOIN Income
           ON FirstTable.UserID = Income.UserID
       INNER JOIN Outcome
           ON FirstTable.UserID = Outcome.UserID

Calculated column in EF Code First

Related

Recent Posts