Can I use filtered explicit loading when REMOVING a many-to-many relationship?

Question

WHAT I HAVE:

Visual Basic 2015, Entity Framework 6.1.3

MY PROBLEM:

(Bear with me for a moment.) It's important for EF programmers to know that SaveChanges, as its name suggests, looks for changes to an entity's properties since EF last "looked at" the entity (i.e., using Attach, Entry(...).State, DetectChanges, or a previous SaveChanges)--and modifies only what's been altered. It does not save the "absolute", at-the-moment state of the entity. That's why, when removing a (many-to-many) relationship from a collection navigation property, one must do one of the following:

1. If the primary entity is attached, then first load the entire collection (i.e., using Entry(...).Collection(...).Load or a query using Include) before Removing the relationship from the entity and doing SaveChanges.
2. If the primary entity is unattached, then first "Add" the relationship to the primary entity, formally attach that entity (using Attach or Entry(...).State = Unchanged), and finally Remove the relationship from the entity and do SaveChanges. This is a delete-without-loading trick. 

(One should also make sure that the related, "secondary" entity is attached before Removing the relationship between it and the primary one.)

My question is as follows:

If the primary entity is attached, then can I pre-load just the secondary entity/entities that is/are to be Removed (instead the entire collection), using the filtered explicit loading technique, before Removing? This would be a third, "in-between" approach; it's always convenient to load only the minimum data needed for an operation. 

That is, will this code work?

Imports System.Data.Entity

'   remove relationship between e1 and e2
Dim e1 As Entity1 = attached/queried value
Dim e2 As Entity2 = attached/queried value
'   load only item(s) to be removed from collection
Context.Entry(e1).Collection(Function(c) e1.Entity2).Query().Where(Function(d) d.Id = e2.Id).Load()
e1.Enity2.Remove(e2)
Context.SaveChanges()

I think I know the answer, but give it to me anyway, just in case. (I'll mark this post as answered thereafter.)

PS. BTW, it's also important, when doing any kind of data changes, to make sure there's only 1 instance of a given entity item that's Attached to the context at the time when doing SaveChanges; otherwise, EF will get confused and do the wrong thing! (If there are multiple instances of the same data--i.e., from different queries--then detach, using Entry(...).State = Detached, all but 1.) I learned a lot of these rules through trial and error with a sample model!

Answer 1

Hi RobertGustafson,

>>Can I use filtered explicit loading when REMOVING a many-to-many relationship?

You don't need to use filtered explicit loading when removing a many-to-many relationship, just like this:

 Using db As New EFContext

            Dim e1 As Entity1 = db.Entity1.Find(1)
            Dim e2 As Entity2 = db.Entity2.Find(2)

            'db.Entry(e1).Collection(Function(c) e1.Entity2).Query().Where(Function(d) d.Id = e2.Id).Load()


            e1.Entity2.Remove(e2)
            db.SaveChanges()

        End Using

For more information about many-to-many relationship, please refer to:

http://www.entityframeworktutorial.net/delete-many-to-many-entities-in-entity-framework.aspx

Best regards,

Cole Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Are you saying that it's not necessary to load a secondary entity (e2) into the primary entity's navigation-property collection (e1.Entity2) before Removing it? Because every time I try Remove to e2, it needs to be Loaded into the collection first or SaveChanges doesn't notice the "change". I know I can Remove e2 if I load the entire collection, as in the following:

'   load entire collection, then remove e2
db.Entry(e1).Collection(Function(e) e.Entity2).Load()
e1.Remove(e2) : db.SaveChanges()

>>>So I need to know if I can also opt to load just e2 into the collection, using the following:

'   load just e2 into collection, then remove it
db.Entry(e1).Collection(Function(e) e.Entity2).Query().Where(Function(d) d.Id = e2.Id).Load()
e1.Remove(e2) : db.SaveChanges()

If it's possible to simply query for e1 and e2 and do a Remove without any Load at all--as you demonstrated in your reply above--that's a new ability I'm not aware of. (Note that I have EF 6.1.3 and VB 2015.)

Robert Gustafson

PS. I got the first lambda wrong in my original expression; it should have been Function(e) e.Entity2, like above in this reply.

PPS. BTW, it's also possible to do a "loadless" Remove if e1 is detached:

e1.Entity2.Add(e2)   ' pre-populate e2 into collection of stub entity e1
db.Entry(e1).State = Unchanged   ' Attach e1 with e2 present in collection
e1.Entity2.Remove(e2)   ' explicitly remove e2 from collection
db.SaveChanges()

That's a little different than the situation I'm asking about, though.