Entity Framework 6 w/ DatabaseGeneratedOption.Computed: column does not allow nulls. INSERT fails

Question

Entity Framework 6 with a Database Generated field is failing on insert. From what I've read once you mark a column as DatabaseGenerated, EF won't try to insert/update anything into that field, which is especially important in my case because the fields on the database are SQL Server computed columns and not just default values, which is what I've seen in a lot of similar questions on Stack Overflow/Google.

Column definition (using a User Defined Function in the computed column):

ALTER TABLE dbo.MyModel ADD [TotalUnits]  AS ([dbo].[CalculateTotalUnits]([Id]));

EF definition:

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

And then I'm just doing a

var myNewModel = new MyModel();
DbContext.MyModels.Add(myNewModel);
DbContext.SaveChanges();

Which gives:

System.Data.SqlClient.SqlException : Cannot insert the value NULL into column 'TotalUnits', table 'MyDatabase.dbo.MyModel'; column does not allow nulls. INSERT fails.

Why is EF trying to insert a NULL value into a computed column, and how can I tell it not to?

are you sure the db schema is configured correctly? EF will insert NULL into computed and identity columns, since these are expected to be set by DBMS. — DevilSuichiro
@DevilSuichiro If it's expected to be set by DBMS, why would EF try to insert anything (even NULL) into it? In SSMS if I run INSERT INTO MyModel ([...], [TotalUnits]) VALUES ([...], NULL) I get the expected The column "TotalUnits" cannot be modified because it is either a computed column or is the result of a UNION operator.. You can't insert anything, even NULL, into a computed column. I'm not sure why Entity Framework would try... — Seafish
is this the same schema? as you see there, the given exception is the thrown SqlException, and it differs. What is the computed sql statement? — DevilSuichiro
Perhaps your user defined function is returning NULL for that record? Try to manually run the function with that value of the parameter ID to see the returned value. — Diana
@DevilSuichiro You are right - for some reason we have it set up so our integration tests don't use our explicitly configured EF migrations but instead use automatic migrations. We add the computed column definition in our explicit migrations, so the automatic migrations must be adding the columns in as a normal non-nullable decimal field. Although I'm still confused as to why EF would try to insert NULL into any field that has [DatabaseGenerated(DatabaseGeneratedOption.Computed)] on top regardless of what the actual database column type is. I would expect it to just exclude that column. — Seafish

Seafish Seafish · Accepted Answer · 2017-07-10T18:48:19

Turns out when running integration tests EF was configured to use Automatic Migrations instead of our own migrations. Since the computed columns were being added in a custom SQL script during the Up method of a migration, the columns weren't actually computed columns during the tests but were in fact generated by EF as regular decimal (non-nullable) fields. Thus trying to add a new model to the context was causing EF to insert NULL into those columns and blowing up.

Solution is to actually run the migrations in the integration tests. Once the column is actually computed than EF stops tracking it.

Entity Framework 6 w/ DatabaseGeneratedOption.Computed: column does not allow nulls. INSERT fails

1 Answers