Thoughts on using EF to insert fields with default values

Today, when EF is used for the insert operation, it is found that the values of a datetime type field (createdate) in the database are all null. As a result, the table structure shows that the createdate field is allowed to be empty.

Although it is null, the default value getdate () is set. It should not be null. Then the test starts.

Allowed null values for a field

The users table structure is as follows:

If a field has a default value and can be null, what will happen during the insert operation?

For example, in the table structure, createdate is allowed to be null, and the default value is getdate (). In this way, what will happen when the traditional SQL statement and EF are used for insert operations?

First look at the traditional SQL statements:

insert into Users(Username,Password) values(‘test‘,‘123456‘)

Insert result:

There is no problem with SQL.

Use EF again:

Users user = new Users();user.Username = "test2";user.Password = "123456";TestEntities entity = new TestEntities();entity.Users.Add(user);int result = entity.SaveChanges();

Insert result:

EF insertion is actually null !!! Immediately open the SQL Server Profiler monitoring generated SQL, EF actually generated the following SQL:

Fields without a value assignment are set to null when EF generates SQL statements. Do you have any default values!

It may be that EF is very delicate. My field can be null. When EF generates an SQL statement, it is set to null. So what kind of SQL statement does EF generate when I set the field to not allow null?

The field does not allow null values.

The users table structure is as follows:

This time, createdate cannot be null, and SQL is of course normal:

insert into Users(Username,Password) values(‘admin‘,‘123456‘)

Then let's look at EF:

Users user = new Users();user.Username = "admin";user.Password = "123456";TestEntities entity = new TestEntities();entity.Users.Add(user);int result = entity.SaveChanges();

Result...

"The conversion from datetime2 data type to datetime data type generates a value out of the range. "

EF threw an exception !! Why is this exception thrown? What does this mean? As a result, the SQL Server Profiler appeared again:

If a field of the date type is not assigned a value, the default value is "0001-01-01 00:00:00". This is irrelevant to the SQL generation. It is the CLR initialization operation before the SQL generation, similarly, if a field of the string type is not assigned a value, the default value is string. empty!

However, it will not throw an exception. At most, the field values are stored as "0001-01-01 00:00:00.

As a result, msdn finds that the datetime type date range only supports 1750-01-01 00:00:00 to 9999-12-31 23:59:59. 997, and 0001-01-01 00:00:00 is not in this range, so an exception is thrown. It turns out that.

Datetime and datetime2 support the following date ranges:

Conclusion

Therefore, whether the field has a default value or not, EF insertion does not have any practical effect. Therefore, if you use EF, we recommend that you simply write it. For example, user. createdate = datetime. Now.

PS. here is just an example. In fact, it is not recommended to write datetime in actual projects. now, because the database system time and server time are generally different, in my project, it encapsulates a method called getsqlserversystemdatetime.

Is there any other method?

Yes. Open the. edmx file of EF and find the <entitytype name = "table name"> node:

<Property Name="CreateDate" Type="datetime" Nullable="false"  />

Changed:

<Property Name="CreateDate" Type="datetime" Nullable="false" StoreGeneratedPattern="Computed" />

You can.

Storegeneratedpattern is an enumeration:

• None indicates that it is not an attribute generated by the server. This is the default value. If the storegeneratedpattern attribute is not available, the default value is none.
• Identity generates a value during insertion, but remains unchanged during update.
• Computed generates a value when executing insert and update.

I found through actual tests:

1. If the storegeneratedpattern value is set to identity, an exception is thrown when the createdate field is modified;
2. If you set the storegeneratedpattern value to computed, no exception is thrown, but the value is still not modified, even if you write user. createdate = "XXX ".

Therefore, if you use EF, we recommend that you assign user. createdate = XX. getsqlserversystemdatetime () directly to the program (). This method is easy and secure. It is too troublesome to modify the edmx file. Besides, you must modify the edmx file every time you add a datetime field.

Finally, I would like to thank you for your suggestions. You are welcome to personally test and feedback the results to me.

Thoughts on using EF to insert fields with default values