locked
Question regarding many-to-many relationship RRS feed

  • Question

  • WHAT I HAVE:

    Visual Basic 2010, .NET Framework 4.0, Entity Framework 4.0, SQL Express

    MY PROBLEM:

    I want to create a many-to-many relationship between 2 entities: Person and PhoneNumber. (The same person might use multiple phones, and multiple persons might use the same phone.) If it were that simple, I believe I would simply create an association between the 2 entities with both sides flagged as "many". The third, "intersection" table would be created automatically. However ...

    I want the intersection table (i.e., PersonPhoneNumber) to have additional fields for particular person/phone # combinations--i.e., a "Default" field to specify whether a particular phone # is the "preferred" # for a particular person. How do I set this up? I figure I'll have to explicitly create an intersection table entity, with a 1-to-many relationship from it to Person and to PhoneNumber, and with the additional field(s) in PersonPhoneNumber (Right?) Is there anything else I have to do? Walk me through anything I've missed or should be mindful of. (Also, what would a LINQ query on the intersection table look like?) 


    Robert Gustafson



    Saturday, October 5, 2013 4:57 AM

Answers

  • Hi Robert;

    Sorry could not foresee that you were using model first design, not stated in the question. There is not difference with the two tables that connect to the Junction Table. The Junction Table can be created as follows.

    1. In the designer drag an entity object onto the design surface.
    2. Change the entity name to something more suitable for your design
    3. Change the field Id name to something more suitable for that table, this field is the primary kay for the table
    4. Add only the extra fields that you wish this table to have DO NOT ADD the foreign keys from the other tables
    5. No in the designer select the Association tool from the tool box then drag from the table the ONE side to the Junction table and release and do the same from the other ONE side table to the Junction table.
    6. The navigation property name on the ONE side may not be meaningful so double click on it and give it a meaningful names
    7. Now right click on the design surface and select Generate Database from Model and save the script.
    8. Execute the script created in step 7 
    9. Done


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Proposed as answer by Fred Bao Thursday, October 10, 2013 6:25 AM
    • Marked as answer by RobertGustafson Thursday, October 10, 2013 7:52 AM
    Sunday, October 6, 2013 5:10 PM

All replies

  • Hi Robert;

    Please have a look at this article on Entity Framework: Queries involving many to many relationship tables. It talks about how to query two types of Junction Tables, one that contains just the two foreign keys and another with additional feels other then the two foreign keys. This should answer your questions.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, October 5, 2013 3:35 PM
  • Good article, with one small problem: What you've provided is based on database-first design. Please tell me if the steps can be "reversed" since I'm using model-first design. (If so, then the solution is what I thought it would be all along--but I want to be sure.)

    Robert Gustafson



    Sunday, October 6, 2013 1:21 AM
  • Hi Robert;

    Sorry could not foresee that you were using model first design, not stated in the question. There is not difference with the two tables that connect to the Junction Table. The Junction Table can be created as follows.

    1. In the designer drag an entity object onto the design surface.
    2. Change the entity name to something more suitable for your design
    3. Change the field Id name to something more suitable for that table, this field is the primary kay for the table
    4. Add only the extra fields that you wish this table to have DO NOT ADD the foreign keys from the other tables
    5. No in the designer select the Association tool from the tool box then drag from the table the ONE side to the Junction table and release and do the same from the other ONE side table to the Junction table.
    6. The navigation property name on the ONE side may not be meaningful so double click on it and give it a meaningful names
    7. Now right click on the design surface and select Generate Database from Model and save the script.
    8. Execute the script created in step 7 
    9. Done


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Proposed as answer by Fred Bao Thursday, October 10, 2013 6:25 AM
    • Marked as answer by RobertGustafson Thursday, October 10, 2013 7:52 AM
    Sunday, October 6, 2013 5:10 PM