Deploying an Entity Data Model

In the past few posts, you've seen how to create and modify an Entity Data Model (EDM). You've also looked at the XML behind the visual EDM to understand how the conceptual model in the application maps to the data in the data store.

You may recall that I mentioned in the first post how easy it is to change your applications data store without having to modify the application itself. So, here's how to do it.

By default, the Entity Data Model Wizard creates a model with the metadata embedded inside the application assembly. If you take a look in App.config or Web.config, you'll see the connection information as shown below.

<connectionStrings>

<add name="AdventureWorksEntities" connectionString="metadata=res://*/AWModel.csdl|res://*/AWModel.ssdl|res://*/AWModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=DemoBox\SQLExpress;Initial Catalog=AdventureWorks;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>

Note that the metadata is identified as being a resource in the assembly by using the res://* path.

This works fine for many applications; however, changes to the model then require changes to the assembly. For example, if your storage model changes (a not uncommon occurrence), you will need to update the model and redeploy the whole assembly. Because it's only likely to be the storage model and mapping sections of the .edmx file that change, not the conceptual model that the application is coded against, it's far easier to store the model externally to the assembly and only update and redeploy the model itself.

The Metadata Artifact Processing property of the model defines where the model is deployed to at build time.

The default setting is Embed in Output Assembly, but you can change this to Copy to Output Directory to store the model outside of the assembly. Now when you build the application, you'll see the .cdsl, .ssdl, and .msl files created in the debug directory.

And the connection string is now updated to point to these external files.

<connectionStrings><add name="AdventureWorksEntities" connectionString="metadata=.\AWModel.csdl|.\AWModel.ssdl|.\AWModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=DemoBox\SQLExpress;Initial Catalog=AdventureWorks;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /></connectionStrings>

So now if you change the model, you can simply rebuild the application and then copy the new model files to the production server without redeploying the assembly.

Read More >>

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.

Read More >>

Modifying ADO.NET Entity Data Models

Continuing our look at Entity Data Models (EDMs), you'll now see how to modify an existing model. During the development process, it's not unusual to find that you need to access more data than originally planned. In Visual Studio, you can simply run the Update Wizard to add entities linked to additional tables that you need or define your own custom entities to the model and the database at the same time.

I'll continue working with the model that I created in the previous post, which contains entities for sales territory, sales orders, and store contacts. I now decide that the application needs additional information from the Customer table in the database. The context menu in the Entity Designer window enables you to launch the Update Wizard and select the new tables that you want to use.

And you can see that the model automatically generates the associations between the new and existing entities.

If I want to use data that is not currently stored in my data source, I can define an entity in the model and then update the database to create a table to hold the new information. The Entity Designer context menu enables you to add new items to the model.

I can define the entity and then add properties and associations to it.

I can then either map the new entity to existing data in my enterprise or use the Generate Database Wizard to generate the Transact-SQL code necessary to add tables for the entity to my database.

Note that the Generate Database Wizard generates a script that defines the entire EDM and begins by dropping all the existing tables mapped to the model. If you want to create only a subset of the model, you can use the wizard to generate the script, copy the code from the wizard, and then edit the code to use only the sections that you need.

You can also map entities in the model to stored procedures or views in a database. I've already added a stored procedure named spNumberOfOrdersForAContact to the AdventureWorks database as defined below.

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER OFF;
GO

USE [AdventureWorks]
GO

CREATE PROCEDURE [Sales].[spNumberOfOrdersForAContact]
@ContactID [int] = 1
AS
BEGIN
SELECT COUNT(*)
FROM [Sales].[SalesOrderHeader]
WHERE [ContactID] = @ContactID
END;
GO

This stored procedure takes a ContactID as an input and then returns the number of orders that the contact has made as a scalar value.

To access the stored procedure, I first add it to the model by using the Update Wizard similarly to adding a new table to the model and then create a function import in the model to retrieve the data.

So you've now seen how easy it is to modify models to include additional data in them. In my next post, I'll show you more details about how the model is mapped to the data source.

Read More >>

Creating ADO.NET Entity Data Models

Over the past few years, data access in .NET Framework applications has been transformed. The introduction of the Entity Framework into ADO.NET means that you can use conceptual models of your normalised data, without worrying about the structure of that data in your data store. You simply define the entities that you require in your application and then map them to the underlying data by using an Entity Data Model (EDM). Sounds complex, but by using the Entity Data Model tools in Visual Studio, it's just a case of following a wizard and then a little click & drag. I'll start by looking at how to create a model from an existing database and in the next post I'll show you some of the cooler features of the tools.

If I add a new ADO.NET Entity Data Model item to a project, Visual Studio automatically launches the Entity Data Model Wizard to step through the creation of an EDM. The first decision to make is whether to generate an EDM from an existing database or create an empty model. I'll choose the first option for this post.

After selecting or creating a connection to the AdventureWorks database, I can select the database objects that I want to use in my application. I'll simply choose a few tables for now, but you can also create entities from views and stored procedures.

When I click Finish, Visual Studio creates an EDM with an entity per table that I selected and associations between the entities corresponding to the relationships defined in the database.

If I right-click in the designer pane and click Mapping Details, I can view the mappings between an entity in my EDM and the underlying table in the database.

And if I close the designer pane and then right-click the model in Solution Explorer, I can select to open it in an XML editor to see the XML code behind the mappings.

When Visual Studio generates the model and mappings, it also auto-generates an object layer based on the contents of the model.

I can code against these objects and their properties to work with and navigate around the data in the model.

As you can see, no more complex Transact-SQL join query strings risking run-time errors if my table or field names aren't accurate, just simple coding against a conceptual model that maps to my data. These few lines of code populate a data grid with SQL Server data based on the contents of a combo box on a form.

You can create an EDM for many different types of data source, so it makes it easier to update applications if your data store changes. You simply change the connection information for the model, update your mappings, and your code should continue to run without any modifications. If you also configure your application to store the model data externally (use the Metadata Artifact Processing property of the model), you only need to redeploy the three model definition files, not the entire application.

So you've now seen how quick and easy it is to generate a model from an existing database and write code against in. In my next post, I'll show you more features of the Entity Designer and EDMs.

Read More >>