Delete from multiple tables (linq to sql)

Question

hi,

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.

Thanks for any advice.

Answer 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:

int affectedRecords = 
    dc.ExecuteCommand("Update Person SET FirstName = {0} WHERE FirstName = {1}", "Foo", "Bar");

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.

Answer 2

The simplest solution is just to create a FOREIGN KEY constraint with ON DELETE CASCADE in 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.

Answer 3

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 Relationships button, click on one of the relationships and expand the INSERT And UPDATE Spesification under Table designer.

Then choose Delete rule = Cascade and 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.