LINQ to SQL Integrated Code Review

Mapping the Class and Database Objects Using XML Mapping File

Code Review

Code Walkthrough

The following XML elements are used to define the key objects in the database model:

  • Database - map the database attributes.
  • Table - map specific table attributes.
  • Type - map a database object to an application specific type.
  • Column - map column attributes.
  • Association - map a primary key/foreign key relationship.
  • ElementType - map stored procedure output to application specific types.

The below XML is stored in the PersonLinqToSqlMapping.xml file and is used to specify mapping between the data model of the database and the application object model. The SqlMetal command-line tool can also be used to generate code and mappings.

<?xml version="1.0" encoding="utf-8" ?> <Database Name="Foo" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  • Name - name of the database being mapped to.
  • xmlns - used to reference the LinqToSqlMapping.xsd schema to get intellisense as you create the XML mappings.

Define the mapping between the Person database table and the Person class (object).

<Table Name="Person" Member="PersonLINQAdvanced.Person"> <Type Name="PersonLINQAdvanced.Person"> <Column Name="ID" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDRole" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL" /> <Column Name="LastName" Member="LastName" Storage="m_lastName" DbType="nvarchar NOT NULL" /> <Column Name="FirstName" Member="FirstName" Storage="m_firstName" DbType="nvarchar NOT NULL" /> <Association Name="FK_Person_Role" Member="Role" Storage="m_role" ThisKey="RoleID" OtherKey="RoleID" IsForeignKey="true"/> <Association Name="FK_Salary_Person" Member="Salaries" Storage="m_salaries" OtherKey="PersonID" /> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Person object perspective.
  • OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Person object perspective.
  • IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

Define the mapping between the Role database table and the Role class (object).

<Table Name="Role" Member="PersonLINQAdvanced.Role"> <Type Name="PersonLINQAdvanced.Role"> <Column Name="ID" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="RoleDescription" Member="RoleDescription" Storage="m_roleDescription" DbType="nvarchar(50) NOT NULL" /> <Association Name="FK_Person_Role" Member="People" Storage="m_people" OtherKey="RoleID" /> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • OtherKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Role object perspective.

Define the mapping between the Salary database table and the Salary class (object).

<Table Name="Salary" Member="PersonLINQAdvanced.Salary"> <Type Name="PersonLINQAdvanced.Salary"> <Column Name="ID" Member="SalaryID" Storage="m_salaryID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDPerson" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="Year" Member="Year" Storage="m_year" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="SalaryYear" Member="SalaryYear" Storage="m_salaryYear" DbType="numeric(18,0) NOT NULL" /> <Association Name="FK_Salary_Person" Member="Person" Storage="m_person" ThisKey="PersonID" OtherKey="PersonID" IsForeignKey="true"/> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Salary object perspective.
  • OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Salary object perspective.
  • IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

More Information

Code Review

Microsoft.NET

Share Content