Delete many thousands of records

kakss

Hi,

I am trying to use LINQPad to delete many thousands records from a table.

What is the best way to do it in LINQPad?

JoeAlbahari

Is the deletion predicate-based? If so, it's usually most efficient to use SQL. In LINQ-to-SQL:

this.ExecuteCommand ("delete purchase where Date < {0}", DateTime.Now.AddYears (-10));

In EF Core, replace ExecuteCommand with Database.ExecuteSqlRaw.

kakss

Hi Joe,

Yes, delete is predicate based but it has 1 million DELETE FROM TableName WHERE PK IN (a, b, c... 2 million parameters). Table has 5 million records and need to delete 2 million records.

As SQL Server has 2100 parameters limit so what is the other way I can delete it?

dickbaker

make a simple table with rows that identify those PKs that are to be deleted (if WHERE not enough)
create table Gone(PK int)
insert Gone values(1),(3),(5),(7),(11) -- etc i.e. 2M out of the 5M
delete from TableName where PK in (select PK from Gone)

kakss

Hi,

Thank you.