StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>Let’s set the stage. Assume we have a database relationship as such:</p> <blockquote> <p>Students (StudentID(PK), StudentName, Gender)<br /> Courses (CourseID(PK), CourseName, CourseDescription)<br /> StudentsCourses (StudentID(PK, FK), CourseID(PK, FK))</p> </blockquote> <p>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:</p> <p><a href="http://thedatafarm.com/blog/data-access/inserting-many-to-many-relationships-in-ef-with-or-without-a-join-entity/" rel="nofollow">http://thedatafarm.com/blog/data-access/inserting-many-to-many-relationships-in-ef-with-or-without-a-join-entity/</a><br /> <a href="http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx" rel="nofollow">http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx</a></p> <p>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.</p> <p>Here’s the code sample for associating an existing student with existing courses:</p> <pre><code>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(); } </code></pre> <p>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.</p> <p>Here’s a code sample that associates a new student with the same existing courses:</p> <pre><code>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(); } </code></pre> <p>And finally, here’s the code to associate a new student with new courses (‘...’ used for brevity):</p> <pre><code>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(); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload