2

I am working on application where I use EF code first (v4.4) as ORM and SqlServer as database. I also need an option to import data from another database type, which has similar data structure. During the data transfer I need to transfer identity columns with values they have, in order not to break related data.

Assuming this is the sequence of operations:

  1. fetch the data
  2. convert it to fit EF model
  3. add each entity to DbContext
  4. save changes

the problem is that it will not insert identity values as they were, due to the fact that my primary keys are set as Identity columns.

One of the ways to solve this problem is to use IDENTITY_INSERT command, but it requires to be executed together with insert queries under the same connection cycle. By connection cycle I mean that connection must not be closed during all operations (IDENTITY_INSERT and INSERT INTO).

Problem: ExecuteSqlCommand closes the connection, even if it was explicitly opened prior calling it.

A solution to this problem is to not use EF for this task, but go with ADO.NET SqlCommand. This way we have more control of when the connection will be opened / closed.

Problem: if I change my model, I also need to change the import queries, so I need to maintain database structure on two places.

Any advice how to achieve this with EF? I do not have the slightest idea why did EF team decide to always close the connection after calling ExecuteSqlCommand.

Goran
  • 6,328
  • 6
  • 41
  • 86

2 Answers2

2

In order to go with EF DbContext, here is what needs to be done:

  • Use IObjectContextAdapter to open the connection
  • Use ObjectContext's ExecuteStoreCommand to turn IDENTITY_INSERT ON/OFF
  • In the DbContext have a constructor where you have a switch to turn off DatabaseGeneratedOption.Identity for PKs when doing data conversion

As for the perfromance, the best way I found is to do a batch update for X records, and then dispose / recreate the Context. Also, set AutoDetectChangesEnabled to false

Interesting reading about performance can be found here:

Fastest Way of Inserting in Entity Framework

Community
  • 1
  • 1
Goran
  • 6,328
  • 6
  • 41
  • 86
1

I think the right way to do this would be to let go of the requirement for the Identity values to stay identical in source and target DB. If you import the data into the EF model keeping the relations intact, i.e. create a parent object and then add its children correctly, then your structure and relationships will remain unchanged.

You will never use identity values in queries anyway, so there is no point in worying about their values.

If nothing else, this will be much more maintainable.

The other option is to create the PK columns as regular insertable columns, not auto-generated. In the POCO, use the

<DatabaseGenerated(DatabaseGeneratedOption.None)>

decoration to achieve this.

GilShalit
  • 6,175
  • 9
  • 47
  • 68
  • Hi GilShalit, I have taken the time to think about your approach, and I think it is not the best way to go. If you think that I am mistaken, let me know where. Among the databases that will be converted there are some with 1milion+ records. If I have AutoDetectChangesEnabled set to True, its going to take lots of hours to complete the job. Currently I am doing updates for not more that 100 entities, after what I dispose/recreate DbContext. That approach has provided HUGE performance gain. Keeping all records in memory, while maintaining relationships, would slow down things drastically. – Goran Sep 01 '12 at 00:49
  • You do not need to keep all in memory. There is no problem to create LINQ object, and use SaveChanges once in every 10,000 additions or any such number. – GilShalit Sep 02 '12 at 06:12
  • I think it is generally true that preserving the Identity values between databases is a useless effort. – GilShalit Sep 02 '12 at 06:18
  • I agree, but it can be applied only on "small" number of records (lets say <100,000). I dont know if you have tested this, but if you want to keep changes and relationships, I think you would need to keep AutoDetectChangesEnabled to true, and that can lead to terrible performqance. Even without AutoDetextingChangesEnabled set to true, it is still very slow. – Goran Sep 02 '12 at 11:50
  • see the second option for the (really) big data cases. – GilShalit Sep 03 '12 at 05:48
  • It is not the problem on the model side, its the problem on the Sql server side, as I have explained in my question. I am going to post the findings that I found. – Goran Sep 03 '12 at 10:07