Using LINQ to SQL to copy between databases.

Question

Given two databases of identical schema, is there any easy way to transfer records between the two? I'm using LINQ to SQL as I need to perform a few additional operations along the way and having objects representing the records I'm transferring makes this much easier. This is a small-scale helper app, so SSIS is probably overkill and SQLBulkCopy doesn't allow me to easily interrogate objects en route.

What I'd like to do is this:

public static void TransferCustomer
                     (DBDataContext DCSource, 
                      DBDataContext DCDest, 
                      int CustomerID)
{
  Customer customer;
  using (DCSource = new DBDataContext(SourceConnStr))
  {
    customer = DCSource.Customers
                       .Where(c => c.CustomerID == CustomerID)
                       .Single();
  }

  using (DCDest = new DBDataContext(DestConnStr))
  {
    DCDest.Customers.InsertOnSubmit(customer);
    DCDest.SubmitChanges();
  }
}

But for obvious reasons, that throws an exception with the message:

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

I can get round this by doing a shallow copy of the object like this:

public static Customer Clone(this Customer customer)
{
  return new Customer()
  {
    Name = customer.Name,
    Email = customer.Email
    etc...
  };
}

and then inserting the cloned item. If I only copy the fields in question, and not any references representing table relationships this works fine. It's a bit long-winded however and the way I'm doing it needs me to manually assign every field in the Clone method. Could anyone suggest any ways of making this easier? The two things I'd like to know are:

1. Can LINQ to SQL do this in an easier way,
2. Is there a nice way of using Reflection to make a shallow copy which will insert?

Many thanks!

Answer 1

Is this purely an academic exercise?

If not i would seriously think about using SSIS: http://msdn.microsoft.com/en-us/library/ms141026.aspx instead, or failing that Rhino ETL: http://ayende.com/Blog/archive/2008/01/16/Rhino-ETL-2.0.aspxfor transfering data between databases.[link text][1]

Answer 2

Here's a way to shallow clone LINQ objects (omitting the primary key): Copy Linq Objects.

It could be tweaked to use the meta-model if you aren't using attributes. Then all you'd need is 2 data-contexts (one source, one destination).

Alternatively, you could look at SqlBulkCopy - see Copy from one database table to another C#

Answer 3

Why use LINQ to do this? It seems a bit of waste for a framework to get in the way when you can make use of SQL Server's DTS.

Answer 4

I use the DataContractCloner to detach LINQ-to-SQL entities:

/// <summary>
/// Serializes the entity to XML.
/// </summary>
/// <returns>String containing serialized entity.</returns>
public string Serialize()
{
    return DataContractCloner.Serialize<Organization>(this);
}

/// <summary>
/// Deserializes the entity from XML.
/// </summary>
/// <param name="xml">Serialized entity.</param>
/// <returns>Deserialized entity.</returns>
public static Organization Deserialize(string xml)
{
    return DataContractCloner.Deserialize<Organization>(xml);
}

All LINQ-to-SQL entities are already annotated, so this creates an attachable clone that you can later re-attach with a few caveats. First, foreign key relationships will be resolved, so you must insert in order of dependency, and if you have circular relationships you'll have to remove the foreign keys from your schema and re-apply after the transfer. Second, LINQ-to-SQL does not perform an identity insert, so if you are using auto-increment identity columns you're going to have problems on submit. I haven't had success with forcing the DataContext to enable identity insert, but YMMV.

Answer 5

You might like to consider using the SQLBulkCopy class instead. You can find a code project article about it here: http://www.codeproject.com/KB/database/SqlBulkCopy.aspx. That would be a quicker way to do it, but I guess it will require you to create the queries yourself instead of letting LINQ-to-SQL do it.

Answer 6

Try this

....

using (DCDest = new DBDataContext(DestConnStr)) { DCDest.Customers.InsertOnSubmit(customer.ToList());

DCDest.SubmitChanges();

}