在 Linq to SQL 中执行条件批量更新
我有一个 SQL 表，用于存储带有smallint SortOrder 字段的照片。用户可以插入新照片，指定十进制排序顺序以将新记录放置在两张现有照片之间（或第一张照片之前）。 SortOrder 将存储为smallint，因此当我检测到插入将移动现有记录时，我需要更新所有受影响的照片以将 SortOrder 增加 1。
这在存储过程中很容易做到，但我正在寻找使用 Linq to SQL 实现此目的的最有效方法。如果我必须将所有记录拉到客户端，更新它们，然后提交它们，那么我将坚持使用已经运行且速度非常快的存储过程。
下面是转移记录的 T-SQL：
UPDATE Photo
SET SortOrder = SortOrder + 1
WHERE AlbumId = @AlbumId
AND SortOrder >= CEILING(@SortOrder)
有没有一种方法可以在 Linq to SQL 中进行这种批量更新，而无需获取记录？
I have a SQL table that stores photos with a smallint SortOrder field. Users can insert new photos, specifying a decimal sort order to place the new record between 2 existing photos (or before the first photo). The SortOrder will be stored as a smallint, so when I detect that an insertion will shift existing records, I need to update all affected photos to increment the SortOrder by 1.
This is easy to do in a stored procedure, but I'm looking for the most efficient way to accomplish this with Linq to SQL. If I have to pull all of the records down to the client, update them, and then submit them, then I will just stick with the stored procedure that is already working and very fast.
Here's the T-SQL that shifts the records:
UPDATE Photo
SET SortOrder = SortOrder + 1
WHERE AlbumId = @AlbumId
AND SortOrder >= CEILING(@SortOrder)
Is there a way to do this kind of bulk update in Linq to SQL without having to fetch the records?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
LINQ to SQL 不会对集合执行 CUD 语句，因此请坚持使用现有的实现，因为它将是您场景中的最佳实现。
LINQ to SQL doesn't do CUD statements for sets, so stick with your existing implementation as it would be the best in your scenario.
我在这些人的工作中取得了很大的成功：
http: //www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx
我只在开发中使用过它几次几个月了，但到目前为止，一切都还不错。
I had a lot of success with this guys work:
http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx
I've only been using it in development for a couple of months, but so far it's been pretty good.
是的，你必须拉下你的物体，操纵它们，然后把它们推回去。
存储过程是客户端在推送新照片时负责调用的东西吗？相反，您最好将其设置为触发器，这样您的应用程序就不会直接负责额外的（容易忘记的）步骤。当然，这是复杂性的权衡，也是一个偏好问题。
Yes, you'd have to pull down your objects, manipulate them, and push them back.
Is the sproc something that the client is responsible for calling when pushing a new photo? You might do well to set it up as a trigger, instead, so your application is not directly responsible for the extra (easily forgotten) step. This is a trade-off in complexity, of course, and a matter of preference.
一种选择是构建存储过程中的 sql 字符串并通过 DataContext.ExecuteQuery 方法执行它。这样做会阻止获取记录。
One option would be to build the sql string that was in the stored procedure and execute it via your DataContext.ExecuteQuery method. Doing it this way would prevent the records from being fetched.