Many to Many relationships are defined in a database using a join table.
Depending on the schema of that join table, they are represented differently in an Entity Framework EDM. The Person...PersonStores...StoreLocations becomes a clean *:* relationship between Person and Store. Person has an EntityCollection of stores and vice versa. The join table is hidden in the mappings. However the Person...FavoriteBreweries...Brewery relationship includes the FavoriteBreweries join entity.
The reason that these are different is because the PersonStores table contains only the PrimaryKeys of the joined tables, whereas the FavoriteBreweries table contains the Primary Keys as well as another column, DateAdded. The EDM is not able to hide this join table because it won't know how to deal with the "extra" property.
When working with the simple *:* entities, you can query, update, insert and delete the involved entities without having to worry about the join table.
Here's an example of creating a relationship between an existing Person and an existing Store.
//grab a random person and random store from the db
Person person = context.People.First();
Store store = context.StoreLocations.First();
person.FavoriteStores.Add(store);
It's not quite as simple when there is a join entity involved.
Here is an example of creating a relationship between an existing Person and an existing Brewery
//grab a random person and a random brewery from the db Person person = context.People.First(); Brewery brewery = context.Breweries.First(); //create new favorite brewery and set it's person and brewery FavoriteBrewery fb = new FavoriteBrewery();fb .DateAdded = DateTime.Now; fb.Person = person; fb.Brewery = brewery; context.AddToFavoriteBreweries(fb);
This code makes sense now that it's spelled out, but it took a few tries before I figured out the pattern.
You can also use new entities for person/store
Person newPerson = Person.CreatePerson(0, "Julie", "Lerman"); Store newStore = Store.CreateStore(0, "Burlington Vermont"); newPerson.FavoriteStores.Add(newStore); context.SaveChanges();
or person/brewery,
//Create new person and new brewery and then new //favorite brewery between them Person newPerson = Person.CreatePerson(0, "Julie", "Lerman"); Brewery newbrewery=Brewery.CreateBrewery(0,"Magic Hat"); FavoriteBrewery fb = new FavoriteBrewery();
fb.DateAdded = DateTime.Now;fb.Person = newPerson; fb.Brewery = newbrewery; context.AddToFavoriteBreweries(fb);
Just make sure that any required EntityReferences (FKs) for those new entities are spelled out before you call SaveChanges. For example, if the person has a 1:1 relationship with PersonalDetails (e.g., an entity that contains things like Birthday and shoe size), then the constraints will require that the new person has a related PersonalDetails record as well. Although this has nothing to do with the *:* relationship, it could easily trip you up when doing an insert like this.
