Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I force entity framework to insert identity columns?

I want to write some C# code to initialize my database with some seed data. Clearly, this is going to require the ability to be able to set the values of various Identity columns when inserting. I'm using a code-first approach. By default, DbContext handles the database connection and so you can't SET IDENTITY_INSERT [dbo].[MyTable] ON. So, what I've done so far is use the DbContext constructor that lets me specify a DB connection to be used. Then, I set IDENTITY_INSERT to ON in that DB connection, and then try to insert my records using entity framework. Here's an example of what I've got so far:

public class MyUserSeeder : IEntitySeeder {     public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {         context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginId = "520476", Password="28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df", IsEnabled = true, SpecialRequirements = null });         try {             connection.Open();             SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON", connection);             int retVal = cmd.ExecuteNonQuery();             context.SaveChanges();         }         finally {             connection.Close();         }     } } 

So close and yet so far - because, although cmd.ExecuteNonQuery() works fine, when I then run context.SaveChanges(), I'm informed that "Explicit value must be specified for identity column in table 'MyUser' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

Presumably, because MyUserId (which is the Identity column in the MyUser table) is the primary key, entity framework doesn't try to set it when I call context.SaveChanges(), even though I gave the MyUser entity a value for the MyUserId property.

Is there a way to force entity framework to try and insert even primary key values for an entity, then? Or maybe a way to temporarily mark MyUserId as not being a primary key value, so EF tries to insert it?

like image 506
Jez Avatar asked Oct 26 '12 11:10

Jez


People also ask

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 set an explicit value to ID property in EF core?

To insert explicit values into a SQL Server IDENTITY column, you need to manually enable IDENTITY_INSERT before calling SaveChanges() . The following example demonstrates how to set an explicit value to an id property. In the above example, we first saved a new Student with the DB-generated StudentId .

How do you set an identity insert for all tables?

You can do all tables at once in the Import / Export wizard. On the Select Source Tables and Views Page you can select the tick box in the source bar, once selected you can select Edit Mappings and you'll see Enable Identity Insert at the bottom.


1 Answers

EF 6 method, using the msdn article:

using (var dataContext = new DataModelContainer()) using (var transaction = dataContext.Database.BeginTransaction()) {     var user = new User()     {         ID = id,         Name = "John"     };      dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");      dataContext.User.Add(user);     dataContext.SaveChanges();      dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");      transaction.Commit(); } 

Update: To avoid error "Explicit value must be specified for identity column in table 'TableName' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column", you should change value of StoreGeneratedPattern property of identity column from Identity to None in model designer.

Note, changing of StoreGeneratedPattern to None will fail inserting of object without specified id (normal way) with error "Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF".

like image 178
Roman O Avatar answered Oct 02 '22 16:10

Roman O