In Entity Framework Code First, how should I access a calculated column?

c# ef-code-first entity-framework

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.

1
10
8/4/2011 4:16:57 PM

Accepted Answer

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.

12
8/13/2011 3:37:50 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow