Database Reference
In-Depth Information
foreach (var pair in evsorg2)
{
Console.WriteLine("EventId {0}, OrganizerId {1}",
pair.EventId, pair.OrganizerId);
}
}
The output of the code in Listing 6-1 should be similar to the following:
Using nested from clauses...
EventId 31, OrganizerId 87
EventId 32, OrganizerId 88
Using SelectMany()
EventId 31, OrganizerId 87
EventId 32, OrganizerId 88
How It Works
A link table is a common way of representing a many-to-many relationship between two tables in a database. Because
it serves no purpose other than defining the relationship between two tables, Entity Framework represents a link table
as a many-to-many association, not as a separate entity.
The many-to-many association between Event and Organizer allows easy navigation from an Event entity to the
associated organizers and from an Organizer entity to all of the associated events. However, you may want to retrieve
just the keys in the link table. You may want to do this because the keys are themselves meaningful or you want to use
these keys for operations on these or other entities. The problem here is that the link table is not represented as an
entity, so querying it directly is not an option. In Listing 6-1, we show a couple of ways to get just the underlying keys
without materializing the entities on each side of the association.
The first approach in Listing 6-1 uses nested
from
clauses to retrieve the organizers for each event. Using the
Organizers' navigation property on the instances of the Event entity leverages the underlying link table to enumerate
all of the organizers for each of the events. We reshape the results to the pairs of corresponding keys for the entities.
Finally, we iterate through the results, printing the pair of keys to the console.
In the second approach, we use the
SelectMany()
method to project the organizers for each event into the pairs
of keys for the events and organizers. As with the nested
from
clauses, this approach uses the underlying link table
through the Organizers' navigation property. We iterate through the results in the same way as with the first approach.
6-2. Exposing a Link Table as an Entity
Problem
You want to expose a link table as an entity instead of a many-to-many association.
Solution
Let's say that your database has a many-to-many relationship between workers and tasks, and it looks something
like the one in the database diagram shown in Figure
6-3
.
