Delete many records per ID - MVC C#

Question

The setup for the database I have is, Company can have many Engineer, and can be in many Territory. If I am supposed to remove a company form the list, I need to make a clean exit, so instead of deleting just the Company form the company table. I have to make sure all records are deleted everywhere. So no child/related records become orphan data.

If I am to remove just one record from all the tables, I would simply delete it using FirstOrDefault, then I could use

public void RemoveCompany(long companyId)
{
    using (var db = new BoilerServicingDbContext())
    {
        var ec = db.Engineers.FirstOrDefault(x => x.CompanyId == companyId);
        db.Engineers.Remove(ec);
        var tc = db.CompanyTerritories.FirstOrDefault(x => x.CompanyId == companyId);
        db.CompanyTerritories.Remove(tc);
        var p = db.Companies.FirstOrDefault(x => x.Id ==  companyId);
        db.Companies.Remove(p);
        db.SaveChanges();       
    }
}

However, there are more than one Engineer per company and more than one Territory per company. Is there a simple method, as in general database sense.

DELETE * FROM Engineers WHERE companyId = 1;

At this moment I do not have Cascading delete setup, so other than that. Any other options.

Thank you @CodeCaster, I will look at the SQL version after getting the EF working. Much appreciated ! — PaulFrancis, Feb 24 '15 at 14:29

VMAtm · Accepted Answer · 2015-02-24T14:32:51.640

8

Why not to use .Where() method to find all corresponding items in DB? Like this:

foreach(var ec in db.Engineers.Where(x => x.CompanyId == companyId))
{
    db.Engineers.Remove(ec);
}
// same logic here for other tables

Also consider the @workabyte answer as there is a way to implement cascade delete in your database.

edited Feb 24 '15 at 14:32

answered Feb 24 '15 at 14:21

VMAtm

27,943
17
79
125

I imagined there were to be a simpler method to delete in one line. This has done it for me though. Thank you ever so much ! – PaulFrancis Feb 24 '15 at 14:26
Also consider the @workabyte answer as there is a way to implement `cascade delete` in your database. – VMAtm Feb 24 '15 at 14:32
Absolutely. Cascade on Delete was the first option, but I was not sure if there were other possibilities. :) – PaulFrancis Feb 24 '15 at 14:35
I brought this up in the C# chatroom and the general consensus is that using an ORM to do batch operations is probably a bad idea and you should use stored commands instead. – Inspector Squirrel Feb 24 '15 at 16:31
Personally I prefer no use ORM at all :) – VMAtm Feb 24 '15 at 17:14

score 2 · Answer 2 · edited May 23 '17 at 12:24

2

One option could be cascade on delete, you have to tell EF to do this for you as it will not by default, something like this should do it. Also linked to a couple other SO answers that have similar appeal for some extra reading.

modelBuilder.Entity<Company>()
        .HasMany(b => b.Engineer)
        .WillCascadeOnDelete(true);

https://stackoverflow.com/a/9241880/546411

https://stackoverflow.com/a/14493591/546411

edited May 23 '17 at 12:24

Community

1
1

answered Feb 24 '15 at 14:21

workabyte

3,496
2
27
35

Thank you, I might make it Cascade on Delete. At this point of time, I am trying to delete through code. :) – PaulFrancis Feb 24 '15 at 14:27
2

@PaulFrancis, you would delete the parent record via code that would cause the rest to delete so that you dont have to loop through a bunch of records. Code well, glad you found an answer – workabyte Feb 24 '15 at 14:29
2

Yes, absolutely. I am just getting my teeth into C#, so learning all possibilities. Since Cascade on delete is something that would be very easy to achieve, I was thinking all other twisted ways. ;) +1 for providing me with alternate answer ! :) – PaulFrancis Feb 24 '15 at 14:31

score 2 · Answer 3 · answered Feb 24 '15 at 14:48

2

Use plain SQL if you don't mind using the table name (most efficient):

public void RemoveCompany(long companyId)
{
    using (var db = new BoilerServicingDbContext())
    {
        var engineerIds = db.Engineers
                            .Where(x => x.CompanyId == companyId)
                            .Select(x => x.Id).ToList();

        var sql  = "DELETE FROM Engineers WHERE Id IN ({0})";
        sql = string.Format(sql, string.Join(", ", engineerIds);
        db.Database.ExecuteSqlCommand(sql);
        db.SaveChanges();       
    }
}

answered Feb 24 '15 at 14:48

jgauffin

99,844
45
235
372

just curious, not attacking. can you elaborate on the efficiency of this method? – workabyte Feb 24 '15 at 15:10
2

It's not more efficient than a cascading delete, but more efficient than generating one SQL statement per delete. (will also not map the engineer object hierarchy (if any)) – jgauffin Feb 24 '15 at 15:19

VisualBean · Answer 4 · 2015-02-24T14:28:42.340

0

Take a look at this link. Its a library that extends EF and (amongst other things) provide batch update/delete methods.

In your case:

int companyid = 1;

context.Engineers.Delete(x=>x.CompanyId == companyid)
context.CompanyTerritories.Delete(x=>x.CompanyId == companyid)
context.Companies.Delete(x=>x.CompanyId == companyid)

edited Feb 24 '15 at 14:28

answered Feb 24 '15 at 14:22

VisualBean

4,908
2
28
57

score 0 · Answer 5 · answered Jul 27 '17 at 15:35

0

In EF6 it's much easier Using RemoveRange and without foreach:

db.Engineers.RemoveRange(db.Engineers.Where(x => x.CompanyId == companyId));
db.SaveChanges();

answered Jul 27 '17 at 15:35

Zerghouni Idriss

84
7

Delete many records per ID - MVC C#

5 Answers5