Database Reference
In-Depth Information
As we illustrated in several recipes in Chapter 2, a many-to-many relationship is represented in a database
using an intermediate table called a
link table
. The link table holds the foreign keys on each side of the relationship
(see Figure
6-2
). When a link table with no additional columns and the related tables are imported into Entity
Framework, the Entity Data Model Wizard creates a many-to-many association between the related tables. The link
table is not represented as an entity; however, it is used internally for the many-to-many association.
Figure 6-2.
A database diagram showing the EventOrganizer link table holding the foreign keys to the related Event
and Organizer tables
To retrieve the entity keys EventId and OrganizerId, we can use either a nested
from
clause or the
SelectMany()
method. Listing 6-1 shows both approaches.
Listing 6-1.
Retrieving a Link Table Using Both a Nested
from
Clause and the
SelectMany()
Method
using (var context = new EF6RecipesContext())
{
var org = new Organizer { Name = "Community Charity" };
var evt = new Event { Name = "Fundraiser" };
org.Events.Add(evt);
context.Organizers.Add(org);
org = new Organizer { Name = "Boy Scouts" };
evt = new Event { Name = "Eagle Scout Dinner" };
org.Events.Add(evt);
context.Organizers.Add(org);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
var evsorg1 = from ev in context.Events
from organizer in ev.Organizers
select new { ev.EventId, organizer.OrganizerId };
Console.WriteLine("Using nested from clauses...");
foreach (var pair in evsorg1)
{
Console.WriteLine("EventId {0}, OrganizerId {1}",
pair.EventId,
pair.OrganizerId);
}
var evsorg2 = context.Events
.SelectMany(e => e.Organizers,
(ev, org) => new { ev.EventId, org.OrganizerId });
Console.WriteLine("\nUsing SelectMany()");