none
ADO.NET Entity Framework Associations Inserts

    Question

  • Hi :)

    I have a problem when inserting records with many to many relationships. It's not the end to end of the many to many that I have a problem with but rather the bridging table that appears as an association.

    This is my issue:

    I have the following tables: Media, MediaCategory and Category. As you can imagine, when I create the Entity Model, Media and Category tables are exposed which is great. I can add data to both tables with very little effort and I'm happy about this. MediaCategory links these two tables.

    I'm using 2 datagrids to do this. One Grid contains Media and the other Category

    When I want to use the same Category across Media records it becomes a problem. I can't find a way to insert into the association as the record I need already exists in the Category table. for example:

    Catergory Table contains a record that has "Music" as data. I can add it to the first Media record but I don't know how to update the MediaCategory table so other media records can be linked to this Category.

    Any help would be great :)

    • Moved by VMazurModerator Monday, August 16, 2010 10:13 AM (From:ADO.NET Managed Providers)
    Saturday, August 14, 2010 8:48 AM

Answers

  • Hello,

    Thanks for your reply. I've actually gotten around he problem.

    You see, when the entity framework imports your data structure it will use the bridging table (link table) as an association but not create that bridging table as an entity that you can insert data into.

    As i described in the earlier post, instead of ending up with Media->MediaCategory->Category (which is what the tables are in the database) you end up with Media->Category. Media would be an entity and so would Category. MediaCategory would become an association and not an entity.

    Simply by adding a field to MediaCategory on the database which is not related to Media or Category and setting the field with an identity seed (I used an integer field) the entity framework will then import the data structure with the correct model (the way you see it in the database diagram)

    I'm absolutely over the moon as I can now move completely away from Dataset (for desktop development anyway)

    I hope this makes sense :)

    Monday, August 16, 2010 10:45 AM
  • Adding PayLoad to the LinkTable is a quick and dirty way. It works but you need to change the Table structure by adding redundant column.

    Please see this discussion on how to add the LinkTable manually to the model - without adding any columns to it:

    http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/f1a8f6cd-a8f7-4fef-9cf8-97b8a8ea1c93

     

    Monday, August 16, 2010 3:17 PM

All replies

  • Hi,

    Based on your description, I would recommend that you could achieve your goal via JOIN statements.

    I believe that the three tables have some dependence each other.

     

    Best Regards

    Larcolais Gong


    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, August 16, 2010 10:05 AM
  • Hello,

    Thanks for your reply. I've actually gotten around he problem.

    You see, when the entity framework imports your data structure it will use the bridging table (link table) as an association but not create that bridging table as an entity that you can insert data into.

    As i described in the earlier post, instead of ending up with Media->MediaCategory->Category (which is what the tables are in the database) you end up with Media->Category. Media would be an entity and so would Category. MediaCategory would become an association and not an entity.

    Simply by adding a field to MediaCategory on the database which is not related to Media or Category and setting the field with an identity seed (I used an integer field) the entity framework will then import the data structure with the correct model (the way you see it in the database diagram)

    I'm absolutely over the moon as I can now move completely away from Dataset (for desktop development anyway)

    I hope this makes sense :)

    Monday, August 16, 2010 10:45 AM
  • Adding PayLoad to the LinkTable is a quick and dirty way. It works but you need to change the Table structure by adding redundant column.

    Please see this discussion on how to add the LinkTable manually to the model - without adding any columns to it:

    http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/f1a8f6cd-a8f7-4fef-9cf8-97b8a8ea1c93

     

    Monday, August 16, 2010 3:17 PM
  • For a couple of days now, I have been wracking my brains and scouring the Interwebz for information about how to insert data into database intersection tables using the Entity Framework (EF).  I’ve hit all the major players’ web sites and blogs and NO ONE has provided straightforward syntax on how to perform this.  Out of the blue, the answer occurred to me and I was bound and determined to share this with as many people as I could to lessen the pain I went through.

    Let’s set the stage.  Assume we have a database relationship as such:

    Students (StudentID(PK), StudentName, Gender)
    Courses (CourseID(PK), CourseName, CourseDescription)
    StudentsCourses (StudentID(PK, FK), CourseID(PK, FK))

    For those of you familiar enough with EF, you know that when the relationships above are translated into an entity data model, the Students and Courses tables are created as entities, but the StudentsCourses table is not.  This is because the StudentsCourses table does not contain any attributes other than the keys from the other two tables, so EF directly maps the many-to-many relationship between Students and Courses (EF is not limited in the way relational databases are in this respect.) and instead of an entity, translates the intersection table into an AssociationSet.  If you weren’t aware of this behavior, check out these links for examples:

    http://thedatafarm.com/blog/data-access/inserting-many-to-many-relationships-in-ef-with-or-without-a-join-entity/
    http://thedatafarm.com/LearnEntityFramework/tutorials/many-to-many-relationships-in-the-entity-data-model/
    http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx

    Now let’s assume that you want to register a current student (ID:123456) for new courses this semester (ENGL101, SOC102, and PHY100).  In this case, we want to insert new records into the StudentsCourses table using existing information in the Students table and Courses table.  Working with data from either of those tables is easy as they are both an entity in the model, however you can’t directly access the StudentsCourses table because it’s not an entity.  The key to this dilemma lies with the navigation properties of each entity.  The Student entity has a navigation property to the Course entity and vice versa.  We’ll use these to create “records of association” as I like to call them.

    Here’s the code sample for associating an existing student with existing courses:

    using (var context = TheContext())
    {
     Student st = context.Students.Where(s => s.StudentID == “123456”).First();
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “ENGL101”).First());
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “SOC102”).First());
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “PHY100”).First());
    
     context.Students.AddObject(st);
     context.SaveChanges();
    }
    

    Because the associations go both ways, it stands to reason that one could retrieve three Course objects (by CourseID) and associate the same Student object to each, but I haven’t tested that myself.  I think it would result in more code than is necessary and might be semantically confusing.

    Here’s a code sample that associates a new student with the same existing courses:

    using (var context = TheContext())
    {
     Student st = new Student({ StudentID = “654321”, StudentName = “Rudolph Reindeer”,
      Gender = “Male” });
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “ENGL101”).First());
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “SOC102”).First());
     st.Courses.Add(context.Courses.Where(c => c.CourseID == “PHY100”).First());
    
     context.Students.AddObject(st);
     context.SaveChanges();
    }
    

    And finally, here’s the code to associate a new student with new courses (‘...’ used for brevity):

    using (var context = TheContext())
    {
     Student st = new Student({ ... });
     st.Courses.Add(new Course({ ... }));
     st.Courses.Add(new Course({ ... }));
     st.Courses.Add(new Course({ ... }));
    
     context.Students.AddObject(st);
     context.SaveChanges();
    }
    

    • Edited by TonganJedi Sunday, May 15, 2011 8:43 PM cleaned up format with additional line spacing
    Sunday, May 15, 2011 8:40 PM