从多个表中删除（linq to sql）
我正在使用 Linq to SQL 访问我的数据库。我想要一个“删除帐户”功能，它基本上可以删除属于给定用户的所有表中的所有记录。做到这一点最有效的方法是什么？
删除必须按一定顺序进行，否则会出现外键完整性错误。我可以从数据库手动执行此操作，我的猜测是简单地在 L2S 中进行模拟，例如（伪代码）
var tb1del = From Table1 Where userId == userIdToDelete;
mydatacontext.Table1.deleteonsubmit(tb1del);
var tb1de2 = From Table1 Where userId == userIdToDelete;
mydatacontext.Table2.deleteonsubmit(tb1de2);
...
但是，我确信这不是删除记录的最有效方法。
I'm using Linq to SQL to access my database. I want to have a 'delete account' feature which basically gets rid of all records in all tables that belong to a given user. What would be the most efficient way of doing this?
The deletion has to occur in a certain order, otherwise there are foreign key integrity errors. I can do this manually from the database, and my guess would be to simply emulate that in L2S e.g. (pseudo code)
var tb1del = From Table1 Where userId == userIdToDelete;
mydatacontext.Table1.deleteonsubmit(tb1del);
var tb1de2 = From Table1 Where userId == userIdToDelete;
mydatacontext.Table2.deleteonsubmit(tb1de2);
...
However, I'm sure that's not the most efficient way of deleting the records.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
最简单的解决方案就是在数据库中使用
ON DELETE CASCADE创建一个FOREIGN KEY约束。这样您就无需担心删除顺序或其他任何问题。只需删除帐户记录即可。The simplest solution is just to create a
FOREIGN KEYconstraint withON DELETE CASCADEin the database. That way you don't need to worry about the deletion order, or anything else. Just delete the account record and you're done.如果有少量表并且属于用户的数据不多，那么您可以在 LINQ to SQL 中发出命令。我所说的“不多”是指一些几乎没有改变的记录，例如登录信息、地址信息和首选项。理想情况下，您希望将所有这些逻辑放在一起，以确保步骤始终按预期顺序执行。即使是这种情况，仍然可以使用存储过程。
另一方面，如果数据预计会很多，例如论坛用户有多个帖子、评论、收藏夹、书签等，那么我建议使用一个存储过程，该过程获取用户的 ID，并将其余部分执行为需要。令人担忧的是，针对每个匹配记录发出单个删除语句。这就是导致性能比在一个语句中删除与用户 ID 关联的所有记录的删除语句差的原因。
选项 1：使用存储过程
您可以将存储过程映射到 DataContext，这样您就可以将其用作
dc.DeleteUserAccount(userId)。 Scott Gu 有一篇优秀的博客文章可以帮助您入门：LINQ to SQL（第 6 部分 - 使用存储过程检索数据）。选项 #2：使用 Terry Aney 的批量更新代码
Terry Aney 有一篇很棒的博客文章，讨论了他开发使用 LINQ to SQL 高效地批量更新和删除功能的经验。该代码不会为每个匹配记录生成单个更新/删除语句，而是按照我们通常编写的方式为所有记录生成单个语句。帖子和代码可以在这里找到：使用 LINQ to SQL 批量更新和删除。
选项 #3：使用 DataContext.ExecuteCommand 方法
DataContext.ExecuteCommand方法可以用来直接执行SQL，如：
注意
{0}和{1} 允许输入参数化。使用它而不是连接来防止 SQL 注入攻击。如果您打算使用它，那么您不妨选择选项#1。
If there are a handful of tables and the data belonging to the user isn't much then you could issue the commands in LINQ to SQL. By "isn't much" I mean a few records that barely change, such as login info, address info, and preferences. Ideally you would want to keep all this logic together to ensure the steps are always executed in the intended order. Even if this is the case, a stored procedure may still be used.
On the other hand, if the data is expected to be a lot, such as a forum user who has multiple posts, comments, favorites, bookmarks, etc. then I recommend using a stored procedure that takes the user's ID and does the rest as needed. The concern is that a single delete statement is issued for each matching record. That is what causes poorer performance than a delete statement that deletes all records associated with the user ID in one statement.
Option #1: Use a Stored Procedure
You can map the stored procedure to your DataContext, which gives you the ability to use it as
dc.DeleteUserAccount(userId). Scott Gu has an excellent blog post that can get you started: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures).Option #2: Use Terry Aney's Batch Update Code
Terry Aney has a great blog post that discusses his experience developing the ability to batch update and delete with LINQ to SQL in an efficient manner. Instead of generating a single update/delete statement for each matching record, the code will generate a single statement for all records the way we would normally write them. The post and code can be found here: Batch Updates and Deletes with LINQ to SQL.
Option #3: Use the DataContext.ExecuteCommand Method
The DataContext.ExecuteCommand method can be used to execute SQL directly, such as:
Notice the use of
{0}and{1}which allow input to be parametrized. Use this instead of concatenating to prevent SQL injection attacks.If you're going to use this then you might as well go with option #1.
Aaronaught 是正确的，但对于那些想知道如何在 Microsoft SQL Server Management Studio 中执行此操作的人，我附上了一个屏幕截图，描述了在哪里执行此操作。
右键单击所需的表，单击
关系按钮，单击其中一个关系，然后展开表设计器下的插入和更新说明。然后选择
删除规则=Cascade即可完成。每次从该表中删除一条记录时，您选择级联的所有外键项也将被删除。Aaronaught is correct, but for those of you who wonder how to do this in Microsoft SQL Server Management Studio I have attached a screenshot which describes where to to this.
Right click your desired table, click on the
Relationshipsbutton, click on one of the relationships and expand theINSERT And UPDATE SpesificationunderTable designer.Then choose
Delete rule=Cascadeand you are done. Everytime you delete a record from this table, all the foreign key items which you have selected cascading on will be deleted aswell.