ADO.NET Entity Data Model Mapping

In my last two posts, I've shown you how to create and modify ADO.NET Entity Data Models (EDMs). Now we'll look at the mappings behind the model and how the information is stored in the model.

When you use the Entity Data Model Wizard to generate a model, Visual Studio automatically displays it in the Entity Designer and stores it as an .edmx file. The view you see in the Entity Designer is a graphical representation of the XML data stored in the .edmx file. This XML comprises of the storage model of the data, the conceptual model, and the mapping specification between the two.

Let's take a look at the XML behind the EDM I created in previous posts.

Storage Model (SSDL)

The storage model is described by using the store schema definition language, or SSDL. The section begins by defining the schema namespace and the provider used to access the data.

<Schema Namespace="AdventureWorksModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">

It then declares one EntitySet element per entity in the model. This element defines the name, base type, store type, and schema for the entity.

<EntityContainer Name="AdventureWorksModelStoreContainer">

<EntitySet Name="Contact" EntityType="AdventureWorksModel.Store.Contact" store:Type="Tables" Schema="Person" />

<EntitySet Name="Customer" EntityType="AdventureWorksModel.Store.Customer" store:Type="Tables" Schema="Sales" />

<EntitySet Name="SalesOrderHeader" EntityType="AdventureWorksModel.Store.SalesOrderHeader" store:Type="Tables" Schema="Sales" />

<EntitySet Name="SalesTerritory" EntityType="AdventureWorksModel.Store.SalesTerritory" store:Type="Tables" Schema="Sales" />

<EntitySet Name="StoreContact" EntityType="AdventureWorksModel.Store.StoreContact" store:Type="Tables" Schema="Sales" />

The next elements declare the associations between the entities, specifying the entity at either end of the association. For example, here's the SalesOrderHeader to Contact association.

<AssociationSet Name="FK_SalesOrderHeader_Contact_ContactID" Association="AdventureWorksModel.Store.FK_SalesOrderHeader_Contact_ContactID">

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

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

</AssociationSet>

Then the entities themselves are defined, with elements for each property of the entity specifying the name, type, nullability, and maximum length of the property. Here's the XML defining the Contact entity.

<EntityType Name="Contact">

<Key>

<PropertyRef Name="ContactID" />

</Key>

<Property Name="ContactID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

<Property Name="NameStyle" Type="bit" Nullable="false" />

<Property Name="Title" Type="nvarchar" MaxLength="8" />

<Property Name="FirstName" Type="nvarchar" Nullable="false" MaxLength="50" />

<Property Name="MiddleName" Type="nvarchar" MaxLength="50" />

<Property Name="LastName" Type="nvarchar" Nullable="false" MaxLength="50" />

<Property Name="Suffix" Type="nvarchar" MaxLength="10" />

<Property Name="EmailAddress" Type="nvarchar" MaxLength="50" />

<Property Name="EmailPromotion" Type="int" Nullable="false" />

<Property Name="Phone" Type="nvarchar" MaxLength="25" />

<Property Name="PasswordHash" Type="varchar" Nullable="false" MaxLength="128" />

<Property Name="PasswordSalt" Type="varchar" Nullable="false" MaxLength="10" />

<Property Name="AdditionalContactInfo" Type="xml" />

<Property Name="rowguid" Type="uniqueidentifier" Nullable="false" />

<Property Name="ModifiedDate" Type="datetime" Nullable="false" />

<Property Name="CurrentPoints" Type="int" Nullable="false" />

</EntityType>

And then the associations are defined, including their multiplicity properties and referential constraints. Here's the definition for the SalesOrderHeader to Contact association.

<Association Name="FK_SalesOrderHeader_Contact_ContactID">

<End Role="Contact" Type="AdventureWorksModel.Store.Contact" Multiplicity="1" />

<End Role="SalesOrderHeader" Type="AdventureWorksModel.Store.SalesOrderHeader" Multiplicity="*" />

<ReferentialConstraint>

<Principal Role="Contact">

<PropertyRef Name="ContactID" />

</Principal>

<Dependent Role="SalesOrderHeader">

<PropertyRef Name="ContactID" />

</Dependent>

</ReferentialConstraint>

</Association>

The final section of the SSDL for this model defines the function import that I used to access the stored procedure. It defines the name of the function and the parameters it takes.

<Function Name="Sales_spNumberOfOrdersForAContact" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="Sales.spNumberOfOrdersForAContact" Schema="dbo">

<Parameter Name="ContactID" Type="int" Mode="In" />

</Function>

</Schema>

</edmx:StorageModels>

Conceptual Model (CSDL)

The conceptual model is described by using the conceptual schema definition language, or CSDL. Similarly to the storage model, it begins by declaring the schema namespace for the model.

<edmx:ConceptualModels>

<Schema Namespace="AdventureWorksModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">

It then declares each EntitySet used in the model.

<EntityContainer Name="AdventureWorksEntities" annotation:LazyLoadingEnabled="true">

<EntitySet Name="Contacts" EntityType="AdventureWorksModel.Contact" />

<EntitySet Name="SalesOrderHeaders" EntityType="AdventureWorksModel.SalesOrderHeader" />

<EntitySet Name="SalesTerritories" EntityType="AdventureWorksModel.SalesTerritory" />

<EntitySet Name="StoreContacts" EntityType="AdventureWorksModel.StoreContact" />

And continues by declaring the associations.

<AssociationSet Name="FK_SalesOrderHeader_Contact_ContactID" Association="AdventureWorksModel.FK_SalesOrderHeader_Contact_ContactID">

<End Role="Contact" EntitySet="Contacts" />

<End Role="SalesOrderHeader" EntitySet="SalesOrderHeaders" />

</AssociationSet>

It then declares the function import and the parameters it uses.

<FunctionImport Name="NumberOfOrders" ReturnType="Collection(Int32)">

<Parameter Name="ContactID" Mode="In" Type="Int32" />

</FunctionImport>

</EntityContainer>

Then each entity is defined in terms of its properties and their attributes.

<EntityType Name="Contact">

<Key>

<PropertyRef Name="ContactID" />

</Key>

<Property Name="ContactID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />

<Property Name="NameStyle" Type="Boolean" Nullable="false" />

<Property Name="Title" Type="String" MaxLength="8" Unicode="true" FixedLength="false" />

<Property Name="FirstName" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />

<Property Name="MiddleName" Type="String" MaxLength="50" Unicode="true" FixedLength="false" />

<Property Name="LastName" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />

<Property Name="Suffix" Type="String" MaxLength="10" Unicode="true" FixedLength="false" />

<Property Name="EmailAddress" Type="String" MaxLength="50" Unicode="true" FixedLength="false" />

<Property Name="EmailPromotion" Type="Int32" Nullable="false" />

<Property Name="Phone" Type="String" MaxLength="25" Unicode="true" FixedLength="false" />

<Property Name="PasswordHash" Type="String" Nullable="false" MaxLength="128" Unicode="false" FixedLength="false" />

<Property Name="PasswordSalt" Type="String" Nullable="false" MaxLength="10" Unicode="false" FixedLength="false" />

<Property Name="AdditionalContactInfo" Type="String" MaxLength="Max" Unicode="true" FixedLength="false" />

<Property Name="rowguid" Type="Guid" Nullable="false" />

<Property Name="ModifiedDate" Type="DateTime" Nullable="false" />

<Property Name="CurrentPoints" Type="Int32" Nullable="false" />

<NavigationProperty Name="SalesOrderHeaders" Relationship="AdventureWorksModel.FK_SalesOrderHeader_Contact_ContactID" FromRole="Contact" ToRole="SalesOrderHeader" />

<NavigationProperty Name="StoreContacts" Relationship="AdventureWorksModel.FK_StoreContact_Contact_ContactID" FromRole="Contact" ToRole="StoreContact" />

</EntityType>

Finally, the associations are defined with their multiplicities and referential constraints.

<Association Name="FK_SalesOrderHeader_Contact_ContactID">

<End Role="Contact" Type="AdventureWorksModel.Contact" Multiplicity="1" />

<End Role="SalesOrderHeader" Type="AdventureWorksModel.SalesOrderHeader" Multiplicity="*" />

<ReferentialConstraint>

<Principal Role="Contact">

<PropertyRef Name="ContactID" />

</Principal>

<Dependent Role="SalesOrderHeader">

<PropertyRef Name="ContactID" />

</Dependent>

</ReferentialConstraint>

</Association>

Conceptual to Storage Mapping (C-S Mapping)

The final section of the file defines the mapping between the conceptual and storage models. It describes how each entity from the store should map to each entity in the conceptual model that the application is written against. Here's the mapping fragment for the Contact entity.

<EntityContainerMapping StorageEntityContainer="AdventureWorksModelStoreContainer" CdmEntityContainer="AdventureWorksEntities">

<EntitySetMapping Name="Contacts">

<EntityTypeMapping TypeName="AdventureWorksModel.Contact">

<MappingFragment StoreEntitySet="Contact">

<ScalarProperty Name="ContactID" ColumnName="ContactID" />

<ScalarProperty Name="NameStyle" ColumnName="NameStyle" />

<ScalarProperty Name="Title" ColumnName="Title" />

<ScalarProperty Name="FirstName" ColumnName="FirstName" />

<ScalarProperty Name="MiddleName" ColumnName="MiddleName" />

<ScalarProperty Name="LastName" ColumnName="LastName" />

<ScalarProperty Name="Suffix" ColumnName="Suffix" />

<ScalarProperty Name="EmailAddress" ColumnName="EmailAddress" />

<ScalarProperty Name="EmailPromotion" ColumnName="EmailPromotion" />

<ScalarProperty Name="Phone" ColumnName="Phone" />

. . .

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

There's a similar mapping for the function import too.

<FunctionImportMapping FunctionImportName="NumberOfOrders" FunctionName="AdventureWorksModel.Store.Sales_spNumberOfOrdersForAContact" />

</EntityContainerMapping>

In these simple examples, the property names in the model match the column names in the data store. However, you can edit property names in the model and this mapping section is where they are linked to the original column name in the data store. Here's how the XML looks if I change the property name from Phone to Cellphone in the Contact entity in the model.

In the SSDL section, the property is still listed as Phone because it is describing the storage model.

<EntityType Name="Contact">

<Key>

<PropertyRef Name="ContactID" />

</Key>

<Property Name="ContactID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />

. . .

<Property Name="Phone" Type="nvarchar" MaxLength="25" />

. . .

</EntityType>

But in the CSDL section describing the conceptual model, you can see that the property is now called Cellphone.

<EntityType Name="Contact">

<Key>

<PropertyRef Name="ContactID" />

</Key>

<Property Name="ContactID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />

. . .

<Property Name="Cellphone" Type="String" MaxLength="25" Unicode="true" FixedLength="false" />

. . .

</EntityType>

And in the C-S mapping section, the two differently named properties are mapped together.

<EntitySetMapping Name="Contacts">

<EntityTypeMapping TypeName="AdventureWorksModel.Contact">

<MappingFragment StoreEntitySet="Contact">

<ScalarProperty Name="ContactID" ColumnName="ContactID" />

. . .

<ScalarProperty Name="Cellphone" ColumnName="Phone" />

. . .

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

So you've now seen how the XML behind an EDM defines the storage model from the data store, the conceptual model in the application, and the mappings between the two.