Linq to SQL Xml Based Mapping
In the last post I talked about Linq to SQL Attribute Based Mapping that lets you map CLR Objects to database objects using attributes. Although this approach is very simple and easy, it is compiled with the code and cannot be changed without recompiling. Another approach is Linq to SQL Xml Based Mapping that maps entities to database objects according to an xml file that is loaded in runtime.
So, given the two entities from the previous post, that have no mapping attributes at all:
class Order
{
public int Id { get; set; }
public DateTime? OrderDate { get; set; }
public string CustomerId { get; set; }
private EntityRef<Customer> _customerRef;
public Customer Customer
{
get { return this._customerRef.Entity; }
set { this._customerRef.Entity = value; }
}
}
and:
public class Customer
{
public string CustomerId { get; set; }
}
The Xml Based Mapping schema should look like:
<Database Name="Northwind"
xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customers" Member="Customers">
<Type Name="XmlBasedMapping.Customer">
<Column Name="CustomerID"
Member="CustomerId"
CanBeNull="false"
IsPrimaryKey="true" />
</Type>
</Table>
<Table Name="dbo.Orders" Member="Orders">
<Type Name="XmlBasedMapping.Order">
<Column Name="OrderID"
Member="Id" IsPrimaryKey="true" IsDbGenerated="true" />
<Column Name="CustomerID"
Member="CustomerId" DbType="NChar(5)" />
<Column Name="OrderDate"
Member="OrderDate" DbType="DateTime" />
<Association Name="Orders_Customers"
Member="Customers"
ThisKey="CustomerId"
OtherKey="CustomerId"
IsForeignKey="true" />
</Type>
</Table>
</Database>
The root element is the Database element. The child elements are the database objects the are included in the mapping - Customers and Orders tables from the Northwind database. Each table can have child types that are mapped to entities in the application. This hierarchy sits also with the concept of inheritance in Linq to SQL since it only supports the Table per Class Hierarchy strategy. In the above example each table is mapped to a single entity. Notice that each table column is mapped to a member in the class.
To work with this mapping source, we should load it from a file / stream / url or any other resource, and supply it as a parameter for the DataContext instance we want to work with.
string connectionString = "...";
// Load the Mapping from a file
XmlMappingSource mapping = XmlMappingSource.FromUrl("NorthwindMap.xml");
// Create a DataContext to the database, and supply
// the url for the mapping file
DataContext ctx = new DataContext(connectionString, mapping);
var query = from order in ctx.GetTable<Order>()
where order.CustomerId == "ALFKI"
select order;
foreach (Order order in query)
{
Console.WriteLine(order.Id + " " + order.OrderDate + " " + order.CustomerId);
}
Enjoy!