I need to persist an entity instance into database table. The table has columns with default constraint. When I directly run a sql insert statement, the default values correctly inserted. However, if I insert the entity by IRepository.InsertAndGetIdAsync(), the default values are always null or DBNull.
For example, a table with 3 int type columns: T.col1, T.col2, and T.col3. The T.col2 is nullable and has a default constraint of value 2, and T.col3 is not allowed null and has default constraint of value 3.
If I run insert statement: insert into T (col1) values (1) It results correctly as col1=1, col2=2, and col3=3.
When I use IRepository.InsertAndGetIdAsync() like:
var t=new T(); t.col1=1; IRepository<T>.InsertAndGetIdAsync(t);
The data table in database inserts a record with values of col1=1, col2=NULL, col3=0.
It likes that the IRepository<T>.InsertAndGetIdAsync(t) does not respect the database rule. What do I miss?
Thanks,
3 Answer(s)
-
0
Hi @fguo,
I think this is related to Entity Framework. Can you try to insert your entity using dbContext ? And see same thing happens ?
If it happens, you can set default values in your entity class's constructor.
Thanks.
-
0
Yes. As my research, this is EF's issue. EF core seems not too friendly to DB first approach. I guess that, if an Entity defines a property without explicit initial value, it uses the implicit default values upon the data type. It results all fields having values while generating SQL script, so database default constraint never be triggered.
I do set default values in entity class's constructor, but this approach has some shortcomings:
- We have to manually initiate a value on all properties to match the Db table columns' default value.
- Our DB is shared with multiple applications. An application developer cannot control Db. If DB default value changed, all hard corded entity property initial values must be manually changed accordingly.
I tried to use fluent ValueGeneratedOnAdd() in DbContext.OnModelCreating() event. It is a better way to get Db' default value, but doesn’t work as expected in some cases. E.g. if DB default value = 1, you can’t override it as 0, because it treats 0 as null for int type, I guess.
I currently use the above two workarounds to handle the default values.
Thanks,
-
0
Hi @ fguo,
Thanks for detailed information :).
