Auto-Generate Code for LINQ to SQL Repository Pattern using T4

Using LINQ to SQL is simple but introduces code that is tightly-coupled with this kind of data access method. One way to solve this is to use the repository pattern. With T4, implementing this pattern has become easier.

Introduction

I really like LINQ to SQL because it is easy to setup and use in my opinion. However, it is not quite easy to create unit tests for methods that call LINQ to SQL methods because the DataContext will need to connect to an actual SQL Server database. Moreover, moving from LINQ to SQL to another technology to use for database access would be hard if the design is not loosely-coupled. One design pattern that can address these issues is the repository pattern. I found this blog post by Fredrick Kalseth that discusses applying the repository pattern together with LINQ to SQL to an application. To summarize what was discussed, the auto-generated classes corresponding to the database tables (generated either by the Object Relational (O/R) Designer or the SQLMetal command-line tool) must implement interfaces that expose their properties. Meanwhile, the DataContext class will be wrapped by an IDataContext object that will expose its functionalities. In short, we will create our code that only knows about these interfaces and we won’t be tied to a specific database access implementation.

The problem with this approach is writing the interfaces and implementing them for all the generated tables. Coding them manually will take a lot of time especially when your database has many tables. It is also prone to human mistakes. So I decided to automate that process. At first, I thought of extending visual studio (adding commands and menu items to Visual Studio). However, I decided to use T4 (Text Template Transformation Toolkit) because it is much simpler to do. You can check the MSDN site for more details regarding T4.

An Example

To explain more clearly what we are going to do, I created a Visual Studio 2010 solution containing a DBML file. The contents of the DBML file is shown in the following figure using the O/R Designer.


Figure 1. Database Example

So we have 3 tables: Order, Customer and Product. The Order table is referencing the Customer and Product tables. The generated Order class follows the following structure. I left out the implementation details for brevity.

public partial class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public int ProductId { get; set; }
public Customer Customer { get; set; }
public Product Product { get; set; }
}

Listing 1. Generated Order Class

What we want to do here is to have an IOrder interface which is similar to the preceding code. The Order class will then implement the said interface. However, instead of referencing Customer and Product classes in the IOrder interface, we have to reference the ICustomer and IProduct interfaces since the interfaces need not know anything about the LINQ to SQL classes. Assuming that we have created the IOrder interface, the Order class’ implementation of that interface will be like this.

public partial class Order : IOrder
{
int IOrder.Id
{
get { return Id; }
set { Id = value; }
}

int IOrder.CustomerId
{
get { return CustomerId; }
set { CustomerId = value; }
}

int IOrder.ProductId
{
get { return ProductId; }
set { ProductId = value; }
}

ICustomer IOrder.Customer
{
get { return Customer; }
set { Customer = (Customer)value; }
}

IProduct IOrder.Product
{
get { return Product; }
set { Product = (Product)value; }
}
}

Listing 2. IOrder Interface Implementation

In implementing the properties, we have to qualify the property names with the interface name so that it won’t conflict with the existing properties. Note that the Order class is declared partial, and all definitions of the Order class will be merged when compiled. As you can see, each of the properties call their corresponding property defined in the original auto-generated class. There is another case we haven’t covered yet: a property’s type is a generic collection, which happens when database tables are associated (using foreign keys). As we know, we just can’t cast a generic collection of some type to a generic collection of another type. The following shows the structure of the generated Product class.

public partial class Product
{

public int Id { get; set; }
public string Name { get; set; }
public EntitySet<Order> Orders { get; set; }
}

Listing 3. Generated Product Class

Our problem now is how to convert EntitySet<Order> to IList<IOrder>. We can cast EntitySet<Order> to IList<Order>, but not to IList<IOrder>. Fortunately, Fredrick Kalseth already thought of this in his article and came up with a CastList extension method and helper class that will expose the contents of the collection as of the specified type. The same thing we did with the Order class, the Product class will have to implement the IProduct interface.

public partial class Product : IProduct
{
int IProduct.Id
{
get { return Id; }
set { Id = value; }
}

string IProduct.Name
{
get { return Name; }
set { Name = value; }
}

IList<IOrder> IProduct.Orders
{
get { return Orders.CastList<IOrder, Order>(); }
set { Orders = (EntitySet<Order>)value.Cast<Order>(); }
}
}

Listing 4. IProduct Interface Implementation

In the Orders property, the getter uses the CastList extension method. Basically, it will expose the contents as IOrder objects instead of Order objects. In the setter, we just need to cast it back to EntitySet<Order>. Now that we know what to generate, we can now use T4 to generate the code.

Using T4 (Text Template Transformation Toolkit)

To get started using T4, go to the Add New Item dialog and add a Text Template item, as shown in the figure below. I installed tangible T4 Editor mainly for syntax highlighting and IntelliSense for T4 templates. If you also installed it, you can add a text template via the tangible T4 Editor’s template gallery.

Figure 2. Add New Text Template

Let’s name our T4 template after the name of the database. In our example, the database is named MyDB. In the solution explorer you will see two files: MyDB.tt and MyDB.txt. The MyDB.tt file is the text template and the MyDB.txt file is the auto-generated file based on the code in the text template. Since we are using C#, we have to change the extension from .txt to .cs.



Figure 3. Text Template in Solution Explorer

The default contents of the text template are as follows:

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".txt" #>

Anything in between the <# and #> symbols are parsed by the text template transformation engine. They are not copied to the output file. The contents that begin with the @ sign are considered directives. Using the template directive, you can specify parameters that affect the template, like what programming language will be used. With the output directive, you can specify parameters that affect the output file, like the file extension or encoding to use. With this, we can change the extension to .cs from .txt. In our example, I’m going to set the extension to .repository.cs. Visit this link for more information about T4 directives. Ok, let’s try to extract the interfaces from the classes generated by the O/R Designer.

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ output extension=".repository.cs" #>
<#@ assembly name="System.Core.dll" #>
<#@ assembly name="System.Xml.dll" #>
<#@ assembly name="System.Xml.Linq.dll" #>
<#@ assembly name="EnvDTE.dll" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="EnvDTE" #>
<#

var dbmlFile = Host.TemplateFile.Replace(".tt",".dbml");
XElement root = XElement.Load(dbmlFile);

// Get the project's default namespace
IServiceProvider serviceProvider = (IServiceProvider)Host;
DTE dte = (DTE)serviceProvider.GetService(typeof(DTE));
ProjectItem templateItem = dte.Solution.FindProjectItem(Host.TemplateFile);

#>
// Interfaces
namespace <#= templateItem.ContainingProject.Properties.Item("DefaultNamespace").Value #>
{
<#
// Generate Interfaces
foreach (XElement elementClass in root.Descendants().Where(x => x.Name.LocalName == "Type"))
{
string className = elementClass.Attribute("Name").Value.ToString();
#>
public interface I<#= elementClass.Attribute("Name").Value #>
{
<#
// Generate properties that are columns
foreach (XElement elementProperty in elementClass.Descendants().Where(x => x.Name.LocalName == ("Column")))
{
string propertyType = elementProperty.Attribute("Type").Value.ToString();
string propertyName = elementProperty.Attribute("Name").Value.ToString();
#>
<#= propertyType #> <#= propertyName #>
{
get; set;
}
<#
}

// Generate properties that are associations
foreach (XElement elementProperty in elementClass.Descendants().Where(x => x.Name.LocalName == ("Association")))
{
string propertyType = elementProperty.Attribute("Type").Value.ToString();
string propertyName = elementProperty.Attribute("Member").Value.ToString();

if (elementProperty.Attributes().Where(a => a.Name.LocalName == "IsForeignKey").Count() == 0)
{
#>
System.Collections.Generic.IList<I<#= propertyType #>> <#= propertyName #>
{
get; set;
}
<#
}
else
{
#>
I<#= propertyType #> <#= propertyName #>
{
get; set;
}
<#
}
}
#>
}
<#
}
#>
}
Listing 5. Code for Generating Interfaces

The assembly directive adds a reference to the specified assembly for use within the text template, meaning the reference does not get added to the Visual Studio project where the text template resides. The import directive lets you specify the namespaces of the types you need to use for your text template code. Standard control blocks follow, the ones that are delimited by the <# and #> symbols. This is the place where you put your code logic: declare and define variables, use flow control and loop constructs, use assignment statements, etc.

Next, we have to get the DBML file. It contains XML that tells us what we need to know about the database tables and its columns and associations. To get the DBML file, we will assume that the DBML file and the text template’s filenames (without the extension) are the same and located in the same directory. Next, we get the text template filename through the Host property. We have to set the template directive’s hostspecific parameter to true for this to work. Notice that the code will only work when run in Visual Studio.

Once we get the ProjectItem object that corresponds to the text template, we can get the default namespace of the containing Visual Studio project and set it as the namespace for our generated interfaces. This is achieved by the following line.

namespace <#= templateItem.ContainingProject.Properties.Item("DefaultNamespace").Value #>

The word “namespace” is not enclosed within <# and #> symbols, thus it will get copied to the output file. Meanwhile, the expression that is enclosed in <#= and #> symbols gets evaluated, and the output of that evaluation will be copied to the output file. We assumed here that the namespace of the auto-generated LINQ to SQL classes is the same with the project’s default namespace. If not, we have to edit the expression. Not much explanation is needed for the succeeding lines. LINQ to XML is used to get the values we need like the type’s name and its properties. Here is the list of database tables in the DBML file.

<Table Name="dbo.Customer" Member="Customers">
<Type Name="Customer">
<Column Name="Id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
<Association Name="Customer_Order" Member="Orders" ThisKey="Id" OtherKey="CustomerId" Type="Order" />
</Type>
</Table>
<Table Name="dbo.Product" Member="Products">
<Type Name="Product">
<Column Name="Id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
<Association Name="Product_Order" Member="Orders" ThisKey="Id" OtherKey="ProductId" Type="Order" />
</Type>
</Table>
<Table Name="dbo.[Order]" Member="Orders">
<Type Name="Order">
<Column Name="Id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="CustomerId" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
<Column Name="ProductId" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
<Association Name="Customer_Order" Member="Customer" ThisKey="CustomerId" OtherKey="Id" Type="Customer" IsForeignKey="true" />
<Association Name="Product_Order" Member="Product" ThisKey="ProductId" OtherKey="Id" Type="Product" IsForeignKey="true" />
</Type>
</Table>

Listing 6. DBML File Tables
The corresponding interfaces are shown below. These are generated when we saved the text template file.

// Interfaces
namespace LinqToSqlRepositoryDBAccess
{
public interface ICustomer
{
System.Int32 Id
{
get; set;
}
System.String Name
{
get; set;
}
System.Collections.Generic.IList<IOrder> Orders
{
get; set;
}

}
public interface IProduct
{
System.Int32 Id
{
get; set;
}
System.String Name
{
get; set;
}
System.Collections.Generic.IList<IOrder> Orders
{
get; set;
}

}
public interface IOrder
{
System.Int32 Id
{
get; set;
}
System.Int32 CustomerId
{
get; set;
}
System.Int32 ProductId
{
get; set;
}
ICustomer Customer
{
get; set;
}
IProduct Product
{
get; set;
}

}
}

Listing 7. Generated Interfaces
The next thing to do is generate the partial classes that will implement the generated interfaces. As mentioned earlier, we need to use the CastList extension method for the properties corresponding to some table associations. I downloaded the project at the author’s blog and included it in the solution. Here is the additional T4 code for generating the partial classes.

// Classes
namespace <#= templateItem.ContainingProject.Properties.Item("DefaultNamespace").Value #>
{
<#
// Generate Classes
foreach (XElement elementClass in root.Descendants().Where(x => x.Name.LocalName == "Type"))
{
string className = elementClass.Attribute("Name").Value.ToString();
#>
public partial class <#= elementClass.Attribute("Name").Value #> : I<#= elementClass.Attribute("Name").Value #>
{
<#
// Generate properties that are columns
foreach (XElement elementProperty in elementClass.Descendants().Where(x => x.Name.LocalName == ("Column")))
{
string propertyType = elementProperty.Attribute("Type").Value.ToString();
string propertyName = elementProperty.Attribute("Name").Value.ToString();
#>
<#= propertyType #> I<#= className #>.<#= propertyName #>
{
get { return <#= propertyName #>; }
set { <#= propertyName #> = (<#= propertyType #>)value; }
}
<#
}

// Generate properties that are associations
foreach (XElement elementProperty in elementClass.Descendants().Where(x => x.Name.LocalName == ("Association")))
{
string propertyType = elementProperty.Attribute("Type").Value.ToString();
string propertyName = elementProperty.Attribute("Member").Value.ToString();

if (elementProperty.Attributes().Where(a => a.Name.LocalName == "IsForeignKey").Count() == 0)
{
#>
System.Collections.Generic.IList<I<#= propertyType #>> I<#= className #>.<#= propertyName #>
{
get { return <#= propertyName #>.CastList<I<#= propertyType #>, <#= propertyType #>>(); }
set { <#= propertyName #> = (EntitySet<<#= propertyType #>>)value.Cast<<#= propertyType #>>(); }
}
<#
}
else
{
#>
I<#= propertyType #> I<#= className #>.<#= propertyName #>
{
get { return <#= propertyName #>; }
set { <#= propertyName #> = (<#= propertyType #>)value; }
}
<#
}
}
#>
}
<#
}
#>
}

Listing 8. Code for Generating Partial Classes

Notice inside the foreach loop for generating associations that there is an if-else statement. Based on the DBML file some associations have the IsForeignKey attribute. For example, the association with the attribute “member” set to “Customer” (which has an IsForeignKey attribute) corresponds to property of type Customer. Meanwhile, the association with attribute “member” set to “Orders” corresponds to a property of type EntitySet<Order>. I assumed here that the IsForeignKey value is always set to true. The following listing shows the output of the preceding code.

// Classes
namespace LinqToSqlRepositoryDBAccess
{
public partial class Customer : ICustomer
{
System.Int32 ICustomer.Id
{
get { return Id; }
set { Id = (System.Int32)value; }
}
System.String ICustomer.Name
{
get { return Name; }
set { Name = (System.String)value; }
}
System.Collections.Generic.IList<IOrder> ICustomer.Orders
{
get { return Orders.CastList<IOrder, Order>(); }
set { Orders = (EntitySet<Order>)value.Cast<Order>(); }
}

}
public partial class Product : IProduct
{
System.Int32 IProduct.Id
{
get { return Id; }
set { Id = (System.Int32)value; }
}
System.String IProduct.Name
{
get { return Name; }
set { Name = (System.String)value; }
}
System.Collections.Generic.IList<IOrder> IProduct.Orders
{
get { return Orders.CastList<IOrder, Order>(); }
set { Orders = (EntitySet<Order>)value.Cast<Order>(); }
}

}
public partial class Order : IOrder
{
System.Int32 IOrder.Id
{
get { return Id; }
set { Id = (System.Int32)value; }
}
System.Int32 IOrder.CustomerId
{
get { return CustomerId; }
set { CustomerId = (System.Int32)value; }
}
System.Int32 IOrder.ProductId
{
get { return ProductId; }
set { ProductId = (System.Int32)value; }
}
ICustomer IOrder.Customer
{
get { return Customer; }
set { Customer = (Customer)value; }
}
IProduct IOrder.Product
{
get { return Product; }
set { Product = (Product)value; }
}

}
}

Listing 9. Generated Partial Classes

We’re actually done already. If you want to build the type mapping for your IOC container, you could also use T4. Take note that there may be cases that I failed to consider so please edit the text template as you see fit. You can download the Visual Studio 2010 solution here.

By Michael Detras   Popularity  (5177 Views)
Biography - Michael Detras
.NET developer. Interested in WPF, Silverlight, and XNA.
My blog