Removing thousands of rows from table using EF6

Question

Hi i am trying to remove 4-5k rows from table using EF6 in .net mvc application

I am using RemoveRange for that, and if its 100-200 rows it completes in few seconds. But when i try to remove few thousands records it takes a very long time (10 min +)

using (someEntites dc = new someEntites())
                    {
                        var listForRemoval = (from a in dc.someTable
                                         where a.Year == 2018 && a.month == 04
                                         select a).ToList();

                        if (listForRemoval != null)
                        {
                            dc.someTable.RemoveRange(listForRemoval);
                            dc.SaveChanges();
                        }
                    }

i tried disabling AutoDetectChangesEnabled but that did not help.

Is there any other (relativly simple) way for removing larger chunks of data from db table ?

-- p.s writing simple delete - where in sql takes a second to complete

Can you create a SQL-Statement for deleting the rows and execute the SQL-statement? Should perform much better than any EF Stuff. — user743414, May 30 '18 at 09:51
Relatively simple and fast way is to just execute "DELETE" query manually. Now you are first fetching few thousand records to client, then delete them (with inefficient query). There are also various "extensions" to EF which allow to do that with LINQ syntax. — Evk, May 30 '18 at 09:52
If you want to remove all rows... You could execute a TRUNCATE query manually. — IronAces, May 30 '18 at 09:59
i have seens solutions using the extensions and tried to avoid that for now. I have written raw querry using (ExecuteSqlCommand) and its much faster, i am just not a fan of using direct sql commands. Thanks anyways :) — Adrian, May 30 '18 at 10:00
not a Duplicate Marco, i have seen that topic and it did not solve my issue. Daniel i dont want to remove all records only selections specefied in where — Adrian, May 30 '18 at 10:01
if you still want to use linq syntax, you can take a look at 3rd party libraries like http://entityframework-plus.net/batch-delete which behind the scenes will execute `DELETE .. WHERE .. ` for you — Dmitry Pavliv, May 30 '18 at 10:04

score 2 · Accepted Answer · edited May 30 '18 at 13:00

2

Can you try like this :

using (someEntites dc = new someEntites())
{
    //var listForRemoval = (from a in dc.someTable
    //where a.Year == 2018 && a.month == 04
    //select a).ToList();

    //if (listForRemoval?.Count > 0)
    //{
    //dc.someTable.RemoveRange(listForRemoval);
    //dc.SaveChanges();
    //}

    dc.Database.ExecuteSqlCommand("DELETE FROM someTable " + 
                                  "WHERE Year = {0} " + 
                                  "AND month = {1}", 2018, 4); 
    // Executes a commande with parameters. You can add more parameters separated by ','.
}

If there are relations with other tables, you should delete them separately.

edited May 30 '18 at 13:00

Felix D.

4,811
8
38
72

answered May 30 '18 at 10:02

Coskun Ozogul

2,389
1
20
32

i think its ExecuteSqlCommand but thanks. That will have to do :) – Adrian May 30 '18 at 10:09
Sorry :) I modify my response. – Coskun Ozogul May 30 '18 at 10:09

score 1 · Answer 2 · edited May 30 '18 at 10:20

1

You could try execute raw SQL queries using Entity Framework. More details under:

https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

edited May 30 '18 at 10:20

Program.X

7,250
12
49
83

answered May 30 '18 at 09:58

Meechau

11
3

score 1 · Answer 3 · answered May 30 '18 at 09:59

1

The Approach you are using is not the best thing because you are loading all the records you want to delete in memory when you use .ToList() then you are deleting. Imagine you loaded thousands of records per each request. To achieve here is below example :

string statement= "DELETE FROM sometable WHERE year = 2018 AND month = 4";
dc.Database.SqlQuery(statement);

Full tutorial : http://www.entityframeworktutorial.net/entityframework4.3/raw-sql-query-in-entity-framework.aspx

answered May 30 '18 at 09:59

Hany Habib

1,377
1
10
19

Is it good practice to use sql embedded statements especially delete statements? – user9405863 May 30 '18 at 10:03
Another approach you can use stored procedure name as mentioned here: https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa .. – Hany Habib May 30 '18 at 10:06

Removing thousands of rows from table using EF6

3 Answers3