Using Serialization to copy entities between two ObjectContexts in Entity Framework

Question

Preface

I know this is probably already regarded as crazy question, but I am looking for the most educated advice and PROVEN recommendation on how to proceed with copying ALL data (i.e., all entities and relationships) from an ObjectContext to a newly created ObjectContext backed in a different store. See "Cloning" EntityConnections and ObjectContexts in Entity Framework to check out how I'm setting this up.

---

Intro

I've seen Cloning data on Entity Framework, but:

1. I'm looking for the whole enchilada, i.e., the whole object graph: all entities & relationships. Later on, I'll go for a more fined-grained selection of what parts of the object graph)

2. As per my updates below, I haven't made serialization to work, but only on special cases. I feels like it really be a not-so-complex task, but it's been surprisingly challenging. I definitely need some insight on how to make it work.

---

Step 1

OK, this is what I've tried so far.

I am aware that when I use the Detach/Attach power duo, relationships are kaput—and I ACTUALLY want to preserve the whole object graph.

Therefore, I was thinking of loading the root entities with the MergeOption.NoTracking option:

var serializer = new DataContractSerializer(typeof(Root));

var query = (ObjectQuery<Root>) sourceContext.Roots
    .Include(d => d.Children.Select(c => c.MoreChildren.Select(r => r.EvenMoreChildren)))
    .Include(d => d.Children.Select(c => c.MoreChildren.Select(r => r.MoreAndMoreChildren)))
    .Include(d => d.Children.Select(c => c.MoreChildren.Select(r => r.Shizzle)));

foreach (var d in query.Execute(MergeOption.NoTracking)) {
    //sourceContext.Detach(d); // not needed
    Print(d);
    using (var ios = new MemoryStream()) {
        serializer.WriteObject(ios, d);
        ios.Seek(0, SeekOrigin.Begin);
        var dd = (Root) serializer.ReadObject(ios);
        //Console.WriteLine(string.Join(",", dd.EntityKey.EntityKeyValues.Select(k => k.Key + "=" + k.Value)));
        targetContext.Roots.AddObject(dd);
    }
}

Given that I am loading entities as non-tracked, the I don't need to call sourceContext.Detach(d) anymore.

The Print method simply prints the child-object tree, and it shows that up to that point things are going well (I won't show it here cuz it's huge and irrelevant).

However, now the whole thing is blowing @ serializer.WriteObject(ios, d) with the following message:

"When an object is returned with a NoTracking merge option, Load can only be called when the EntityCollection or EntityReference does not contain objects."

(Which kinda makes sense, because the serializer is probably trying to lazy load related entities.)

Remember, if I don't use NoTracking, I have to detach the entities but then I would lose my relationships...

---

Step 2

Of course I've tried setting sourceContext.ContextOptions.LazyLoadingEnabled = false just before executing the serializing loop, and that fixes the error above, but that results in the infamous:

"The object could not be added or attached because its EntityReference has an EntityKey property value that does not match the EntityKey for this object."

Also, remember I still cannot uncomment the sourceContext.Detach(d) since I loaded the roots with NoTracking...

---

Step 3

I've tried setting the EntityKey = null before serialization and even after deserialization on the cloned entity... all to no avail:

sourceContext.ContextOptions.LazyLoadingEnabled = false;

foreach (var d in query.Execute(MergeOption.NoTracking)) {
    //sourceContext.Detach(d);
    Print(d);
    d.EntityKey = null;
    using (var ios = new MemoryStream()) {
        serializer.WriteObject(ios, d);
        ios.Seek(0, SeekOrigin.Begin);
        var dd = (Root) serializer.ReadObject(ios);
        if (dd.EntityKey != null) {
            Console.WriteLine("Deserialized EntityKey: {0}", string.Join(",", dd.EntityKey.EntityKeyValues.Select(k => k.Key + "=" + k.Value)));
            dd.EntityKey = null;
        }
        targetContext.Roots.AddObject(dd);
    }
}

What's fishy is that I don't even know what bloody "object" is the exception above talking about.

Am I really, really, really, really DOOMED in my attempt to have a purely EF approach to solve this problem???

Am I completely DOOMED?!?!?! :(

Answer 1

@khovanskiiªn - here's the thing. Despite what you say about the wonderfulness of EF, it's not designed to do this. You can wax poetic all day about the wonders of a hammer, but it won't help you if your problem requires a wrench.

There are lots of solutions out there that will do what you want, but don't use EF and do it on the SQL side, it even has a fancy name. It's called ETL or Extract/Transform/Load, it's sole purpose is to take data from one set of tables and move it to another, possibly massaging the data in between.

You can, in fact, integrate these tools with client code seamlessly. For example, an SSIS package can be executed from client code, and parameters passed to it to control which data is worked on.

The fact of the matter is, Entity Framework is designed to work with a single context, and it only tracks relationships and changes on a single context. As soon as you separate it, it loses that information.

What's more, Entity framework is horrendously inefficient for this kind of thing because EF works on single entities, rather than bulk/batch operations. If you have 1 million records, it could take all day to do a single operation, whereas a sql-side batch operation could take a few minutes.

Do yourself a favor, look into SSIS (Sql Server Integration Services).