C# 使用 linq-to-sql 批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9220901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
bulk insert with linq-to-sql
提问by frenchie
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();
}
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?
此查询基本上使用 linq-to-sql 将列表插入到数据库中。现在我在网上看到 L2S 不支持批量操作。我的查询是通过一次插入每个元素还是在一次写入中插入所有元素？
Thanks for the clarification.
感谢您的澄清。
采纳答案by Sam Saffron
The term Bulk Insertusually refers to the SQL Server specific ultra fast bcp based SqlBulkCopyimplementation. It is built on top of IRowsetFastLoad.
该术语Bulk Insert通常是指 SQL Server 特定的基于超快速 bcp 的SqlBulkCopy实现。它建立在IRowsetFastLoad 之上。
Linq-2-SQL does not implement insert using this mechanism, under anyconditions.
在任何情况下，Linq-2-SQL 都不会使用此机制实现插入。
If you need to bulk load data into SQL Server and need it to be fast, I would recommend hand coding using SqlBulkCopy.
如果您需要将数据批量加载到 SQL Server 中并且需要它的速度，我建议您使用 SqlBulkCopy 手动编码。
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)
Linq-2-SQL 将尝试执行一些优化以加速多个插入，但是它仍然无法满足许多微 ORM（即使我知道没有实现 SqlBulkCopy 的微 ORM）
回答by Chris Sainty
It will generate a single insert statement for every record, but will send them all to the server in a single batch and run in a single transaction.
它将为每条记录生成一个插入语句，但会将它们全部发送到单个批处理中并在单个事务中运行。
That is what the SubmitChanges() outside the loop does.
这就是循环外的 SubmitChanges() 所做的。
If you moved it inside, then every iteration through the loop would go off to the server for the INSERT and run in it's own transaction.
如果你把它移到里面，那么循环中的每次迭代都会转到服务器进行 INSERT 并在它自己的事务中运行。
I don't believe there is any way to fire off a SQL BULK INSERT.
我不相信有任何方法可以触发 SQL BULK INSERT。
回答by Andrew Mao
I modified the code from the following link to be more efficient and used it in my application. It is quite convenient because you can just put it in a partial class on top of your current autogenerated class. Instead of InsertOnSubmitadd entities to a list, and instead of SubmitChangescall YourDataContext.BulkInsertAll(list).
我修改了以下链接中的代码以提高效率并在我的应用程序中使用它。这非常方便，因为您可以将它放在当前自动生成的类之上的部分类中。而不是InsertOnSubmit将实体添加到列表中，而不是SubmitChanges调用YourDataContext.BulkInsertAll(list).
http://www.codeproject.com/Tips/297582/Using-bulk-insert-with-your-linq-to-sql-datacontex
http://www.codeproject.com/Tips/297582/Using-bulk-insert-with-your-linq-to-sql-datacontex
partial void OnCreated()
{
CommandTimeout = 5 * 60;
}
public void BulkInsertAll<T>(IEnumerable<T> entities)
{
using( var conn = new SqlConnection(Connection.ConnectionString))
{
conn.Open();
Type t = typeof(T);
var tableAttribute = (TableAttribute)t.GetCustomAttributes(
typeof(TableAttribute), false).Single();
var bulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = tableAttribute.Name
};
var properties = t.GetProperties().Where(EventTypeFilter).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
table.Columns.Add(new DataColumn(property.Name, propertyType));
}
foreach (var entity in entities)
{
table.Rows.Add(
properties.Select(
property => property.GetValue(entity, null) ?? DBNull.Value
).ToArray());
}
bulkCopy.WriteToServer(table);
}
}
private bool EventTypeFilter(System.Reflection.PropertyInfo p)
{
var attribute = Attribute.GetCustomAttribute(p,
typeof(AssociationAttribute)) as AssociationAttribute;
if (attribute == null) return true;
if (attribute.IsForeignKey == false) return true;
return false;
}
回答by atik sarker
LINQ Single Insert from List:
LINQ 单从列表插入：
int i = 0;
foreach (IPAPM_SRVC_NTTN_NODE_MAP item in ipapmList)
{
++i;
if (i % 50 == 0)
{
ipdb.Dispose();
ipdb = null;
ipdb = new IPDB();
// .NET CORE
//ipdb.ChangeTracker.AutoDetectChangesEnabled = false;
ipdb.Configuration.AutoDetectChangesEnabled = false;
}
ipdb.IPAPM_SRVC_NTTN_NODE_MAP.Add(item);
ipdb.SaveChanges();
}
