I've seen many posts and answers regarding how to mark a field as the identity column. Many of them are outdated and are targeting older versions of Entity Framework.
Some resources tell me to use an attribute on the field:
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }
Other resources tell me to add this code to OnModelCreating
method:
modelBuilder.Entity<User>().Property(u => u.ID).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
Which one should I use? First, second, both, doesn't matter, or something else?
You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
The SQL Server identity columnAn identity column will automatically generate and populate a numeric column value each time a new row is inserted into a table. The identity column uses the current seed value along with an increment value to generate a new identity value for each row inserted.
EF execute each INSERT command followed by SELECT scope_identity() statement. SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. The above example will execute the following SQL in the database. WHERE @@ROWCOUNT = 1 AND [StudentID] = scope_identity();
To manually insert a new value into the Id column, we first must set the IDENTITY_INSERT flag ON as follows: SET IDENTITY_INSERT Students ON; To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table.
(for people who search and arrive here) One more thing to note is that EF Core will try to use a value in the ID field on an Insert query even though it is marked as DatabaseGeneratedOption.Identity. I had -1 in my Id field an tried to add a new record and expected EF to ignore my value but instead it passed it to the Insert query and failed with 'Cannot insert explicit value for identity column' It only works when you set the Id field to zero.
As long as the type of the primary key property is numeric or GUID, Code First will, by convention, automatically configure the key as an identity column.
That means you don't need to have any of the configuration you put in your code to explicity set the property as an identity column because Code First already use covention for that. The data annotation attribute or fluent API configurations you set are useless.
You use those configurations on numeric or GUID type primary key only if you want to disable the identity.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With