CODEWITHSUNDEEP
c#linqlinq-to-sqlsqlcommand
baddaydaddy
baddaydaddy

Reputation: 624

Fastest bulk deleting using LINQ-to-SQL

I have an old solution running on LINQ-to-SQL (DMBL), where I want to delete a large amount of rows based on id's (incl. a lot of referenced objects).

But the traditional way of using DeleteOnSubmit or DeleteAllOnSubmit is too slow (my calculation says 48 hours+):

context.Table.DeleteAllOnSubmit(entities);

I've tried to rewrite the code to use ExecuteCommand instead - it would be great to execute something like this:

DELETE dbo.Table WHERE Id in (1,2,3..)

But since LINQ-to-SQL can't handle IN statements (ref: Datacontext ExecuteCommand parameters in IN statement), I'm stuck with calling ExecuteCommand for each row like this:

var command = $"DELETE {tableName} WHERE {idColumnName} = {{0}}";
foreach (var id in idList)
{
    context.ExecuteCommand(command, id);
}

...which is close to being as slow as DeleteOnSubmit/DeleteAllOnSubmit.

The db have have been optimized with index's, so our DevOps says it's not much more that can be done there.

I know this is old-fashioned, but still hope for some useful input.

Upvotes: 2

Views: 342

Answers (0)

Related Questions