How to effectively refresh many to many relationship

Question

Lets say I have entity A, which have many to many relationship with another entities of type A. So on entity A, I have collection of A. And lets say I have to "update" this relationships according to some external service - from time to time I receive notification that relations for certain entity has changed, and array of IDs of current related entities - some relations can be new, some existing, some of existing no longer there... How can I effectively update my database with EF ? Some ideas:

eager load entity with its related entities, do foreach on collection of IDs from external service, and remove/add as needed. But this is not very effective - need to load possibly hundreds of related entities
clear current relations and insert new. But how ? Maybe perform delete by stored procedure, and then insert by "fake" objects
```
a.Related.Add(new A { Id = idFromArray })
```

but can this be done in transaction ? (call to stored procedure and then inserts done by SaveChanges)

or is there any 3rd way ?

Thanx.

score 0 · Answer 1 · edited May 23 '17 at 12:19

Well, "from time to time" does not sound like a situation to think much about performance improvement (unless you mean "from millisecond to millisecond") :)

Anyway, the first approach is the correct idea to do this update without a stored procedure. And yes, you must load all old related entities because updating a many-to-many relationship goes only though EFs change detection. There is no exposed foreign key you could leverage to update the relations without having loaded the navigation properties.

An example how this might look in detail is here (fresh question from yesterday):

Selecting & Updating Many-To-Many in Entity Framework 4

(Only the last code snippet before the "Edit" section is relevant to your question and the Edit section itself.)

For your second solution you can wrap the whole operation into a manually created transaction:

using (var scope = new TransactionScope())
{
    using (var context = new MyContext())
    {
        // ... Call Stored Procedure to delete relationships in link table

        // ... Insert fake objects for new relationships

        context.SaveChanges();
    }
    scope.Complete();
}

score 0 · Accepted Answer · answered Oct 18 '11 at 21:18

Ok, solution found. Of course, pure EF solution is the first one proposed in original question.

But, if performance matters, there IS a third way, the best one, although it is SQL server specific (afaik) - one procedure with table-valued parameter. All new related IDs goes in, and the stored procedure performs delete and inserts in transaction.

Look for the examples and performance comparison here (great article, i based my solution on it):

http://www.sommarskog.se/arrays-in-sql-2008.html

How to effectively refresh many to many relationship

2 Answers2