CODEWITHSUNDEEP
sqllinqtransactions
Snowy
Snowy

Reputation: 6122

Linq to SQL Chunked Delete or Update Operation?

I have a lot of rows to delete in a table, and using ADO.NET I might send a SqlCommand with this text:

 WHILE(1=1)  
BEGIN  
DELETE TOP(5000) FROM myTable WHERE myval=123  
 IF @@ROWCOUNT < 5000 BREAK  
END

I would wrap that in a transaction or 2 and could then delete in chunks so the DB could come up for air.

I would like to do the same thing but in C# using LINQ-to-SQL, is that possible?

Thanks.

Upvotes: 1

Views: 201

Answers (1)

James Curran
James Curran

Reputation: 103525

Unfortunately, there is no mass update or mass delete operations in Linq-to-SQL (and with developement suspended on L2S, it's unlikely there ever will be)

The best you could do is

db.myTable.DeleteOnSubmit(db.MyTable.Where(m=>m.myval ==123).Take(5000));

But I'm pretty sure that will generate a SELECT followed by 5000 DELETE statements.

Upvotes: 1

Related Questions