How should I access a computed column in Entity Framework Code First?

Question

I am using Entity Framework Code First in my ASP.NET MVC application. One of my classes has several columns that are added together. I am storing these columns as computed columns in the tables by running an alter table script in the database initializer. Let's say the class looks like:

public class Bond
{
        public decimal ParAmountOfIssuance { get; set; }
        public decimal AccruedInterest { get; set; }
        public decimal Premium { get; set; }
        public decimal OriginalIssueDiscount { get; set; }
}

The alter script is something like:

alter table Bonds
add TotalSources as (ParAmountOfIssuance + AccruedInterest + Premium - OriginalIssueDiscount)

I want the Total Sources column to be available for viewing in the web app. What's the best way to accomplish this? The [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribute doesn't work because EF Code First creates the table from the class before the alter script is ran.

Any suggestions are welcome.

Couldn't you use [NotMapped] and calculate this in code? It is not much of "code first" this way. Why you need to alter schema in script? — Goran Obradovic, Aug 08 '11 at 18:07
I'm afraid that what you already found is the final result: It's not possible to let EF create a database with a computed column (aside from timestamp or rowversion properties). But you *must* put the `Computed` attribute on the property in order to let EF handle computed columns correctly (only read, never write). Consequence is that it only works with mapping to existing DBs but not with a DB created by EF Code-First. See also here: http://msdn.microsoft.com/en-us/library/gg193958.aspx in section "DatabaseGenerated", 2nd paragraph. — Slauma, Aug 08 '11 at 18:48
@obrad, I don't want to perform the calculations in code because I don't know for sure that my application is the only one that will ever change the values of the columns. The alter script creates the computed columns. That is, unless you're asking if I should perform the calculations in code when the data is being edited/viewed, which is what I'm presently doing. It doesn't seem like the best course of action. — Robert S., Aug 08 '11 at 19:12
@robert-s If you need to have some logic in your database, that is ok if you accept tradeoffs. If you use CodeFirst and generate database, that means that your database depends on your application. But if you are unsure about having to use database in more than one application, then tradeoff is CodeFirst. If you are using CodeFirst, then you should be totally unaware of database, it is abstracted, and if you need to share data, then odata or other service is option. As this cannot be done without script in current version of CF, that means that it is not supported in CF :S — Goran Obradovic, Aug 08 '11 at 21:17
What if you would use [DatabaseGenerated(DatabaseGeneratedOption.Computed)] or [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute, and then from script only altered this column with formula? That way CF won't write to this field, and you will be able to read it. This way it is not different from using existing database, and that is supported. — Goran Obradovic, Aug 09 '11 at 10:44
Never mind. I see now that EF throws exception in that case. — Goran Obradovic, Aug 09 '11 at 11:12

Goran Obradovic · Accepted Answer · 2011-08-13T15:37:50.110

I have a somewhat of an workaround.

You can only use calculated field on a existing database.

If you add your property to CF object as:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public decimal TotalSources { get; set; }

and if you add a line in your script that will delete information about generation of that database:

DELETE FROM [dbo].[EdmMetadata]

CF will assume it is existing database and it will work, I have just tried.

UPDATE I forgot, if you add property to your Bond entity like this, then in your script you need to alter it to make it calculated, not add it :) You can even manually "synchronize" database and model - at point where you have everything working without this field, add it in model as computed, and in table as calculated. When you delete hash from edm metadata table CF will work without trying to regenerate model with database.

score 6 · Answer 2 · answered Aug 09 '11 at 12:26

This is definitely not supported - defining Computed option on custom property will throw exception. Code first = logic in code. If you need custom computed properties use database first. The only database logic supported by code first is identity column and timestamp.

The problem is that you need the column to be marked as computed but creating database will not allow that. If the column is not marked as computed it will be updatable = EF will generate update statements trying to update this column which will fail in the database.

score 0 · Answer 3 · 2017-08-04T11:23:05.037

I'm doing computed columns in CF (WinForms) like that (I don't know if it's the best):

This is one Entity:

public class Result
{
    public int ResultId { get; set; }
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public float Arabic { get; set; }
    public float English { get; set; }
    public float Math { get; set; }
    public float Science { get; set; }
    public float Total { get; set; }
}

This is Form2:

    private void Form2_Load(object sender, EventArgs e)
    {
        Model1 model = new Model1();//Model1 : DbContext
        model.Database.CreateIfNotExists();
        model.Database.ExecuteSqlCommand("alter table Results drop column Total; alter table Results add Total AS (Arabic + English + Math + Science)");

        var r1 = (from s in model.Students
                  join r in model.Results
                  on s.StudentId equals r.StudentId
                  select new { s.StudentName, r.Arabic, r.English, r.Science, r.Math, r.Total }).ToList();
        dataGridView1.DataSource = r1;
    }

How should I access a computed column in Entity Framework Code First?

3 Answers3

Linked