score:2

Accepted answer

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
}

score:1

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.


Related Query

More Query from same tag