Linq to Sql: How to quickly clear a table

Question

To delete all the rows in a table, I am currently doing the following:

context.Entities.DeleteAllOnSubmit(context.Entities); context.SubmitChanges(); 

However, this seems to be taking ages. Is there a faster way?

Answer 1

You could do a normal SQL truncate or delete command, using the DataContext.ExecuteCommand method:

context.ExecuteCommand("DELETE FROM Entity"); 

Or

context.ExecuteCommand("TRUNCATE TABLE Entity"); 

The way you are deleting is taking long because Linq to SQL generates a DELETE statement for each entity, there are other type-safe approaches to do batch deletes/updates, check the following articles:

• Batch Updates and Deletes with LINQ to SQL
• LINQ to SQL Extension: Batch Deletion with Lambda Expression

Answer 2

Unfortunately LINQ-to-SQL doesn't execute set based queries very well.

You would assume that

context.Entities.DeleteAllOnSubmit(context.Entities);  context.SubmitChanges();  

will translate to something like

DELETE FROM [Entities] 

but unfortunately it's more like

DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ... DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ... DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ... 

You'll find the same when you try to do bulk update in LINQ-to-SQL. Any more than a few hundred rows at a time and it's simply going to be too slow.

If you need to do batch operations & you're using LINQ-to-SQL, you need to write stored procedures.