使用 linq-to-sql 批量插入

问题描述

我有一个类似这样的查询:

I have a query that looks like this:

using (MyDC TheDC = new MyDC())
{
   foreach (MyObject TheObject in TheListOfMyObjects)
   {
      DBTable TheTable = new DBTable();

      TheTable.Prop1 = TheObject.Prop1;
      .....
      TheDC.DBTables.InsertOnSubmit(TheTable);

   }
   TheDC.SubmitChanges();
}

这个查询基本上使用 linq-to-sql 将一个列表插入到数据库中.现在我在网上看到 L2S 不支持批量操作.我的查询是通过一次插入每个元素还是在一次写入中插入所有元素?

This query basically inserts a list into the database using linq-to-sql. Now I've read online that L2S does NOT support bulk operations. Does my query work by inserting each element at a time or all of them in one write?

感谢您的澄清.

推荐答案

术语Bulk Insert 通常指的是 SQL Server 特定的基于超快速 bcp 的 SqlBulkCopy 实现.它建立在 IRowsetFastLoad.

The term Bulk Insert usually refers to the SQL Server specific ultra fast bcp based SqlBulkCopy implementation. It is built on top of IRowsetFastLoad.

Linq-2-SQL 不会在任何条件下使用这种机制实现插入.

Linq-2-SQL does not implement insert using this mechanism, under any conditions.

如果您需要将数据批量加载到 SQL Server 中并且需要它的速度，我建议您使用 SqlBulkCopy 手动编码.

If you need to bulk load data into SQL Server and need it to be fast, I would recommend hand coding using SqlBulkCopy.

Linq-2-SQL 将尝试执行一些优化以加速多次插入，但是它仍然无法满足许多微 ORM(即使我知道没有实现 SqlBulkCopy 的微 ORM)

Linq-2-SQL will attempt to perform some optimisations to speed up multiple inserts, however it still will fall short of many micro ORMs (even though no micro ORMs I know of implement SqlBulkCopy)