Mapping LINQ to SQL table to another very similar table without iterating through every one

Question

Apologies for the vague question. Here it is: I have a table object created by LINQ to SQL. I am copying entries from one table to an "archive" table that has all the columns of the original, plus a couple extra ones.

It feels sloppy to iterate through every object and manually define the mapping like:

foreach (oldTable in dbContextOldTables)
{
   var newTable = new NewTable();
   newTable.TableID = oldTable.ID;
   newTable.Title = oldTable.Title;
   ... (repeat for twenty columns)
   newTable.ColumnThatIsntInOldTable = "Data that will be the same for every row";
   dbContext.NewTables.InsertOnSubmit(newTable);
} 
dbContext.SubmitChanges();

Is there a clever way to do this?

Answer 1

Consider using dbConext.ExecuteCommand() function to execute SQL directly, e.g.:

ctx.ExecuteCommand("INSERT INTO backup SELECT * FROM original");

Alternatively you could use InsertAllOnSubmit, e.g.:

var entries = dbContextOldTables.AsEnumerable().Select(x => new NewTable() { /* mapping */ });
dbContext.NewTables.InsertAllOnSubmit(entries);

Edit 2010-04-09:

Passing an IQueryable into InsertAllOnSubmit that constructs a new item (i.e. new NewTable()) fails for the following reason (source):

This check was added because it was supposed to be there from the beginning and was missing. Constructing entity instances manually as a projection pollutes the cache with potentially malformed objects, leading to confused programmers and lots of bug reports for us. In addition, it is ambiguous whether projected entities should be in the cache or changed tracked at all. The usage pattern for entities is that they are created outside of queries and inserted into tables via the DataContext and then later retrieved via queries, never created by queries.

So the error occurs because the IQueryable is trying to create an item on the cache by executing and SQL query that returns the item specified by the select. Converting the IQueryable into an IEnumberable using the AsEnumerable() function breaks the SQL generation. So the query generated just selects the item (i.e. the SQL does not do the mapping) and the construction of the new item is done outside the Linq to SQL logic.

To be sure I tested the approach using a Northwind DB in which I created a copy of the Categories table using the code below:

using (var ctx = new NorthwindDataContext()) {
    var categories = ctx.Categories;

    var catcopy = categories.Select(x => new CategoriesBackup() {
        CategoryID = x.CategoryID,
        CategoryName = x.CategoryName,
        Description = x.Description,
        Picture = x.Picture
    });
    //ctx.CategoriesBackups.InsertAllOnSubmit(catcopy);  // THIS DOES NOT WORK

    var catcopy2 = categories.AsEnumerable().Select(x => new CategoriesBackup() {
        CategoryID = x.CategoryID,
        CategoryName = x.CategoryName,
        Description = x.Description,
        Picture = x.Picture
    });
    ctx.CategoriesBackups.InsertAllOnSubmit(catcopy2);  // THIS WORKS
}

Answer 2

Why not something like(not tested or compiled):

dbContext.InsertAllOnSubmit(dbContextOldTables.Select(old=> new NewTable(){TableID = old.ID, Title = old.Title /*etc etc*/}));
dbContext.SubmitChanges(); 

Answer 3

You could use Automapper (http://automapper.codeplex.com/) or some very simple reflection code to copy the data from one object to the other so you don't need to manually write out each field.

For example, using an interface to ensure they match:-

   public interface IShared
    {
        int Prop1 {get; set;}
        string Prop2 {get; set;}
    }

    public class A : IShared
    {
        public int Prop1 {get; set;}
        public string Prop2 {get; set;}
    }

    public class B : IShared
    {
        public int Prop1 {get; set;}
        public string Prop2 {get; set;}
    }


    static void Main(string[] args)
    {
        A A = new A(){ Prop1 = 1, Prop2 = "2" };
        B B = new B();

        var properties = typeof(IShared).GetProperties();
        foreach (var prop in properties)
        {
            object currentValue = prop.GetValue(A, null);
            prop.SetValue(B, currentValue, null);
        }

        Console.WriteLine("B = " + B.Prop1 + " " + B.Prop2);
        Console.ReadKey();

Note: This does NOT handle arrays, you could extend it to do that, or your could just use Automapper.