3

I need to copy data from one database to another with EF. E.g. I have the following table relations: Forms->FormVersions->FormLayouts... We have different forms in both databases and we want to collect them to one DB. Basically I want to load Form object recursively from one DB and save it to another DB with all his references. Also I need to change IDs of the object and related objects if there are exists objects with the same ID in the second database.

Until now I have following code:

Form form = null;
using (var context = new FormEntities())
        {
            form = (from f in context.Forms
                        join fv in context.FormVersions on f.ID equals fv.FormID
                        where f.ID == 56
                        select f).First();
        }

        var context1 = new FormEntities("name=FormEntities1");
        context1.AddObject("Forms", form);
        context1.SaveChanges();

I'm receiving the error: "The EntityKey property can only be set when the current value of the property is null."

Can you help with implementation?

avs099
  • 10,937
  • 6
  • 60
  • 110
zosim
  • 2,959
  • 6
  • 31
  • 34

3 Answers3

4

The simplest solution would be create copy of your Form (new object) and add that new object. Otherwise you can try:

  1. Call context.Detach(form)
  2. Set form's EntityKey to null
  3. Call context1.AddObject(form)
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • I have tried your solution. It works fine, but only for Form object. When I detach form, I loose all related objects. So If Form has 10 FormVersions and I do dettach, form has 0 FormVersions. I would like to achieve to copy all hierarchy, not only root object. – zosim Aug 03 '11 at 12:22
  • 1
    Yes it is exactly what happens and there is no solution for that except creating complete copy of the Form and all its relations (the easiest solution is to serialize and deserialize Form to get complete clone - but Form must be serializable). – Ladislav Mrnka Aug 03 '11 at 12:40
  • thanks. the serialization is a solution for me. But in the default template for generation EF model there is [XmlIgnoreAttribute()] attribute for entity collection. It meeans, that related objects are not serialized. Is there any option how to serialize also related objects without modifiyng template? – zosim Aug 05 '11 at 14:56
3

I would first second E.J.'s answer. Assuming though that you are going to use Entity Framework, one of the main problem areas that you will face is relationship management. Your code should use the Include method to ensure that related objects are included in the results of a select operation. The join that you have will not have this effect.

http://msdn.microsoft.com/en-us/library/bb738708.aspx

Further, detaching an object will not automatically detach the related objects. You can detach them in the same way however the problem here is that as each object is detached, the relationships that it held to other objects within the context are broken.

Manually restoring the relationships may be an option for you however it may be worthwhile looking at EntityGraph. This framework allows you to define object graphs and then perform operations such as detach upon them. The entire graph is detached in a single operation with its relationships intact.

My experience with this framework has been in relation to RIA Services and Silverlight however I believe that these operations are also supported in .Net.

http://riaservicescontrib.codeplex.com/wikipage?title=EntityGraphs

Edit1: I just checked the EntityGraph docs and see that DetachEntityGraph is in the RIA specific layer which unfortunately rules it out as an option for you.

Edit2: Alex Jame's answer to the following question is a solution to your problem. Don't load the objects into the context to begin with - use the notracking option. That way you don't need to detach them which is what causes the problem.

Entity Framework - Detach and keep related object graph

Community
  • 1
  • 1
Scott Munro
  • 13,369
  • 3
  • 74
  • 80
1

If you are only doing a few records, Ladislav's suggestion will probably work, but if you are moving lots of data, you should/could consider doing this move in a stored procedure. The entire operation can be done at the server, with no need to move objects from the db server, to your front end and then back again. A single SP call would do it all.

The performance will be a lot better which may or may not not matter in your case.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
  • thanks for reply. Performance is no important for me in this case. More important is a big hierarchy of related tables which I want to copy. I have write in my post only three tables for example, but there is almost 20 tables: Forms->FormVersions->FormLayouts->LayoutRows->LayoutColumns->....n. I can do it in stored procedure, but I will need to manually traverse all related tables. I'm discovering if EF can do it for me. – zosim Aug 03 '11 at 12:29