0

I currently have an .NET application that has a SQL Sever database with GUIDs for Primary Keys. We are using NHinbernate for the ORM but want to switch to the latest version of Entity Framework and use Identities(INTs) as our Primary Keys.

Does anyone have any experience or references that will help me in making this transition? Whats the easiest way to migrate the data and implement the new ORM?

Jason Roell
  • 6,679
  • 4
  • 21
  • 28
  • I followed a process similar to the marc_s comment here http://stackoverflow.com/questions/19447573/changing-guid-primary-keys-to-integer-primary-keys. I used ROWNUMBER() to seed the new keys. – Steve Greene May 12 '15 at 14:38

2 Answers2

3

If you are choosing to use int keys because of migrating to EF, you actually don't need to change it, and you can keep the existing data, have a look at this.

But if you need to move to int keys for some other reason, it's going to be hard and not that easy. One thing about GUID is it's never duplicate, so you can do something like this,

  1. Export all the GUID's in the current structure (assume table name as key_table) and insert into a table in the new database with auto generated id. Something like this,
--------------------------------------------------
| Id | OldKey                                  |
--------------------------------------------------
| 1|   3d09565d-eb84-4e9c-965c-d530c1be8cf2    |
--------------------------------------------------
| 2|   54a93dbc-7ce8-4c88-a8e0-70cc48a84073    |
--------------------------------------------------

  1. When you do insert, you can fetch the key from this table using a select statement. wherever you need a primary key or a foreign key, something like this,

    SET IDENTITY_INSERT User_Table ON;

    Insert into User_Table (Id,RoleId,UserName,...) VALUES (select id from key_table where OldKey = '3d09565d-eb84-4e9c-965c-d530c1be8cf2'),(select id from key_table where OldKey = '54a93dbc-7ce8-4c88-a8e0-70cc48a84073'),'User 1',...);

    SET IDENTITY_INSERT User_Table OFF;

this would be the easiest way of doing it, but the id columns would not be linear in your new database with this approach.

Community
  • 1
  • 1
Low Flying Pelican
  • 5,974
  • 1
  • 32
  • 43
1

This is a breaking change and there is no tools available to help you with this kind of migration. Your best bet would be to stick to NHibernate. If you must change, you'll need to manually write a data migration tool yourself.

oleksii
  • 35,458
  • 16
  • 93
  • 163