Linq to SQL Xml Based Mapping

27 בספטמבר 2007

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!

Add comment
facebook linkedin twitter email

3 comments

  1. Michael28 ביולי 2008 ב 9:53

    So if your table in the db changed you also have to update the code of entities, is there any work around not need to update the code of entities and compile your project when your db changed just the replace the map file?

  2. Gunnar11 ביוני 2009 ב 3:27

    Thanks for sharing! I wanted to ask if it is possible to avoid keeping parent object ID in child object? I mean if I have Product and ProductCategory classes and XML mapping then I don't want to keep ProductCategoryId in Product class. Is it somehow possible to achieve?

Comments are closed.