Tutorial 4: Configuring mapping between Database Table(s) and Data Model Class(es) using an external XML file

Description Article
Table of Contents
Summary

The mapping of each entity class of the Data Model to a database table is achived through the use of an external XML file.

Description

The Xml Schema file is as below:

?<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://schemas.microsoft.com/linqtosql/mapping/2007" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"
elementFormDefault="qualified" >
  <xs:element name="Database" type="Database" />
  <xs:complexType name="Database">
    <xs:sequence>
      <xs:element name="Table" type="Table" minOccurs="0" maxOccurs="unbounded" />
      <xs:element name="Function" type="Function" minOccurs="0" maxOccurs="unbounded" />
    </xs:sequence>
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Provider" type="xs:string" use="optional" />
  </xs:complexType>
  <xs:complexType name="Table">
    <xs:sequence>
      <xs:element name="Type" type="Type" minOccurs="1" maxOccurs="1" />
    </xs:sequence>
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Member" type="xs:string" use="optional" />
  </xs:complexType>
  <xs:complexType name="Type">
    <xs:sequence>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Column" type="Column" minOccurs="0" maxOccurs="unbounded" />
        <xs:element name="Association" type="Association" minOccurs="0" maxOccurs="unbounded" />
      </xs:choice>
      <xs:element name="Type" type="Type" minOccurs="0" maxOccurs="unbounded" />
    </xs:sequence>
    <xs:attribute name="Name" type="xs:string" use="required" />
    <xs:attribute name="InheritanceCode" type="xs:string" use="optional" />
    <xs:attribute name="IsInheritanceDefault" type="xs:boolean" use="optional" />
  </xs:complexType>
  <xs:complexType name="Column">
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Member" type="xs:string" use="required" />
    <xs:attribute name="Storage" type="xs:string" use="optional" />
    <xs:attribute name="DbType" type="xs:string" use="optional" />
    <xs:attribute name="IsPrimaryKey" type="xs:boolean" use="optional" />
    <xs:attribute name="IsDbGenerated" type="xs:boolean" use="optional" />
    <xs:attribute name="CanBeNull" type="xs:boolean" use="optional" />
    <xs:attribute name="UpdateCheck" type="UpdateCheck" use="optional" />
    <xs:attribute name="IsDiscriminator" type="xs:boolean" use="optional" />
    <xs:attribute name="Expression" type="xs:string" use="optional" />
    <xs:attribute name="IsVersion" type="xs:boolean" use="optional" />
    <xs:attribute name="AutoSync" type="AutoSync" use="optional" />
  </xs:complexType>
  <xs:complexType name="Association">
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Member" type="xs:string" use="required" />
    <xs:attribute name="Storage" type="xs:string" use="optional" />
    <xs:attribute name="ThisKey" type="xs:string" use="optional" />
    <xs:attribute name="OtherKey" type="xs:string" use="optional" />
    <xs:attribute name="IsForeignKey" type="xs:boolean" use="optional" />
    <xs:attribute name="IsUnique" type="xs:boolean" use="optional" />
    <xs:attribute name="DeleteRule" type="xs:string" use="optional" />
    <xs:attribute name="DeleteOnNull" type="xs:boolean" use="optional" />
  </xs:complexType>
  <xs:complexType name="Function">
    <xs:sequence>
      <xs:element name="Parameter" type="Parameter" minOccurs="0" maxOccurs="unbounded" />
      <xs:choice>
        <xs:element name="ElementType" type="Type" minOccurs="0" maxOccurs="unbounded" />
        <xs:element name="Return" type="Return" minOccurs="0" maxOccurs="1" />
      </xs:choice>
    </xs:sequence>
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Method" type="xs:string" use="required" />
    <xs:attribute name="IsComposable" type="xs:boolean" use="optional" />
  </xs:complexType>
  <xs:complexType name="Parameter">
    <xs:attribute name="Name" type="xs:string" use="optional" />
    <xs:attribute name="Parameter" type="xs:string" use="required" />
    <xs:attribute name="DbType" type="xs:string" use="optional" />
    <xs:attribute name="Direction" type="ParameterDirection" use="optional" />
  </xs:complexType>
  <xs:complexType name="Return">
    <xs:attribute name="DbType" type="xs:string" use="optional" />
  </xs:complexType>
  <xs:simpleType name="UpdateCheck">
    <xs:restriction base="xs:string">
      <xs:enumeration value="Always" />
      <xs:enumeration value="Never" />
      <xs:enumeration value="WhenChanged" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="ParameterDirection">
    <xs:restriction base="xs:string">
      <xs:enumeration value="In" />
      <xs:enumeration value="Out" />
      <xs:enumeration value="InOut" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="AutoSync">
    <xs:restriction base="xs:string">
      <xs:enumeration value="Never" />
      <xs:enumeration value="OnInsert" />
      <xs:enumeration value="OnUpdate" />
      <xs:enumeration value="Always" />
      <xs:enumeration value="Default" />
    </xs:restriction>
  </xs:simpleType>
</xs:schema>
XSD Schema location

ProgramFiles\Microsoft Visual Studio 9.0\Xml\Schemas\LiqbToSqlMapping.xsd

Note

External mapping overrides Linq attribute-based mapping and therefore the DataContext object will ignore any mapping attribute created on classes.

If the Linq To SQL component was created automatically through the Visual Studio Designer then the XML file can be obtained by opening the “.dbml” file as a XML file type.

When To Use It

Add a Linq To SQL component based on XML mapping files when

  • The mapping code is to be kept out of the application code
  • The entitiy classes already exists
  • The entitiy classes already exists or are created by a third-party tool for code generation other than the Visual Studio Designe
How to Use It

In order to use Linq to SQL through XML mapping files the steps below must be followed. Refer to the operations section for examples.

  1. Create Entity classes as POCO classes
  2. Create XML mapping file
  3. Create a standard DataContext class OR create a custom DataContext class if stored procedures are included
  4. Load XML mampping file into the DataContext class
Operations
Mapping a database table to the correspondent Data Model class and a database stored procedure to a class method

The SQL code/NET code/XML file below shows how Basic mapping is to be considered.

Remarks

Note that In the XML mapping files:
i) “Member” node represents the corresponding namespace+class name
ii) In case of a stored procedure configuration the “ElementType” node represents the corresponding namespace+class name

How to do it
<?xml version="1.0" encoding="utf-8" ?>
<Database Name="AdventureWorksLT" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="SalesLT.Customer" Member="Model.Customer">
    <Type Name="Model.Customer">
      <Column Name="CustomerID" Member="CustomerID" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Title" Member="Title" />
      <Column Name="FirstName" Member="FirstName" />
      <Column Name="LastName" Member="LastName" />
      <Column Name="ModifiedDate" Member="ModifiedDate" />
    </Type>
  </Table>
  <Function Name="dbo.sp_GetCustomerByLastName" Method="GetCustomerByLastName">
    <Parameter Name="lastNameLetter" Parameter="LastNameLetter" />
    <ElementType Name="Model.Customer" />
  </Function>
</Database>
namespace Model
{
    public class Customer
    {
        public int CustomerID { get; set; }
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime ModifiedDate { get; set; }
    }
}
CREATE PROCEDURE dbo.sp_GetCustomerByLastName
    (
        @LastNameLetter char(1)
    )
AS
    BEGIN
        SELECT CustomerID, Title, FirstName, LastName, ModifiedDate
        FROM SalesLT.Customer
        WHERE LastName LIKE @LastNameLetter + '%'
    END
Mapping two related database tables to the correspondent Data Model classes
How to do it
<?xml version="1.0" encoding="utf-8"?>
<Database Name="Training" Class="TrainingDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection/>
  <Table Name="dbo.Customers" Member="Customers">
    <Type Name="Customer">
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="CompanyName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
      <Column Name="ContactName" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="ContactTitle" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
      <Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
      <Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
      <Column Name="Fax" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
      <Association Name="Customer_Order" Member="Orders" ThisKey="CustomerID" OtherKey="CustomerID" Type="Order" />
    </Type>
  </Table>
  <Table Name="dbo.Orders" Member="Orders">
    <Type Name="Order">
      <Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
      <Column Name="EmployeeID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="OrderDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="RequiredDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="ShippedDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="ShipVia" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Freight" Type="System.Decimal" DbType="Money" CanBeNull="true" />
      <Column Name="ShipName" Type="System.String" DbType="NVarChar(40)" CanBeNull="true" />
      <Column Name="ShipAddress" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
      <Column Name="ShipCity" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="ShipRegion" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="ShipPostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
      <Column Name="ShipCountry" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Association Name="Customer_Order" Member="Customer" ThisKey="CustomerID" OtherKey="CustomerID" Type="Customer" IsForeignKey="true" />
    </Type>
  </Table>
</Database>
Creating a basic DataContext class using a XML mapping file
How to do it
var lvConnectionString = "Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa;Password=;"

// Create a DataContext to the database, and supply the url for the mapping file

XmlMappingSource mapping = XmlMappingSource.FromUrl("mapping.xml");
DataContext dc = new DataContext(lvConnectionString, mapping);
Creating a custom DataContext class using a XML mapping file
How to do it
var lvConnectionString = "Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa;Password=;"

string lvFile = @"c:\mapping.xml";
XmlMappingSource mapping = XmlMappingSource.FromXml (File.ReadAllText(lvFile));

var dc = new TrainingDataContext(lvConnectionString, mapping);
Creating a custom DataContext class using a XML mapping file compiled as an embbeded resource
How to do it
XmlMappingSource mapping;

using (Stream s = Assembly.GetExecutingAssembly().GetManifestResourceStream("mapping.xml");
{
	mapping = XmlMappingSource.FromStream(s);
}

var lvConnectionString = "Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa;Password=;"
var db = new TrainingDataContext(lvConnectionString, mapping);