Home

Delete many thousands of records

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?

Comments

  • 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.

  • 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?

  • 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)

Sign In or Register to comment.