EF how to update many to many join table

I am having trouble getting Entity Framework 4 to handle a bulk update in a m2m join. I have several many to many joins in my model, for example something like

Practice

PracticeID PK

Name...

PracticeSpecialties (join table)

PracticeID PK

SpecialtyID PK

Specialties

SpecialtyID pk

Name...

Pretty basic and EF 4 handles it well. My problem is I need to be able to "merge" 2 or more Specialties into 1, for example "Pediatrics" and "Children" and "Adolescents" should all be the same item. So if Pediatrics has a SpecialtyID of 1 and Children = 3 and Adolescents = 9 the query should perform an update on all rows in PracticeSpecialties where SpecialtyID IN (3, 9) and change the value to 1.

I can write a stored proc that would update do this all rows in the join table containing one of the undesired SpecialtyIDs then delete all the now orphaned Specialties, but if possible I am trying to stick wi开发者_运维技巧th the EF pattern.

Any guidance is appreciated.

In entity framework you must do it in object way = you can simply modify junction table. You must work with objects in navigation properties. Your scenario will look like:

var children = context.Specialities.Include("Practices")
                      .Single(s => s.Name == "Children");
var pediatrics = context.Specialities.Include("Practices")
                        .Single(s => s.Name == "Pediatrics");

foreach (var practice in children.Practices)
{
  pediatrics.Practices.Add(practice);
}

children.Practices.Clear();
context.Specialities.DeleteObject(children);
context.SaveChanges();

You should also override Equals and GetHashCode in Practice entity and use HashSet for Speciality.Practices (in case of POCOs). It will handle duplicities for you.

Result of this will be multiple deletes and inserts to junction table. This is EF way to do that.

Do not use many-to-many relationships. The join table ends up holding more data eventually anyway. Use a one-to-many and a many-to-one combination instead. If you want, you can make a property that holds the created/modified dates in the middle entity for now.

Hope this helps.