Now that you’re getting accustomed to the Entity Framework and Entity Data Model (Entity Data Model 101: Part 1 and Entity Data Model 101: Part 2), we’ll now take a look at the Object Relational Mapping (ORM) characteristics of the Entity Framework.  We’ll post several “Mapping 101” topics to describe the content of the artifacts required by the Entity Framework for supporting mappings between Entity Models and an underlying database.  We expect the content of these artifacts to be best edited with our tool support in Visual Studio, but until those are ready, users may want an understanding of these artifacts in detail.

 

Note: These Mapping 101 posts will make use of the “SampleQueries” solution that ships with the February CTP and its underlying model to best help illustrate the content of these artifacts.

 

These Mapping 101 posts will progress along in complexity with the EDM 101 posts.  In the first EDM 101 post, we described the CSDL file, or Conceptual Schema Definition Language to define the Entity Model.  In this post, we’ll describe the basics of mapping that artifact to a database.  To do that, we need to introduce two additional types of artifacts that support the Entity Framework:

 

·         Store Schema Definition Language (SSDL) artifacts: Describes the structure of the underlying database, including tables, columns and functions used to support the Entity Model.

·         Mapping Schema Language (MSL) artifacts: Describes the mappings between an Entity Model and a Store Model. 

 

One point to make clearly early on in our mapping lesson is that, even though a user can define multiple artifacts for their models and mappings, an “Entity Container” defined in an Entity model can be mapped in a single MSL file.  This is because we need to know the entire scope of the mapping of a container while we’re loading the artifacts. 

 

SSDL Files

In similar manner to how CSDL files define a model using Containers, EntityTypes and EntitySets, SSDL files define a database model the same way.  The key portions of an SSDL file include:

 

·         The Namespace: In SSDL files, the namespace describes the database under the Entity Model.

·         The EntityContainer definition: In the case of an SSDL file, the container helps define the schema in which an Entity Set lives.

·         EntityType definitions: Types define the structure of a table.  These Entity Types can be re-used when defining EntitySets if several tables have the same structure. 

·         EntitySet definitions: EntitySets define instances of tables in the database.  There’s a 1-1 relationship between an EntitySet and a table in the database.

·         Association definitions: Associations are Association Types used to define relationships between Entity Types (Tables).  In the EDM, we use Associations to define the Foreign Key structure between tables

·         AssocationSet definitions: Defines instances of  relationships between Entity Sets

 

Here’s a sample of a streamlined version of the SSDL file from the SampleQueries project:

 

 

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

<Schema Namespace="Northwind" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

 

  <EntityContainer Name="dbo">

 

    <EntitySet Name="Categories" EntityType="Northwind.Categories" />       

    <EntitySet Name="Products" EntityType="Northwind.Products" />

 

    <AssociationSet Name="FK_Products_Categories" Association="Northwind.FK_Products_Categories">

      <End Role="Categories" EntitySet="Categories" />

      <End Role="Products" EntitySet="Products" />

    </AssociationSet>

   

  </EntityContainer>

 

  <EntityType Name="Categories" Key="CategoryID">

    <Property Name="CategoryID" Type="int" Nullable="false" StoreGeneratedPattern="identity" />

    <Property Name="CategoryName" Type="nvarchar" Nullable="false" />

  </EntityType>

 

  <EntityType Name="Products" Key="ProductID">

    <Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="identity" />

    <Property Name="ProductName" Type="nvarchar" Nullable="false" />

    <Property Name="SupplierID" Type="int" />

    <Property Name="CategoryID" Type="int" />

    <Property Name="QuantityPerUnit" Type="nvarchar" />

    <Property Name="UnitPrice" Type="money" />

    <Property Name="UnitsInStock" Type="smallint" />

    <Property Name="UnitsOnOrder" Type="smallint" />

    <Property Name="ReorderLevel" Type="smallint" />

    <Property Name="Discontinued" Type="bit" />

  </EntityType>

 

  <Association Name="FK_Products_Categories">

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

    <End Role="Products" Type="Northwind.Products" Multiplicity="*" />

    <ReferentialConstraint FromRole="Categories" ToRole="Products" FromProperty="CategoryID" ToProperty="CategoryID" />

  </Association>

 

</Schema>

 

 

In the above example,

·         The Schema namespace relates to “Northwind” database

·         The EntityContainer relates to the “dbo” schema

·         The Categories EntitySet defines the instance of the Categories table

·         The Categories EntityType describes the Categories table

·         The FK_Products_Categories AssociationSet defines the instance of the foreign key between the Products and Categories table

·         The FK_Products_Categories Association describes the foreign key relationship between Products and Categories.

 

As we describe mapping in later posts, we’ll review how users can alter the SSDL definition to support complex scenarios such as supporting multiple database schemas.

 

MSL Files

MSL files define the mapping between the Entity layer and Store layer.   In the Entity Framework, we map the instances of the EntitySets.  

 

This XML below shows a scaled back version of the mapping file for the SampleQueries database that we’ll review to look at the details of mapping:

 

 

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

<Mapping xmlns:cs="urn:schemas-microsoft-com:windows:storage:mapping:CS" cs:Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

  <cs:EntityContainerMapping cs:StorageEntityContainer="dbo" cs:CdmEntityContainer="EntityNorthwindContext">

   

    <cs:EntitySetMapping cs:Name="Categories" cs:TableName="Categories" cs:TypeName="EntityNorthwind.Category">

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

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

    </cs:EntitySetMapping>             

   

    <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>

   

  </cs:EntityContainerMapping>

</Mapping>

 

 

Container Mapping

 

We first need to map the Model  EntityContainer to the Store EntiyContainer.  In our example, we’ve defined an EntityContainer in our Entity Model, and we’ve defined the Store EntityContainer using the dbo schema in the Northwind database:

 

 

<cs:EntityContainerMapping cs:StorageEntityContainer="dbo" cs:CdmEntityContainer="EntityNorthwindContext">

 

 

Within container mapping, we define mapping for each of the EntitySets.  This structure helps define the equivalent of mapping an Entity Set to a Table in the database.

 

 

    <cs:EntitySetMapping cs:Name="Categories" cs:TableName="Categories" cs:TypeName="EntityNorthwind.Category">

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

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

    </cs:EntitySetMapping>             

 

 

When mapping the EntitySet we define:

·         Name: Corresponds with the EntitySet name in the CSDL file

·         TableName: corresponds to the table name we’re mapping to – using the EntitySet defined in the SSDL

·         TypeName: corresponds to the model (CSDL) type name underlying the EntitySet

 

For each column in the mapping we define a ScalarProperty mapping:

·         Name: the name of the property on the type in the Entity Model (CSDL)

·         ColumnName: the name of the property on the type in the Store Model (SSDL)

 

For AssociationSets, we want to map the Properties in which the relationship is based on to the underlying store columns that make up 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>

 

 

For each AssociationSetMapping, we define:

·         Name: The name of the Entity model’s AssociationSet we’re mapping

·         TypeName: The name of the Entity Model Association underlying the AssociationSet

·         TableName: The name of the table defining the relationship in the Store Model.  In the example above, this will be the table that contains the foreign key information.

·         For each end of the relationship we defin:

o    EndProperty Name:  The name of the Property on the Association in the Entity model.

o    ScalarProperty Name:The name of the Property on the Entity that maps to this Association end

o    ScalarProperty ColumnName: The name of the column on the table that maps to this Association end.

 

What’s Next?

In future posts, we’ll go into more detail for EntitySet mapping to describe more complex scenarios, including a variety of inheritance scenarios, and Entity splitting across multiple tables.

 

Feedback Welcome

We strongly encourage user feedback at our dedicated forum: http://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