CODEWITHSUNDEEP
sql.netlinqlinq-to-sql
Olaf
Olaf

Reputation: 10247

How to work around the fact that Linq to SQL is using separate SQL DELETE statements for each item in DeleteAllOnSubmit

When using a DeleteAllOnSubmit statement like the following (I'm omitting DataContext because I'm using LinqPad here)

var deleteUs = Foo.Take(9658);
Foo.DeleteAllOnSubmit(deleteUs);
SubmitChanges();

the resulting SQL Code looks like this:

SELECT TOP (9658) [t0].[id] FROM [Foo] AS [t0]
GO

-- Region Parameters
DECLARE @p0 Int SET @p0 = 1
-- EndRegion
DELETE FROM [Foo] WHERE [id] = @p0
GO

-- Region Parameters
DECLARE @p0 Int SET @p0 = 2
-- EndRegion
DELETE FROM [Foo] WHERE [id] = @p0
GO
... and so on

Line 2 in the c# code can't know that line 1 translates to a SELECT TOP statement and that it would be much faster to use a DELETE TOP statement.

Question: how do I work around this without resorting to SQL? Is there a way to make Linq statements translate to DELETE TOP or something similar?

Upvotes: 3

Views: 181

Answers (2)

HLGEM
HLGEM

Reputation: 96590

Why not a stored proc with a table variable as the input variable? You can call stored procs from LINQ.

I don't see any reason not to use SQl for the job it was intended to do if the ORM creates badly performing SQL.

Upvotes: 2

Wiktor Zychla
Wiktor Zychla

Reputation: 48250

A custom query generator with an extension method should allow you to perform batch deletes:

http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

I am not sure however if it supports Take operator.

Upvotes: 2

Related Questions