Mapping 101: Part 2 - Association Mapping

Article
05/10/2007

Continuing along with posts on the essentials of Entity Framework mapping (see Mapping 101: Part 1), we’ll next take a look beyond the basics of mapping containers and sets which are essential in every model and start to look at more complex scenarios.

As with the first part to our Mapping 101 blog post, these samples are leveraging the Northwind “SampleQueries” solution that came with the March CTP. These samples have been updated, and you should be able to find the latest link to those samples on the “101 Linq Samples Updated” post. Also note that the Mapping 101 posts use the same samples as the Entity Data Model 101 posts (Part 1 and Part 2)

Today’s post will focus on Association mapping in the Entity Framework. In the March CTP we support relationships such as the following in the Sample Queries model:

In this diagram of a definition of a relationship between Categories and Products, we’ll look at details in the model definition (CSDL), store definition (SSDL) and mapping definition (MSL). As shown in the diagram, the Categories EntitySet has a one to many relationship with the Products EntitySet.

In the Store and Entity models for this scenario, we’ll define an AssociationType and related AssociationSet for this relationship, as well add a navigation property on the EnittyType to allow for navigation from a Product to its Category. Let’s see that in detail:

The Relationship Definition in the Store:

First, let’s look at the syntax in the SSDL File. I’ve removed most of the detail to focus on the information interesting to the relationship shown in the above diagram:

<?xml version="1.0" encoding="utf-8"?>

< AssociationSetName = "FK_Products_Categories"Association="Northwind.FK_Products_Categories">

< EndRole = "Categories"EntitySet="Categories" />

< EndRole = "Products"EntitySet="Products" />

</ AssociationSet >

</EntityContainer>

</EntityType>

</EntityType>

< AssociationName = "FK_Products_Categories">

< EndRole = "Categories"Type="Northwind.Categories"Multiplicity="0..1" />

< EndRole = "Products"Type="Northwind.Products"Multiplicity="*" />

< ReferentialConstraintFromRole = "Categories"ToRole="Products"FromProperty="CategoryID"ToProperty="CategoryID" />

</ Association >

</Schema>

As noted in Part 1 of the Mapping 101 posts, The SSDL artifact contains information about the EntityContainer, or the database, which defines the names of the tables in the database, along with relationships between those tables. It also contains type descriptions of those tables and relationships.

Let’s take a closer look at two elements in the SSDL related to the relationship:

The AssociationSet definition in the EntityContainer: The AssociationSet represents the instance of the Association in the database. It links to the underlying Association definition, and defines which EntitySet instances (tables) are tied to which role in the Association:

The Association definition: The Association defines the underlying details of the Association Set. It shows information for each end of the relationship:

· Role: A name for each role

· Type: A link to the namespace qualified type, as defined in the SSDL document (in this case, the Categories and Products Types

· Multiplicity: the multiplicity of the end of the relationship (the EDM supports 0..1, 1, or *)

Association definitions can also contain a ReferentialConstraint. In an SSDL, this is used to define the foreign key. The “From” role is defined as the identifying end of the relationship. In the case of a database foreign key, that notes the table including the column as its key. The “To” role is defined as the dependent relationship.

Note: This syntax will change a bit in future releases to make this information for readable!

The Relationship Definition in the Model:

Let’s take a look at the CSDL file – removing all parts that aren’t interesting for describing the relationship between Products and Categories:

<?xml version="1.0" encoding="utf-8"?>

< AssociationSetName = "FK_Product_Category"Association="EntityNorthwind.FK_Product_Category">

< EndRole = "Category"EntitySet="Categories" />

< EndRole = "Product"EntitySet="Products" />

</ AssociationSet >

</EntityContainer>

</EntityType>

< NavigationPropertyName = "Category"Relationship="EntityNorthwind.FK_Product_Category"FromRole="Product"ToRole="Category" />

</EntityType>

< AssociationName = "FK_Product_Category">

< EndRole = "Category"Type="EntityNorthwind.Category"Multiplicity="0..1" />

< EndRole = "Product"Type="EntityNorthwind.Product"Multiplicity="*" />

</ Association >

</Schema>

Next, let’s take a closer look at the interesting information on the association in the CSDL file (bolded above):

The AssociationSet definition in the EntityContainer: In the CSDL file, the AssociationSet represents the instance of the Association in the model. It links to the underlying Association definition, and defines which EntitySet instances will be tied to which role in the Association.

The Association definition: The Association defines the underlying details of the Association Set. It shows information for each end of the relationship:

· Role: A name for each role

· Type: A link to the namespace qualified type, as defined in the SSDL document (in this case, the Categories and Products Types

· Multiplicity: the multiplicity of the end of the relationship (the EDM supports 0..1, 1, or *)

Note: in the current release, we can’t define a “ReferentialConstraint” in the CSDL. This functionality will come in a later beta.

The Association and AssociationSet information provide information to the EntityFramework of how to construct collections of instances at runtime. This allows a user to query information like “Give me all of the Products in this Category”.

The CSDL also contains a NavigationProperty defined on the Entity Type that acts as a role on a relationship with a 0..1, or 1 multiplicity. This shows how the model will define a property so that someone looking at Product can query about the Category it belongs to.

The NavigationProperty contains:

· Name: An identifier for the Navigation Property

· Relationship: The namespace qualified name of the type this navigation property is of

· FromRole: The name of the role in the association where the navigation property originates

· ToRole: The name of the role in the association where the navigation property points.

The Relationship in Mapping

Finally, let’s take a look at how we note the mapping of the relationship in the MSL file. Again, we’ve removed the information in the mapping file that isn’t interesting to this relationship:

<cs:AssociationSetMapping cs:Name="FK_Product_Category" cs:TypeName="EntityNorthwind.FK_Product_Category" cs:TableName ="Products" >

<cs:EndProperty cs:Name="Category">

<cs:ScalarProperty cs:Name="CategoryID" cs:ColumnName="CategoryID" />

</cs:EndProperty>

<cs:EndProperty cs:Name="Product">

<cs:ScalarProperty cs:Name="ProductID" cs:ColumnName="ProductID" />

</cs:EndProperty>

<cs:Condition cs:ColumnName="CategoryID" cs:IsNull="false" />

</cs:AssociationSetMapping>

In the mapping file, we map each AssociationSet. In the case of our FK_Product_Category relationship, it’s mapped to the underlying Products table because that table contains links to both ends (keys) of the relationship.

We define how each EndProperty (role) is mapped to key columns in that table using the ScalarProperty mapping element:

· Name: The name of EntityProperty in the Key

· ColumnName: The Column name in the database.

In the case of our relationship, we’ll map the key of the products table to define the Products end, and the foreign key to the Categories table to define the Categories end.

We can also define a condition – in this case we note that the CategoryID can’t be null. That tells mapping the the relationship between Categories to Products is 1 to Many, not 0..1 to Many.

Comments:

In these samples, the names we’re defining for relationships were borrowed from the Foreign Key definitions in the database. In fact, our model generation functionality will use those foreign key names when defining default relationships in a model. Those can be edited by each user.

This post does not show how to map what we call an Identifying relationship, or one similar to what’s described as a containment relationship in UML. For example you won’t find this relationship in the March CTP samples:

This is however, a typical relationship you’ll want to model. To get this functionality, you’ll have to wait for the next beta of ADO.Net, as we’ve just added that capability. We’ll follow up with a new post on what to change in the mapping for this relationship, once those bits are available publicly.

What’s Next?

In our next post, we’ll focus on EntitySet mapping of Inheritance relationships.

Feedback Welcome

We strongly encourage user feedback at our dedicated forum: https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1. We believe we’ve designed a great product but we still actively trying to understand customers’ needs so we can make it even better. Additionally, our team is committed to help the developer community adopt the new ADO.NET technology to build model-based applications.

Dan Dosen

ADO.Net Program Manager

Comments

Anonymous
May 10, 2007
This is much like an in-memory version of relation database. I usually use stored procedures and functions instead of dynamic sql, so I don't think this is useful to me
Anonymous
May 15, 2007
Is there any time frame on the next beta release for the ADO.Net Entity Framework?
Anonymous
May 20, 2007
I am really struggling with setting up inheritance (specifically Table per Type) in Beta 1. Please publish part 3 of this article series soon Thanks
Anonymous
May 29, 2007
I'm looking for to LINQ and related technologies. Lately, I've been using self-referencing tables alot for navigation systems (I use the With statement in SQL2005) Will there be support for this in the entity model. Like can I get the children of a node which is in the same table?
Anonymous
June 16, 2007
I am still struggling a lot with inheritance. Please post the next part in this article series soon!! Morten
Anonymous
July 31, 2007
The comment has been removed
Anonymous
December 28, 2007
Very helpful posts! Can a property of a complex type be the endpoint of an association? e.g. CountryID in Address when Address is a complex type.
Anonymous
May 26, 2008
Continuing along with posts on the essentials of Entity Framework mapping (see Mapping 101: Part 1 ), we’ll next take a look beyond the basics of mapping containers and sets which are essential in every model and start to look at more complex scenarios
Anonymous
June 04, 2008
Continuing along with posts on the essentials of Entity Framework mapping (see Mapping 101: Part 1 ), we’ll next take a look beyond the basics of mapping containers and sets which are essential in every model and start to look at more complex scenarios
Anonymous
April 29, 2010
Is there a way to map two tables in different databases?

Partager via

Mapping 101: Part 2 - Association Mapping

Comments

Ressources supplémentaires