在 LINQ 中提交更改之前引用对象的标识
有没有办法提前知道通过InsertOnSubmit插入的记录的标识列的ID，例如在调用数据源的SubmitChanges之前？
想象一下，我正在数据库中填充某种层次结构，但我不想在每个子节点的每次递归调用上提交更改（例如，如果我有目录表和文件表，并且正在数据库中重新创建我的文件系统结构） 。
我想这样做，所以我创建一个 Directory 对象，设置其名称和属性， 然后InsertOnSubmit将其插入DataContext.Directories集合，然后在其子文件中引用Directory.ID。目前，我需要调用 InsertOnSubmit 将“目录”插入数据库，并且数据库映射填充其 ID 列。但这会创建大量事务和对数据库的访问，我想如果我批量插入，性能会更好。
我想做的是在提交更改之前以某种方式使用 Directory.ID，提前创建所有文件和目录对象，然后进行一次大型提交，将所有内容放入数据库中。我也愿意通过存储过程解决这个问题，我认为如果所有操作都直接在数据库中完成，性能会更好。
is there a way of knowing ID of identity column of record inserted via InsertOnSubmit beforehand, e.g. before calling datasource's SubmitChanges?
Imagine I'm populating some kind of hierarchy in the database, but I wouldn't want to submit changes on each recursive call of each child node (e.g. if I had Directories table and Files table and am recreating my filesystem structure in the database).
I'd like to do it that way, so I create a Directory object, set its name and attributes,
then InsertOnSubmit it into DataContext.Directories collection, then reference Directory.ID in its child Files. Currently I need to call InsertOnSubmit to insert the 'directory' into the database and the database mapping fills its ID column. But this creates a lot of transactions and accesses to database and I imagine that if I did this inserting in a batch, the performance would be better.
What I'd like to do is to somehow use Directory.ID before commiting changes, create all my File and Directory objects in advance and then do a big submit that puts all stuff into database. I'm also open to solving this problem via a stored procedure, I assume the performance would be even better if all operations would be done directly in the database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
解决这个问题的一种方法是不使用标识列。相反，构建一个 IdService，您可以在代码中使用它来在每次创建 Directory 对象时获取新的 Id。
您可以通过存储最后使用的 id 的表来实现 IdService。当服务启动时，让它获取该号码。然后，该服务可以在创建 Directory 对象时递增，然后使用运行结束时使用的新的最后 id 更新表。
或者，更安全一点，当服务启动时，让它获取最后使用的 id，然后通过添加 1000（例如）来更新表中最后使用的 id。然后让它逐渐消失。如果它使用 1000 个 id，则让它抓取下一个 1000 个并更新最后使用的 id 表。最坏的情况是你浪费了一些 id，但如果你使用 bigint，你就不会关心。
由于目录 ID 现在由代码控制，因此您可以在写入数据库之前将其与文件等子对象一起使用。
只需在 id 获取周围放置一个锁，就可以安全地跨多个线程使用。我一直在像你这样的情况下使用这个。我们跨多个线程在内存中生成大量对象并批量保存它们。
这篇博文将为您在 Linq to SQL 中保存批处理提供良好的开端。
One way to get around this is to not use an identity column. Instead build an IdService that you can use in the code to get a new Id each time a Directory object is created.
You can implement the IdService by having a table that stores the last id used. When the service starts up have it grab that number. The service can then increment away while Directory objects are created and then update the table with the new last id used at the end of the run.
Alternatively, and a bit safer, when the service starts up have it grab the last id used and then update the last id used in the table by adding 1000 (for example). Then let it increment away. If it uses 1000 ids then have it grab the next 1000 and update the last id used table. Worst case is you waste some ids, but if you use a bigint you aren't ever going to care.
Since the Directory id is now controlled in code you can use it with child objects like Files prior to writing to the database.
Simply putting a lock around id acquisition makes this safe to use across multiple threads. I've been using this in a situation like yours. We're generating a ton of objects in memory across multiple threads and saving them in batches.
This blog post will give you a good start on saving batches in Linq to SQL.
不确定是否有办法在 LINQ 中运行直接 SQL 查询，但此查询将返回指定表的当前标识值。
Not sure off the top if there is a way to run a straight SQL query in LINQ, but this query will return the current identity value of the specified table.