How should I access a computed column in Entity Fr

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.

Answer 1

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

Answer 2

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.

Answer 3

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.