Entity Data Model 101: Part 1

 

Most developers are familiar with the Object/Relational Mapping (ORM) problem: databases use the abstraction of rows in tables, but application programs use the abstraction of classes and objects. Existing ORM frameworks tend to address this mismatch by allowing programming classes to be annotated in order to relate them to the database.

The intent with ADO.NET is more ambitious: We view the ORM problem as just one of a number of services we want to build on the database. Other services include reporting, synchronization, backup, and so on. In order to cover all of these services, we have designed a data model that is similar to the object-oriented idiom that programmers use, while remaining independent of any particular programming language or programming platform. This data model is the Entity Data Model (EDM).

The EDM is a conceptual model for designing the data layer of applications. Entities and relationships in the domain of an application are specified in XML syntax. Both the programming classes used in applications and the storage schema map to this conceptual model.

Entities and Associations

The EDM is an implementation of the entity-relationship (ER) model. An EDM entity type is a specification for something in the application domain that must be represented by data. Examples of entity types can be found in a typical line of business (LOB) application and might include employees, customers, orders, order-lines, suppliers, shippers, products, invoices, and so on.

Logical connections between entities are called relationships: for example, the logical association between an employee and his/her contact information or between a merchandise order and the customer who makes the order. An EDM relationship type describes the possible associations between entities, and any constraints on multiplicity.

Schemas

The EDM provides an XML syntax named conceptual schema definition language (CSDL). A CSDL schema is used to specify entities and relationships in the domain of an application.

 

The CSDL schema will become the core data model of the application. It is usually referred to simply as the “conceptual schema”. A CSDL schema declares and defines a Namespace that contains entities and associations. An Alias can be assigned to save typing in the syntax that will follow. A schema is declared using the following XML:

 

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

<Schema Namespace="AdventureWorksModel"

    Alias="AdventureWorksModel"

      xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

  …

</Schema>

 

 

Entities

If the application requires a data type to represent an employee, the schematic representation of the employee type in CSDL looks something like this:

 

  <EntityType Name="Employee" Key="EmployeeID">

 

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

    <Property Name="LoginID" Type="String" Nullable="false"

        MaxLength="256" />

    <Property Name="Title" Type="String" Nullable="false"

        MaxLength="50" />

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

    <Property Name="HireDate" Type="DateTime"

        Nullable="false"/>

    <Property Name="SalariedFlag" Type="Boolean"

        Nullable="false" />

    <Property Name="VacationHours" Type="Int16"

        Nullable="false" />

    <Property Name="SickLeaveHours" Type="Int16"

        Nullable="false" />

    <Property Name="CurrentFlag" Type="Boolean"

        Nullable="false" />

  <!--Other properties-->

 

    <NavigationProperty Name="Contact"

        Relationship="AdventureWorksModel.FK_Employee_Contact_ContactID"

        FromRole="Employee" ToRole="Contact" />

 

    <NavigationProperty Name="Managed_Employees"

        Relationship="AdventureWorksModel.FK_Employee_Employee_ManagerID"

        FromRole="Employee" ToRole="ManagedEmployee" />

 

      </EntityType>

 

 

You don’t have to understand everything in this syntax to see that it declares an entity type named Employee and that it contains properties of various data types.

 

Properties of Entities

The first two lines of the example include declaration of the Name and Key attributes of the entity. When an instance of the Employee entity is created, the Key property is assigned an integer value that uniquely identifies the instance for all operations in which it is used.

 

The Key attribute can be any sequence of properties. Using an integer is only one possibility. The Key could also be a GUID or the combined sequence of one or more properties separated by a space in the attribute declaration.

 

Each property of the Employee type has a simple data type and several facets that further constrain the data that can be assigned to the property. For example, the Employee BirthDate property is of type DateTime. It has one designated facet: Nullable=”false”. The Nullable facet specifies that the BirthDate property cannot be null. The Employee entity also contains a LoginID property. This property is of type String. The facets of the String data include Nullable=”false” and MaxLength=”256”.

 

Attributes declared as Type=”x” are EDM types, each of which has a corresponding CLR type and corresponding SQL type. (The actual SQL type depends on the underlying database.)

 

The properties of this Employee entity type align with an Employee table in the Adventureworks sample database that ships with Microsoft® SQL Server™ 2005. Using the schemas in this article, you can build a class library mapped to data in Adventureworks. This class library would contain an Employee class to represent the Employee entity type.

 

The last six lines of the CSDL syntax shown above declare two navigation properties on the Employee type. We’ll return to these later.

 

To make our example more interesting, we also declare a Contact entity type.

Contact entities contain properties that will be assigned the various data needed to identify and locate a person or business.

 

  <EntityType Name="Contact" Key="ContactID">

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

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

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

    <Property Name="FirstName" Type="String" Nullable="false"

         MaxLength="50" />

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

    <Property Name="LastName" Type="String" Nullable="false"

         MaxLength="50" />

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

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

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

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

    <Property Name="PasswordHash" Type="String" Nullable="false"

         MaxLength="128" />

    <Property Name="PasswordSalt" Type="String" Nullable="false"

         MaxLength="10" />

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

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

  </EntityType>

 

 

Associations and Navigation Properties

 

An Association between Employee and Contact entities is declared and defined in the following CSDL syntax.

 

  <Association Name="FK_Employee_Contact_ContactID">

    <End Role="Contact" Type="AdventureWorksModel.Contact"

        Multiplicity="1..1" />

    <End Role="Employee" Type="AdventureWorksModel.Employee"

        Multiplicity="1..*" />

  </Association>

 

 

Recall that the definition of the Employee entity includes the NavigationProperty between the Employee entity and the Contact entity. This NavigationProperty makes it possible to follow an instance of an Employee entity according to the Association above to an instance of a related Contact entity or vice versa. The Association above declares the End elements of the Association and defines Role, Type, and Multiplicity attributes of these End elements.

 

The Multiplicity attributes of the End elements of the Association specify the number of instances of each End element that can participate in the Association. In the Association named FK_Employee_Contact_ContactID, one Employee can be related to any number of Contact instances; that is, the Multiplicity of the Employee End is “1..*” and the Contact End is “1”.

 

The attributes of the End elements specify a logical Role in the relationship and an explicit type for each End.

 

Type names are qualified by the name of the schema Namespace in which they are declared. In the example above, the schema namespace name is AdventureworksModel.

 

The FK_ prefix on the Association name follows a convention that suggests the corresponding foreign key constraint in the database that exists between an instance of the Employee entity and an instance of the Contact entity. This convention is not enforced by the EDM.

 

The second NavigationProperty in the Employee entity uses an Association in which both End elements are Employee entities. This Association is declared in the following CSDL syntax.

 

<Association Name="FK_Employee_Employee_ManagerID">

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

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

  </Association>

 

 

Note that the Type attributes of both End elements of this Association are specified as type AdventureWorksModel.Employee. The NavigationProperty is named Managed_Employees to indicate the manager/managed employees relationship between instances of the Employee entity. In this schema relationship, an employee can manage any number of other employees.

 

This concludes the introduction to fundamentals of CSDL syntax used in declarations of entity and association types. In part 2 of this discussion we’ll explore the logical groupings of entities and associations called entity sets and association sets that map to tables in storage.

 

Mike Dodaro

Programming Writer for ADO.NET

 

Entity Data Model 101 - Part 2

 

Entity Data Model 101 - Part 3