Why I'm gettin Cannot insert the value NULL into computed column?

Question

I'm using EF-Code First and this is my model with Data Annotation:

public int Count { get; set; }
public int Fee { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public int Total { get; set; }

Or like this:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

public int Total
{
    get { return Fee * Count; }
    private set{}
}

When I want to create a new record I'm getting the following error:

Cannot insert the value NULL into column 'Total'; column does not allow nulls. INSERT fails. The statement has been terminated.

As you can see I'm getting the correct computed value in the Create Action Method with model binder:

By removing the DatabaseGenerated(DatabaseGeneratedOption.Computed) and set the computed value from my code it works fine and I see the updated total in the database. But my question is if I can do this without DatabaseGenerated(DatabaseGeneratedOption.Computed) so what's the DatabaseGenerated(DatabaseGeneratedOption.Computed) usage?

This works:

public int Total
{
    get { return Fee * Count; }
    private set{}
}

Alex · Answer 1 · 2017-01-07T19:59:20.293

1

As mentioned here and here you can't update a calculated column because it's value is retreived from database and never sent back.

So the solution would be to create another not mapped calculated property in your model which will be used in your code and keep your Total property mapped to the database:

public int Count { get; set; }
public int Fee { get; set; }
public int Total { get; set; }

[NotMapped]
public int CalculatedTotal
{
    get { return Count*Fee; }
}

edited Jan 07 '17 at 19:59

answered Jan 02 '17 at 08:25

Alex

1,433
9
18

1

if I should remove the DatabaseGenerated(DatabaseGeneratedOption.Computed) and set the computed value from my code so what's the DatabaseGenerated(DatabaseGeneratedOption.Computed) usage? – Jan 02 '17 at 08:27
Computed columns. Take a look [here](https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx) and [here](http://stackoverflow.com/questions/15585330/calculated-column-in-ef-code-first). – Alex Jan 02 '17 at 08:30

score 0 · Answer 2 · answered Jan 02 '17 at 08:07

0

Try to declare public int? Total to accept null.

answered Jan 02 '17 at 08:07

Elmer Dantas

4,649
5
30
36

I tried this also in this way I'm not getting the error anymore but still in the database I see the NULL value for Total column. I want to see the calculated value in the database also. – Jan 02 '17 at 08:08
maybe [this](http://stackoverflow.com/questions/16981259/entity-framework-4-1-code-first-computed-calculated-column-not-being-inserted) is your problem. Take a look – Elmer Dantas Jan 02 '17 at 08:13
Thanks. I looked that. It suggested that I set a computed column at database engine side but I cannot do this because I'm using Code First and what if I change the model the database will drop and it will recreate again so this is not a good option for me. – Jan 02 '17 at 08:18
Actually it seems that you just need to remove `DatabaseGenerated(DatabaseGeneratedOption.Computed)` from your code. Because this option tells that this value will be computed by database engine but you're already doing this by code..right? – Elmer Dantas Jan 02 '17 at 08:21
My main question is exactly this: if his value will be computed by database engine why I'm getting the NULL value for this computed column in the database?? – Jan 02 '17 at 08:23
Also if I should remove the DatabaseGenerated(DatabaseGeneratedOption.Computed) and set the computed value from my code so what's the DatabaseGenerated(DatabaseGeneratedOption.Computed) usage? – Jan 02 '17 at 08:26
as I understand, this error is because you're trying to update this field...and `Computed` fields are `readonly` – Elmer Dantas Jan 02 '17 at 08:27
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132068/discussion-between-elmer-dantas-and-user5032790). – Elmer Dantas Jan 02 '17 at 08:29

Why I'm gettin Cannot insert the value NULL into computed column?

2 Answers2