Michael Tot Korsgaard Michael Tot Korsgaard - 1 month ago 6
MySQL Question

Insert a specific id into auto incremented field in MySQL with Entity Framework 5

So this i my situation.

I have an old database with a lot, and I mean a LOT of data. However I've ended up creating a new database structure and now I want to move data from the old database into the new one. Being a programmer I'm not keen on doing it manually which is why I made a program to do just that.

Now here is my problem: When adding a new item into the new database I'm unable to set it's id, which is crucial for the different link references and general foreign key references that exists in the old database.

My question is therefore: How would I be able to insert an specified id integer into a field that has auto_increment in my MySQL database via Entity Framework 5 which uses database first in C#?




This is my MySQL script for the new database:

CREATE TABLE Profile
(
_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
UNIQUE KEY(_id),
etc etc
)




My insert code:

public Profile Add(Profile item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}

db.Entry(item).State = EntityState.Added;
db.SaveChanges();
return item;
}


On an end note I whould like to inform that I know this is bad practise in general, but the database is empty and the id inserted are auto generated and unique from the old database which will ensure that no dublicates will occour

Answer

The solution I found where to alter the table before inserting into it. It's basicly the solution other people gave, however I needed to make a few turns.

This is what I did:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE Profile DROP PRIMARY KEY,
MODIFY _id INT PRIMARY KEY NOT NULL;

insert commmand here....

ALTER TABLE Profile DROP PRIMARY KEY,
MODIFY _id INT AUTO_INCREMENT PRIMARY KEY NOT NULL;

SET FOREIGN_KEY_CHECKS=1;