Linq to Sql: How to quickly clear a table
I like using an Extension Method, per the following:
public static class LinqExtension
{
public static void Truncate<TEntity>(this Table<TEntity> table) where TEntity : class
{
var rowType = table.GetType().GetGenericArguments()[0];
var tableName = table.Context.Mapping.GetTable(rowType).TableName;
var sqlCommand = String.Format("TRUNCATE TABLE {0}", tableName);
table.Context.ExecuteCommand(sqlCommand);
}
}
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.
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