Thoughts on using EF to insert fields with default values (continued)

Original article: Thoughts on using EF for insert operations for fields with default values (continued)

Problem description

This afternoon (now, it should have been yesterday afternoon ), yuan You Choon wrote this blog article "thinking about fields with default values when using EF for insert operations".

The main record of the blog post is the default value of the field when Choon uses EF for data insertion and updating. This problem should be common. However, the post content includes comments, it is a pity that no one can give an accurate answer (the blog posts on knowledge points are a bunch of jokes, but the problems encountered in these real projects cannot be answered, so what is the purpose, ah ...). For details, refer to the above blog. Here I will briefly describe it:

The database has an users table with a createdate field. We want to insert user data when using EF, instead of the createdate value, which is generated by default.

1. The createdate field is null: The add operation of EF is used (the createdate value is not assigned to the user object), and the inserted result is that the createdate value is null.
   
2. If the createdate field is not null, an error is returned when the user is inserted according to the preceding operation.

Choon provides the following solutions:

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

Later, Choon added two problems to achieve this (after reading the following content, you will know why these two problems occur ):

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 ".

Problem Analysis

For the sake of understanding, I will describe according to the implementation steps at that time, because I like the codefirst mode of EF, so I will use it for demonstration here and take a look at the sample code:

using System;using System.ComponentModel.DataAnnotations;using System.Data.Entity;namespace CodeFirstDemo{    class Program    {        static void Main(string[] args)        {            using (var db = new ProductContext())            {                var product = new Product { Name = "xishuai" };                db.Products.Add(product);                db.SaveChanges();                Console.WriteLine("success");                Console.ReadKey();            }        }    }    public class Product    {        [Key]        public int ID { get; set; }        public string Name { get; set; }        public DateTime? CreateTime { get; set; }    }    public class ProductContext : DbContext    {        public DbSet<Product> Products { get; set; }    }}

This is our general implementation method. Pay attention to this Code: var Product = new product {name = "xishuai"}; I didn't assign a value to createtime. In order to make the database generate successfully, I also set createtime to null (datetime ?), But the running result is: the database generated, but only the ID and name fields, and the addition of data failed:

Exception message: "alter table alter column failed because the 'producttime' column does not exist in the table 'products .", Why? The createtime attribute is not specified, for example, [required]. However, the name is not specified. Why does it generate database columns? Because the name value is specified during the add operation, EF will automatically identify these fields to generate columns.

We can generate the database first, and then conduct the experiment. We can temporarily change the code for adding data to: var Product = new product {name = "xishuai", createtime = datetime. now}; in this way, the database can be successfully generated, and then restored. We perform similar operations according to the Choon Configuration:

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]        public DateTime? CreateTime { get; set; }

The property configuration added above is the same as the Choon configuration in config, but this scenario is codefirst. There is no doubt that adding data like Choon will still be ineffective or encounter exceptions (no value for createtime). What is the problem? Let's take a look at the enumerated value of storegeneratedpattern (msdn details ):

• None: A value indicating that it is not a property generated by the server. This is the default value. If the storegeneratedpattern attribute is not available, the default value is none.
  
• Identity: A value is generated during insertion, but remains unchanged during update.
  
• Computed: A value is generated during insertion and update.

What does identity mean? Actually, it indicates the ID key, that is, the auto-incrementing key we often call. If we change the createtime data type in the above example to int, the configuration is still the original configuration, however, data recognition can be added, and someone has made an example in the garden. For details, visit databasegenerated, the data generation option of Entity Framework.

An example is also provided in msdn. I will post a summary of this point (msdn details ):

You read abve that by default, a key property that is an integer will become an identity key in the database. that wocould be the same as setting databasegenerated to databasegenerationoption. identity. if you do not want it to be an identity key, you can set the value to databasegenerationoption. none.

I am not very good at English. Please understand it on your own. I have said so much. Isn't there a solution? Of course there will be, but you are just too lazy. Google searches: "code first datetime default", "databasegenerated datetime", or "An error occurred while updating the entries. see the inner exception for details keyword, you will find the answer.

Solution

First, the createtime attribute in product is still configured as follows:

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]        public DateTime? CreateTime { get; set; }

What we need to do is to use codefirst for Migration (how to migrate? Please refer to: First try code first (with demo), first enter the command "enable-migrations" to start the migration, and then "Add-migration update1" to add the migration, at this time, the update1 migration file will be generated, and the following changes will be made after opening:

namespace CodeFirstDemo.Migrations{    using System;    using System.Data.Entity.Migrations;        public partial class Update1 : DbMigration    {        public override void Up()        {            //AlterColumn("dbo.Products", "CreateTime", c => c.DateTime());            AlterColumn("dbo.Products", "CreateTime", c => c.DateTime(defaultValueSql: "GETDATE()"));        }                public override void Down()        {            AlterColumn("dbo.Products", "CreateTime", c => c.DateTime());        }    }}

I will not talk about what the code means. After the code is changed, enter the "Update-Database" command to update it to the database. Then we will test it again:

We can see that the data is successfully added. If you are not at ease, you can go to the database. Some people may have doubts. We have added the default SQL for datetime values, is it unnecessary to configure databasegenerated for datetime? I tried to remove databasegenerated. Why? Actually, when you see this, you should understand the two questions mentioned by Choon above. Here I will explain my understanding, but it is not necessarily correct:

1. If you set the storegeneratedpattern value to identity, an exception will be thrown when you modify the createdate field: storegeneratedpattern is set to identity, that is, the ID key, but the data type is not int, but datetime, most importantly, when adding or modifying data, EF cannot find the "the formula for the computed column" (the formula for calculating the column-from msdn), so it is not surprising that no error is reported.
   
2. If you set the storegeneratedpattern value to computed, no exception is thrown, but the value is still not modified, even if you write the user. createdate = "XXX": Set storegeneratedpattern to computed (a value will be generated during insertion and update). Since it is generated, it is useless to set it again, and as shown above, the calculation formula for this column cannot be found (it can be understood as getdate), so no value is added or modified.

This is the solution in EF codefirst. As for the solution of generating edmx using "model first", I haven't tried this yet (it must be modifying the config configuration file), but I think it is similar. In addition, we recommend that you use the codefirst mode if you can use EF in your project...

Sample Code download: http://pan.baidu.com/s/1pJG3jab

To solve the problem, we need to leave "Traces ":

• Adding createddate to an entity using Entity Framework 5 code first
  
• Code first data annotations
  
• Entity Framework "code first + computed getdate ()"
  
• Entity Framework 6 code first default datetime value on insert
  
• Entity Framework-"An error occurred while updating the entries. See the inner exception for details"
  
• Cannot remember...

Thoughts on using EF to insert fields with default values (continued)