c# - 将 LINQ to SQL 表映射到另一个非常相似的表而不遍历每个表

Question

为这个模糊的问题道歉。在这里:我有一个由 LINQ to SQL 创建的表对象。我正在将条目从一个表复制到一个“存档”表，该表具有原始的所有列，以及一些额外的列。

遍历每个对象并手动定义映射感觉很草率:

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();

有没有聪明的方法来做到这一点？

Answer 1

考虑使用dbConext.ExecuteCommand()函数直接执行SQL，例如:

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

或者您可以使用 InsertAllOnSubmit，例如:

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

编辑 2010-04-09:

将 IQueryable 传递到构造新项(即 new NewTable())的 InsertAllOnSubmit 失败，原因如下 (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.

之所以发生错误，是因为 IQueryable 试图通过执行 SQL 查询来在缓存中创建一个项目，该查询返回由选择指定的项目。使用 AsEnumerable() 函数将 IQueryable 转换为 IEnumberable 会中断 SQL 生成。所以生成的查询只是选择项目(即 SQL 不做映射)，新项目的构建是在 Linq to SQL 逻辑之外完成的。

为了确保我使用 Northwind DB 测试了该方法，我在其中使用以下代码创建了 Categories 表的副本:

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
}