How to delete records in a many-to-many association with a hidden intersection table?

Question

We have two tables, table1 and table2, defined in SQL, that have a many-to-many relationship.

As is the usual practice, we've implemented this by creating an intersection table, table1_table2, that contains the primary keys of table1 and table2.

And we've created 1-many FKs from table1 to table1_table2 and from table2 to table1_table2.

Then we had EF6 create an EDMX, using import from database.

The model defined two entities, table1s and table2s, with a many-to-many relationship.

That is, there is the POCO class table1 contains an ICollection table2s, and class table2 contains an ICollection table1s.

How do I clear the connection, so that I can delete all the instance of table1?

This:

using (var dbContext = new DbContext())
{
    dbContext.table1s.RemoveRange(dbContext.table1s);

    dbContext.SaveChanges();
}

Throws an exception:

System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_table1_table2_table1".

I tried nulling out the relationship, first:

using (var dbContext = new DbContext())
{
    foreach (var table1 in dbContext.table1s)
        table1.table2s = null;

    dbContext.table1s.RemoveRange(dbContext.table1s);

    dbContext.SaveChanges();
}

And I get the same exception.

So I tried removing each table2 record explicitly:

using (var dbContext = new DbContext())
{
    foreach (var table1 in dbContext.table1s)
    {
        foreach (var table2 in table1.table2.ToList()
        {
            table1.table2s.Remove(table2);
        }
    }

    dbContext.table1s.RemoveRange(dbContext.table1s);

    dbContext.SaveChanges();
}

And that seems to work, but it's so tedious that I have a hard time believing that that is the way that I'm supposed to be doing this.

Ideas?

Could have saved yourself a lot of head-scratching by not using EF :) — Charleh, May 06 '19 at 20:40
When we get the current refactoring done, we'll be able to swap out EF with Dapper or NHibernate on a class-by-class basis. But with the constant influx of urgent-but-not-important tasks, that's going to be a long slog. — Jeff Dege, May 06 '19 at 20:44
It has its places, but a lot of the time you don't need it. Way more transparent when you use something like dapper as long as it's not super complex graphs. — Charleh, May 07 '19 at 01:48

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

EF

Well, that's just the way to do it with EF.

It is not exactly too much code and can even be generalized if that is required.

If the problem is performance then it may be partially dealt with by

disabling automatic change detection
eagerly loading the navigation collections
and potentially by directly manipulating entry states.

or you could just go

Not EF

Get(more or less reliably) table name from its DbSet(or just hardcode it if that's acceptable) and delete all the offending rows with plain SQL.

Basically use the right tool for the right job.

EF is usually a decent choice for modifying complex object graphs, but on the other hand it is basically too heavy-weight for bulk record manipulations.

How to delete records in a many-to-many association with a hidden intersection table?

1 Answers1

EF

Not EF