The XML schema's for DLinq

Tonight I browsed some more through the documentation of DLinq (I’ll refer to Linq to SQL this way, because it is shorter and until the namespace is changed from System.Data.DLinq). In particular I looked at the document ‘DLinq Overview for C# Developers’.

There is a section called ‘Generator Tool XML Reference’, that lists a prototypical example of the XML syntax used for the mapping without attributes. But actually, there are two types of XML around:

1. SQL metadata for a database
   Generated by running /xml on SqlMetal
   Used for code and mapping file generation
2. External mapping files
   Generated by running /map on SqlMetal (fragment listed below)

<?xml version="1.0" encoding="utf-8"?>
<Database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="Northwind">
  <Table Name="Orders">
    <Type Name=".Orders">
      <Column Name="OrderID" Member="OrderID" Storage="_OrderID" DbType="Int NOT NULL IDENTITY" IsIdentity="True" IsAutoGen="True" />
      <Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5)" />
      <Column Name="EmployeeID" Member="EmployeeID" Storage="_EmployeeID" DbType="Int" />
      ...
      <Association Name="FK_Order_Details_Orders" Member="OrderDetails" Storage="_OrderDetails" ThisKey="OrderID" OtherTable="Order Details" OtherKey="OrderID" />
    </Type>
</Table>

You can use the SqlMetal tool to give you a headstart on creating the domain objects. You may find yourself deleting some of the classes, Table and EntityRef definitions that you do not want, from the generated code. But, once you start down that path, it is pretty hard to regenerate the code, without having to go through the process of deleting and additing again.

So here’s a tip for you, so you might delay the manual editing of the generated code just a little longer. You first create a dump of a database’s schema, like so:

SqlMetal.exe /server:. /database:Northwind /xml:NorthwindSchema.xml

After that you can add Hidden and Access attributes to all elements of the XML file. All elements share these two attributes (and Name, btw), which are used during code generation. Hidden is used to determine whether the corresponding code fragment is emitted. E.g. an Association element becomes an EntitySet. The Access attribute determines the access modifier of the code fragment or type, which can be public, internal or even protected and private for situations where the modifier is allowed that value. It is still editing the generated XML, but at least not the generated code.

<Table Name="Products">
  <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsIdentity="true" IsAutoGen="true" IsReadOnly="false" Precision="10" Scale="255" />
  <Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" Nullable="false" StringLength="40" />
  <Column Access="private" Name="SupplierID" Type="System.Int32" DbType="Int" Nullable="true" Precision="10" Scale="255" />
  <Column Access="private" Name="CategoryID" Type="System.Int32" DbType="Int" Nullable="true" Precision="10" Scale="255" />
  …
  <Association Hidden="true" Name="FK_Order_Details_Products" Property="OrderDetails"Kind="ManyToOneChild" Target="Order Details">
    <Column Name="ProductID" />
  </Association>
  <Association Name="FK_Products_Categories" Property="Categories" Kind="ManyToOneParent" Target="Categories">
    <Column Name="CategoryID" />
  </Association>

Then run the next command:

SqlMetal.exe /code:NorthwindDomain.cs NorthwindSchema.xml /pluralize /namespace:Northwind.Domain

and you will notice that the EntityRef<OrderDetails> does no longer exist and that the properties for SupplierID and CategoryID are private:

[Column(Storage="_CategoryID", DBType="Int")]
private System.Nullable<int> CategoryID {
  get {
    return this._CategoryID;
  }
  set {
    if ((this._CategoryID != value)) {
      this.OnPropertyChanging("CategoryID");
      this._CategoryID = value;
      this.OnPropertyChanged("CategoryID");
    }
  }
}

In some other blog entry I will write about why you might want to hide certain properties from the public interface of your objects.

Anyway, surely a nice visual tool will become available for Visual Studio to generate and produce the schema XML and edit it. You can use the DLinq Designer right now, but it does not use the schema file as the basis for code generation. You should give it a try, though.