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?

Bradawl

get { return /*calculate here */ } – Kamikamikaze

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

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

Reasonable

I know about it but how do I add a formula to compute it to my database throught EF, so that it would be created by console commad update-database? – Bradawl

Please state this clearly in your question. It means you want migrations to create a computed column. There is an example here. – Reasonable

does setter have to be private? – Glycogenesis

@Glycogenesis Yes, probably better to do that. – Reasonable

This answer is wrong, Computed fields cannot run subquery. – Jiggle

@HosseinShahdoost But you can call scalar-valued functions – Reasonable

side question: how would you do this for a property that needs to be calculated server side, and not db side? – Taken

@Taken maybe you can use the [NotMapped] attribute and then you can do your own calculations server side as EF will completely ignore this property. – Daina

Good answer @GertArnold, but this creates a column in the table. From what I can see in the table, the column remains unpopulated (all contain zero) and the value seems to be calculated at runtime. If I added the [NotMapped] property aswell, wouldn't this achieve the same result but without the extra column? – Daina

This answer should be updated to add that for EF Core, the model builder should use method ValueGeneratedOnAddOrUpdate() because HasDatabaseGeneratedOption does not exist. Otherwise, great answer. – Mot

Answer 2

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.

Dayna

This only applies to EF Core. The question references EF5, which does not support the referenced method. – Indictable

Answer 3

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

Okinawa

+1 for adding private set. Computed column should not be set when adding new objects. – Gilthead

Happened to view this question again and now I see that the part /* do your sum here */ doesn't apply. If the property is calculated inside the class, it should be annotated as [NotMapped]. But the value comes from the database, so it should just be a simple get property. – Reasonable

@GertArnold see here - "Since the FullName property is calculated by the database, it will get out of sync on the object side as soon as we make a change to the FirstName or LastName property. Luckily, we can have the best of both worlds here by also adding the calculation back to the getter on the FullName property" – Boor

@AlexFoxGill Then what's the point? Why bother storing computed values if your dynamically going to recalculate them everytime in case they get "out of sync"? – Byline

@RuudLenders so that you can use the computed column in LINQ queries. – Boor

Answer 4

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

Jordaens

I consider your solution as the most simple and elegant one. Thank you! – Weisshorn

Answer 5

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

Christology

Answer 6

I would go about this by just using a view model. For example rather than have the FirstTable class as a db entity would you not be better just having a view model class called FirstTable and then have a function that is used to return this class that would include the calculated sum? For example your class would just be:

public class FirstTable {
  public int UserID { get; set; }
  public double Sum { get; set; }
 }

And then you would have a function that you call that returns the calculated sum:

public FirsTable GetNetSumByUserID(int UserId)
{
  double income = dbcontext.Income.Where(g => g.UserID == UserId).Select(f => f.inSum);
  double expenses = dbcontext.Outcome.Where(g => g.UserID == UserId).Select(f => f.outSum);
  double sum = (income - expense);
  FirstTable _FirsTable = new FirstTable{ UserID = UserId, Sum = sum};
  return _FirstTable;
}

Basically the same as an SQL view and as @Linus mentioned I don't think it would be a good idea keeping the computed value in the database. Just some thoughts.

Leishaleishmania

+1 for I don't think it would be a good idea keeping the computed value in the database - especially if you going to use Azure SQL which will start to deadlock on heavy load. – Broomfield

@ppumkin In most cases, aggregate calculation is going to be better performed in the DB. Think of something like 'most recent comment id'. You don't want to have to pull back every CommentID to only take one of them. Not only are you wasting data and memory, but you're also increasing the load on the DB itself, and you actually put shared locks on more rows for longer. What's more, you'd have to be updating a lot of rows all the time, which is probably a design that needs review. – Floodlight

OK. I just meant keep them in memory, computed values, cached. Dont go to the database for every visitor. Its going to cause issues. I have seen this happen too many times. – Broomfield

Answer 7

I stumbled across this question when trying to have an EF Code First model with a string column "Slug", be derived from another string column "Name". The approach I took was slightly different but worked out well so I will share it here.

private string _name;

public string Name
{
    get { return _name; }
    set
    {
        _slug = value.ToUrlSlug(); // the magic happens here
        _name = value; // but don't forget to set your name too!
    }
}

public string Slug { get; private set; }

What is nice about this approach is you get the automatic slug generation, while never exposing the slug setter. The .ToUrlSlug() method isn't the important part of this post, you could use anything in its place to do the work you need done. Cheers!

Karlene

Didn't you edit out all the bits that actually link Slug to Name? As currently written, the Name setter shouldn't even compile. – Loquat

No, before your edit, it was a workable example. After your edit, it makes no sense. – Loquat