Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to mark identity column properly with Entity Framework 6.1?

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?

like image 902
Can Poyrazoğlu Avatar asked Nov 26 '15 20:11

Can Poyrazoğlu


People also ask

How do you set column as identity column?

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.

How do you do identity column?

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.

How do I get the identity column value after insert in Entity Framework?

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();

How do you add values into an identity column?

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.


2 Answers

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

like image 82
Paul McCarthy Avatar answered Nov 03 '22 02:11

Paul McCarthy


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.

like image 23
CodeNotFound Avatar answered Nov 03 '22 01:11

CodeNotFound