locked
Inserting records in table with many-to-many relationship RRS feed

  • Question

  • I am new to the Entity Framework so please bear with me..............

    I have the following tables in my database:

    Programs
    ElectricConservationProgram
    ProgramEndUseTargets
    EndUseTargets

    The Programs table has a primary key of ProgramId and Year.  The ElectricConservationProgram table has a primary key that is made up entirely of of foreign keys to the Programs table columns ProgramId and Year.  The EndUseTargets table has a single column EndUseTargetName that is the primary key.  The ProgramEndUseTargets table is a link table between ElectricConservationProgram and EndUseTargets.  Its key is made up entirely of foreign keys from ElectricConservationProgram and EndUseTargets.  Those foreign keys are ProgramId and Year from the ElectricConservationProgram table and EndUseTargetName from EndUseTargets table.

    When I use the EDM to design a model from the database, the link table ProgramEndUseTargets is completely eliminated in the model and is represented by an association between ElectricConservationProgram table and EndUseTarget table.  With this model, I don't know how to insert records into the link table using C# code because the link table is represented entirely by an association.

    I have limited ability to change the table design of the database.  It is not my design and already has much data so suggestions to change the table design to help work with Entity Framework are not going to be helpful.  Can someone please suggest how I can insert into the ProgramEndUseTargets table using C# code?

    If it is helpful, I can provide the edmx file.

    Thursday, July 15, 2010 1:36 PM

Answers

  • Hi mcgjr,

     

    Welcome to EF forum!

     

    For the many-to-many relationships, EF treats the linked tables as associations as you said.   To insert/update/delete the values in the linked table, we can perform the corresponding operations on the entities ElectricConservationProgram and EndUseTargets. 

     

    Insert operation:

    ======================================================================================

    MyEntities context = new MyEntities();

    var eProgram = context.ElectricConservationPrograms.First();

    var pTargets = context.ProgramEndUseTargets.First();

     

    eProgram.ProgramEndUseTargets.Add(pTargets);

     

    context.SaveChanges();

    ======================================================================================

     

    Delete operation:

    ======================================================================================

    MyEntities context = new MyEntities();

    var eProgram = context.ElectricConservationPrograms.First();

    var pTargets = context.ProgramEndUseTargets.First();

     

    eProgram.ProgramEndUseTargets.Remove(pTargets);

     

    context.SaveChanges();

    ======================================================================================

     

    Update operation:

    The update operation for associations is updated by first remove the association and then add a new one.  

     

     

    If you have any questions, please feel free to let me know.   I will do my best to help.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 16, 2010 8:44 AM
  • mcgir,

    Lingzhi is on the right track but I think switched your entities around on accident:

    var eProgram = context.ElectricConservationPrograms.First();
    var endUseTarget = context.EndUseTargets.First();

    eProgram.EndUseTargets.Add(endUseTarget); //will create record in ProgramEndUseTarget
    context.SaveChanges();

    Conversely, you could also do:
    endUseTarget.ElectricConservationPrograms.Add(eProgram);

    The matrix table ProgramEndUseTargets is represented as the EndUseTargets navigation property on ElectricConservationProgram, and as the ElectricConservationPrograms navigation property on an EndUseTarget.

    When you add or remove instances of these entities to or from their respective navigation collection on the related entity, the EF will automatically interpret this to create or delete records in the table ProgramEndUseTarget.  You will not actually see a "ProgramEndUseTarget" entity in the model, because in truth it is not an entity -- it is a relationship.

    Hope this helps,
    Joe

    • Marked as answer by mcgjr Friday, July 16, 2010 4:26 PM
    Friday, July 16, 2010 4:11 PM

All replies

  • Hi,

    Does the ElectricConservationProgram entity has a TrackableColletion<EndUseTargets> navigation property?

    I think is from this navigation property that you can Update the ProgramEndUseTargets table.


    G
    Thursday, July 15, 2010 4:33 PM
  • Thank you for the reply.

    Unfortunately, I don't think so.  Because I am new to the Entity Framework, I may not be looking in the right place for a TrackableCollection, but nowhere in the model nor in the edmx is there any reference to a TrackableCollection.

    Thursday, July 15, 2010 7:00 PM
  • Hi mcgjr,

     

    Welcome to EF forum!

     

    For the many-to-many relationships, EF treats the linked tables as associations as you said.   To insert/update/delete the values in the linked table, we can perform the corresponding operations on the entities ElectricConservationProgram and EndUseTargets. 

     

    Insert operation:

    ======================================================================================

    MyEntities context = new MyEntities();

    var eProgram = context.ElectricConservationPrograms.First();

    var pTargets = context.ProgramEndUseTargets.First();

     

    eProgram.ProgramEndUseTargets.Add(pTargets);

     

    context.SaveChanges();

    ======================================================================================

     

    Delete operation:

    ======================================================================================

    MyEntities context = new MyEntities();

    var eProgram = context.ElectricConservationPrograms.First();

    var pTargets = context.ProgramEndUseTargets.First();

     

    eProgram.ProgramEndUseTargets.Remove(pTargets);

     

    context.SaveChanges();

    ======================================================================================

     

    Update operation:

    The update operation for associations is updated by first remove the association and then add a new one.  

     

     

    If you have any questions, please feel free to let me know.   I will do my best to help.  

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 16, 2010 8:44 AM
  • Thank you for the response, Lingzhi.

    I tried your code sample but there is no ProgramEndUseTargets as a property of the context.  The line:

    var eProgram = context.ElectricConservationPrograms.First();

    works fine.  But the line:

    var pTargets = context.ProgramEndUseTargets.First();

    gives the error:  '[ProjectName].MyEntities' does not contain a definition for 'ProgramEndUseTargets'.

    Any other suggestions?

    Friday, July 16, 2010 3:34 PM
  • mcgir,

    Lingzhi is on the right track but I think switched your entities around on accident:

    var eProgram = context.ElectricConservationPrograms.First();
    var endUseTarget = context.EndUseTargets.First();

    eProgram.EndUseTargets.Add(endUseTarget); //will create record in ProgramEndUseTarget
    context.SaveChanges();

    Conversely, you could also do:
    endUseTarget.ElectricConservationPrograms.Add(eProgram);

    The matrix table ProgramEndUseTargets is represented as the EndUseTargets navigation property on ElectricConservationProgram, and as the ElectricConservationPrograms navigation property on an EndUseTarget.

    When you add or remove instances of these entities to or from their respective navigation collection on the related entity, the EF will automatically interpret this to create or delete records in the table ProgramEndUseTarget.  You will not actually see a "ProgramEndUseTarget" entity in the model, because in truth it is not an entity -- it is a relationship.

    Hope this helps,
    Joe

    • Marked as answer by mcgjr Friday, July 16, 2010 4:26 PM
    Friday, July 16, 2010 4:11 PM
  • Thank you, thank you, thank you Joe and Lingzhi.  Between the two of you, I got it to work.

    Still lots to learn on EF, but I am getting there.

    Friday, July 16, 2010 4:26 PM
  • You're welcome!  :-)   Also thanks to Joe's help!

     

    Good day, both!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, July 19, 2010 12:54 AM
  • This was helpfull for me.. Only one question remains for me:

    The linktable contains one element that I need to update in some cases. How can I do this?

    Short info:

    I have a languages table on one side and correspondents on the other side. One language can be linked to many correspondents and one correspondent can have multiple languages. But only one language per correspondent is the default language. This default language resides in the linked table. How can I update this default language?

    Kind regards

    Peter Nies

    Wednesday, September 20, 2017 9:11 AM