是否有任何选项可以使用实体框架（sql server 2008 r2 目标）批量插入多个相关表？
有一些现有选项可用于使用 EF 实体批量插入到单个表中。具体来说，这个SO问题并使用来自 David Browne 的此类。
然而，在尝试将行批量插入到父表和子表中的情况下，在相同的便利级别上没有任何选项会跳出来。
我能想到的“黑客”（但我希望至少有一个更好的选择）包括：
- 在插入之前生成 PK 并设置 FK（在这种情况下，我们知道没有其他东西同时插入） ，然后对父行进行批量插入（如有必要，在父插入期间关闭 IDENTITY_INSERT）
- 批量插入（使用链接的 SO 问题的方法）父行，选择它们（足够的列来识别哪个父行是其中），生成子行，批量插入那些
- 生成在单个批次中插入所有行所需的sql，执行每个父行，然后执行所有相关子行，使用@@identity填写子插入的FK
“预生成PK” Values 的方法（我实际上没有尝试过）似乎很好，但很脆弱（在操作期间至少不需要向父级插入其他内容）并且取决于空表或事先选择 max(pk)+1 。
由于 SqlBulkCopy 似乎是围绕插入构建的一次一个表（如 bcp），任何仍然让 sql 生成 PK/identity 列的东西似乎都是围绕“下拉”到 ado.net 并构建 sql 来构建的。
除了“生成大量 sql”之外，还有我缺少的选项吗？如果没有，是否有一些东西已经生成了用于批量插入相关表的 sql？
谢谢！！
There are existing options for doing bulk insert into a single table with EF entities. Specifically, this SO question and using this class from David Browne.
In the case of trying to bulk insert rows into both a parent and child table, however, nothing jumps out as an option at that same level of convenience.
The 'hacks' I can think of (but I'm hoping there's at least one better option out there) include:
- generate the PK's and set the FK's before insert (in this scenario, we know nothing else is inserting at the same time), then do the bulk inserts of both (turning off IDENTITY_INSERT during the parent insert if necessary)
- bulk insert (using the linked SO question's approach) the parent rows, select them (enough columns to identify which parent row is which), generate child rows, bulk insert those
- generate the sql necessary to insert all the rows in a single batch, doing each parent and then all related children, using @@identity to fill in the FK for the child inserts
The 'pregenerate PK values' approach (I haven't actually tried it) seems fine, but is fragile (requires no other inserts to at least parent during the operation) and depends on either an empty table or selecting max(pk)+1 beforehand.
Since SqlBulkCopy seems to be built around inserting a table at a time (like bcp), anything that still lets sql generate the PK/identity column would seem to be built around 'dropping down' to ado.net and building the sql.
Is there an option outside of 'generate the tons of sql' that I'm missing? If not, is there something out there that already generates the sql for mass-insert into related tables?
Thanks!!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
任何外键约束的第一条规则是，在插入外键表之前，它必须作为主键或唯一约束存在于另一个表中。
当您一次添加几行（传统事务处理环境）时，这非常有用。但是，您正尝试同时批量插入两者。我将其称为批处理。基本上，父表上的批量更新锁将阻止子表读取它以检查 fk 链接是否有效。
我想说你的 2 个选项是 1.) 完全保留 fk 或 2.) 在批量插入之前将 fk 设置为 nocheck，然后在批量插入完成后使用 alter table 打开检查。
The first rule of any foreign key constraint is that it must exist, as a primary key or unique constraint, in another table before inserted into the foreign key table.
This works great when you are adding a few rows at a time (traditional transaction processing environment). Howevere, you are trying to bulk insert into both at the same time. I'd term this as batch processing. Basically, the bulk update lock on the parent table is going to block the child table from reading it to check that the fk linkage is valid.
I'd say your 2 options would be 1.) leave the fk out entirely or 2.) Set the fk as nocheck before the bulk insert, then turn the check on after the bulk insert is complete with an alter table.