Availability of EDM Schema Files for AdventureWorks Samples?

The primary reason why we didn't include a schema and mapping for AdventureWorks was that at the time we cut the CTP bits we didn't support multiple owning schemas in a database (we do now, but we already shipped the CTP :), and AdventureWorks is divided into multiple schemas.

Here are the metadata files I used when I wrote the article (or at least a similar version, it's been a while since I wrote that). I can't attach files so they're copy-pasted below. Since AdventureWorks won't do, I'm attaching a SQL script that will create a new database based on AdventureWorks (so you still need AdventureWorks as a data source). I haven't looked at this SQL script for a while, so it might be a bit outdated, so you may need to tweak it a bit.

Usual disclaimer applies: this is an example, use it at your own risk, the content provides no warranties and confers no rights.

Pablo Castro
ADO.NET Technical Lead
Microsoft Corporation

SQL script:

-- CREATE DATABASE AWv3

USE AWv3

GO

IF OBJECT_ID('SalesOrder', 'U') IS NOT NULL DROP TABLE SalesOrder

CREATE TABLE SalesOrder (

SalesOrderID INT NOT NULL PRIMARY KEY,

SalesPersonID INT NULL,

OrderDate DATETIME NULL,

Status TINYINT NULL,

OnlineOrderFlag BIT NULL,

AccountNumber NVARCHAR(15) NULL,

TaxAmt MONEY NULL,

TotalDue MONEY NULL,

)

IF OBJECT_ID('SalesPerson', 'U') IS NOT NULL DROP TABLE SalesPerson

CREATE TABLE SalesPerson (

SalesPersonID INT NOT NULL,

SalesQuota MONEY NULL,

Bonus MONEY NULL DEFAULT ((0.00)),

SalesYTD MONEY NULL DEFAULT ((0.00)),

SalesLastYear MONEY NULL DEFAULT ((0.00))

)

IF OBJECT_ID('Employee', 'U') IS NOT NULL DROP TABLE Employee

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY NOT NULL,

LoginID NVARCHAR(256) NULL,

ManagerID INT NULL,

Title NVARCHAR(50) NULL,

BirthDate DATETIME NULL,

HireDate DATETIME NULL,

SalariedFlag BIT NULL DEFAULT ((1)),

VacationHours SMALLINT NULL DEFAULT ((0)),

SickLeaveHours SMALLINT NULL DEFAULT ((0)),

CurrentFlag BIT NULL DEFAULT ((1))

)

IF OBJECT_ID('Contact', 'U') IS NOT NULL DROP TABLE Contact

CREATE TABLE Contact (

ContactID INT PRIMARY KEY NOT NULL,

FirstName NVARCHAR(50) NOT NULL,

MiddleName NVARCHAR(50) NULL,

LastName NVARCHAR(50) NULL,

EmailAddress NVARCHAR(50) NULL,

Phone NVARCHAR(25) NULL,

)

GO

-- Init data from AdventureWorks

INSERT INTO SalesOrder

SELECT SalesOrderID, SalesPersonID = e.ContactID, OrderDate, Status, OnlineOrderFlag, AccountNumber,

TaxAmt, TotalDue

FROM AdventureWorks.Sales.SalesOrderHeader s

LEFT OUTER JOIN AdventureWorks.HumanResources.Employee e

ON s.SalesPersonID = e.EmployeeID

INSERT INTO SalesPerson

SELECT SalesPersonID = e.ContactID, SalesQuota, Bonus, SalesYTD, SalesLastYear

FROM AdventureWorks.Sales.SalesPerson p

INNER JOIN AdventureWorks.HumanResources.Employee e

ON p.SalesPersonID = e.EmployeeID

INSERT INTO Employee

SELECT e.ContactID AS EmployeeID, e.LoginID, e.ManagerID, e.Title,

e.BirthDate, e.HireDate, e.SalariedFlag,

e.VacationHours, e.SickLeaveHours, e.CurrentFlag

FROM AdventureWorks.HumanResources.Employee e

INSERT INTO Contact

SELECT ContactID, FirstName, MiddleName, LastName, EMailAddress, Phone

FROM AdventureWorks.Person.Contact p

CSDL:

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

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

<EntityType Name="SalesOrder" Key="ID">

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

<Property Name="OrderDate" Type="DateTime" Nullable="true" DateTimeKind="Unspecified" PreserveSeconds="true" />

<Property Name="Status" Type="Byte" Nullable="true" />

<Property Name="AccountNumber" Type="String" Nullable="true" MaxLength="15" Unicode="true" Collation="" />

<Property Name="TotalDue" Type="Decimal" Nullable="true" Precision="24" Scale="4" />

<NavigationProperty Name="SalesPerson" FromRole="Order" ToRole="SalesPerson" Relationship="Self.SalesPerson_Order" />

</EntityType>

<EntityType Name="StoreSalesOrder" BaseType="Self.SalesOrder">

<Property Name="Tax" Type="Decimal" Nullable="true" Precision="24" Scale="4" />

</EntityType>

<EntityType Name="SalesPerson" Key="ID">

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

<Property Name="SalesQuota" Type="Decimal" Nullable="true" Precision="24" Scale="4" />

<Property Name="Bonus" Type="Decimal" Nullable="true" Precision="24" Scale="4" />

<Property Name="SalesYTD" Type="Decimal" Nullable="true" Precision="24" Scale="4" />

<Property Name="HireDate" Type="DateTime" Nullable="true" DateTimeKind="Unspecified" PreserveSeconds="true" />

<Property Name="Title" Type="String" Nullable="true" MaxLength="50" Unicode="true" Collation="" />

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

<Property Name="MiddleName" Type="String" Nullable="true" MaxLength="50" Unicode="true" Collation="" />

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

<NavigationProperty Name="Orders" FromRole="SalesPerson" ToRole="Order" Relationship="Self.SalesPerson_Order" />

</EntityType>

<Association Name="SalesPerson_Order">

<End Role="Order" Type="Self.SalesOrder" Multiplicity="0..*" />

<End Role="SalesPerson" Type="Self.SalesPerson" Multiplicity="1" />

</Association>

<EntityContainer Name="AdventureWorks">

<EntitySet Name="SalesOrders" EntityType="Self.SalesOrder" />

<EntitySet Name="SalesPeople" EntityType="Self.SalesPerson" />

<AssociationSet Name="SalesPersonOrders" Association="Self.SalesPerson_Order">

<End EntitySet="SalesPeople" Role="SalesPerson" />

<End EntitySet="SalesOrders" Role="Order" />

</AssociationSet>

</EntityContainer>

</Schema>

MSL:

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

<Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS"

xmlns:cdm="urn:schemas-microsoft-com:windows:storage:mapping:CS"

cdm:Space="C-S"

>

<Alias cdm:Key="Model" cdm:Value="AdventureWorksModel"/>

<Alias cdm:Key="Target" cdm:Value="AdventureWorksTarget"/>

<EntityContainerMapping cdm:CdmEntityContainer="Model.AdventureWorks"

cdm:StorageEntityContainer="Target.dbo">

<EntitySetMapping cdm:Name="SalesOrders">

<EntityTypeMapping cdm:TypeName="Model.StoreSalesOrder">

<TableMappingFragment cdm:TableName="SalesOrder">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="SalesOrderID" />

<ScalarProperty cdm:Name="OrderDate" cdm:ColumnName="OrderDate" />

<ScalarProperty cdm:Name="Status" cdm:ColumnName="Status" />

<ScalarProperty cdm:Name="AccountNumber" cdm:ColumnName="AccountNumber" />

<ScalarProperty cdm:Name="TotalDue" cdm:ColumnName="TotalDue" />

<ScalarProperty cdm:Name="Tax" cdm:ColumnName="TaxAmt" />

<Condition cdm:ColumnName="OnlineOrderFlag" cdm:Value="false" />

</TableMappingFragment>

</EntityTypeMapping>

<EntityTypeMapping cdm:TypeName="Model.SalesOrder">

<TableMappingFragment cdm:TableName="SalesOrder">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="SalesOrderID" />

<ScalarProperty cdm:Name="OrderDate" cdm:ColumnName="OrderDate" />

<ScalarProperty cdm:Name="Status" cdm:ColumnName="Status" />

<ScalarProperty cdm:Name="AccountNumber" cdm:ColumnName="AccountNumber" />

<ScalarProperty cdm:Name="TotalDue" cdm:ColumnName="TotalDue" />

<Condition cdm:ColumnName="OnlineOrderFlag" cdm:Value="true" />

</TableMappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

<EntitySetMapping cdm:Name="SalesPeople">

<EntityTypeMapping cdm:TypeName="Model.SalesPerson">

<TableMappingFragment cdm:TableName="SalesPerson">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="SalesPersonID" />

<ScalarProperty cdm:Name="SalesQuota" cdm:ColumnName="SalesQuota" />

<ScalarProperty cdm:Name="Bonus" cdm:ColumnName="Bonus" />

<ScalarProperty cdm:Name="SalesYTD" cdm:ColumnName="SalesYTD" />

</TableMappingFragment>

<TableMappingFragment cdm:TableName="Employee">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="EmployeeID" />

<ScalarProperty cdm:Name="HireDate" cdm:ColumnName="HireDate" />

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

</TableMappingFragment>

<TableMappingFragment cdm:TableName="Contact">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="ContactID" />

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

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

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

</TableMappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

<AssociationSetMapping cdm:Name="SalesPersonOrders" cdm:TypeName="Model.SalesPerson_Order" cdm:TableName="SalesOrder">

<EndProperty cdm:Name="SalesPerson">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="SalesPersonID"/>

</EndProperty>

<EndProperty cdm:Name="Order">

<ScalarProperty cdm:Name="ID" cdm:ColumnName="SalesOrderID" />

</EndProperty>

</AssociationSetMapping>

</EntityContainerMapping>

</Mapping>

SSDL:

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

<Schema

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

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

Namespace="AdventureWorksTarget"

Alias="Self"

>

<EntityContainer Name="dbo">

<EntitySet Name="SalesOrder" EntityType="Self.TSalesOrder" />

<EntitySet Name="SalesPerson" EntityType="Self.TSalesPerson" />

<EntitySet Name="Employee" EntityType="Self.TEmployee" />

<EntitySet Name="Contact" EntityType="Self.TContact" />

</EntityContainer>

<EntityType Name="TSalesOrder" Key="SalesOrderID">

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

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

<Property Name="OrderDate" Type="datetime"/>

<Property Name="Status" Type="tinyint"/>

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

<Property Name="AccountNumber" Type="nvarchar" MaxLength="15"/>

<Property Name="TaxAmt" Type="decimal" Precision="24" Scale="4"/>

<Property Name="TotalDue" Type="decimal" Precision="24" Scale="4"/>

</EntityType>

<EntityType Name="TSalesPerson" Key="SalesPersonID">

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

<Property Name="SalesQuota" Type="decimal" Precision="24" Scale="4"/>

<Property Name="Bonus" Type="decimal" Precision="24" Scale="4"/>

<Property Name="SalesYTD" Type="decimal" Precision="24" Scale="4"/>

<Property Name="SalesLastYear" Type="decimal" Precision="24" Scale="4"/>

</EntityType>

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

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

<Property Name="LoginID" Type="nvarchar" MaxLength="256"/>

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

<Property Name="BirthDate" Type="datetime"/>

<Property Name="HireDate" Type="datetime"/>

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

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

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

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

</EntityType>

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

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

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

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

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

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

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

</EntityType>

</Schema>

pablo-ms at 2007-8-31 >

Pablo,

Only a minor fix to the query script was required (PRIMARY_KEY for SalesOrders), and the schema files compiled with no problems in C#. However, I had to change Order/order to SalesOrder throughout the three files to compile it in VB.

I get the feeling that keyword conflicts might become a major issue down the road. Also, I assume the problem with composite primary keys will get fixed (e.g., [Order Details]).

Thanks for the help,

--rj

RogerJennings at 2007-8-31 >

Hi,

You can download a project (class library) that contains the above AWv3 model. Read this post.

Guy Burstein

http://blogs.microsoft.co.il/blogs/bursteg/

GuyBurstein at 2007-8-31 >