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

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 2

Try this

using (DCDest = new DBDataContext(DestConnStr))
{
    DCDest.Customers.InsertOnSubmit(customer.ToList());  //note the use of .ToList()
    DCDest.SubmitChanges();
}