How do you submit objects with a many to many relationship using the Entities Framwork?

Question

This is a follow up question to this question I asked yesterday. I've tracked down exactly the issue I think is causing me grief and I think I can explain the situation with more clarity.

I just can't seem to figure out how to write objects with a many to many relationship to a SQL database using the Entities Framework in a C# ASP.NET MVC 2 Project.

Here is what I tried:

I have a very simple database with only two tables. Hobbies with columns name and description and People with columns for first and last names. I create a default .edmx with everything selected. (Both also use an int as a primary key (HobbieID, PersonID) which I set in the database designed to be auto incremented.)

I can then use the following code successfully:

SampleEntities entities = new SampleEntities();
Hobby h1 = new Hobby() { Name = "Tennis", Description = "Not golf" };
Person p1 = new Person() { First = "Jack", Last = "Black" };
entities.Hobbies.AddObject(h1);
entities.People.AddObject(p1);
entities.SaveChanges();

Sure enough both are added to the database correctly. Now where it breaks.

I add another table the database called JunctionHobbiesPeople which has two columns (HobbieID, PersonID) which I make foreign keys using the relationships pop up window in the database designer. I then add the JunctionHobbiesPeople table to the entities designer (via right click update, using the GUI) and it graphically shows a correct many to many relationship.

Then I try the following code:

SampleEntities entities = new SampleEntities();
Hobby h1 = new Hobby() { Name = "Tennis", Description = "Not golf" };
Person p1 = new Person() { First = "Jack", Last = "Black" };
p1.Hobbies.Add(h1);
entities.People.AddObject(p1);
entities.SaveChanges();

The first error I get is about DefiningQuery. I can fix that by just deleting all of the DefiningQuery elements from the xml in the code behind the .edmx file. Speaking of which, as a side question, what is that element for - should I not delete it?

After that error goes away I get the following error:

InnerException  {"Invalid object name 'ModelStoreContainer.JunctionHobbiesPeople'."}    System.Exception {System.Data.SqlClient.SqlException}

Any idea's what I'm doing wrong? Do I have to inset them in a special order? How can I get many to many relationships to correctly be written to the database?

Thanks for your help!!!

Answer 1

I can help you if you want to share your edmx file. Copy the entire xml so i can look at it and tell u what's wrong with it.