In my MSDN Magazine article on SOA Data Access I recommend exposing Data Transfer Objects (DTOs) from the Data Access Layer (DAL). These objects should be Plain Old CLR Objects (POCOs) that are Persistent Ignorant (PI), eschewing traces of any particular persistence technology. (How’s that for an alphabet soup of acronyms?!) The point here is that the DAL truly encapsulates the API you’re using to fetch and update data, which makes it possible to swap out one data access stack for another without breaking the client.

Having said all that, what tools are available to automatically generate POCO entities from the database? The answer: None. Well, that’s not entirely accurate. There are plenty of code-generation tools out there, such as Code Smith. However, after searching for a few hours, I could not find any templates to generate POCOs. There were templates galore for various frameworks, such as n-tiers, NHibernate, CSLA, and PLINKO. But none of these simply generate POCOs.

This exercise in futility led me to check out T4, which stands for Text Template Transformation Toolkit. Simply stated, it’s a code-generation tool that’s built into Visual Studio! Scott Hanselman has a nice post on how to use it, and there are templates out there for generating various kinds of text files, especially C# and VB code files. There is also a T4 Editor by Clarion that provides intellisense and the like, as well as a Toolbox by Oleg Sych with some handy templates for common scenarios.

As was the case with Code Smith, I could not find any T4 templates to generate POCOs from a database. However, I did find a template by Damien Guard that serves as a wholesale replacement for LINQ to SQL entities. Using this template as a starting point, I ripped out the L2S-specific attributes for entities in order to generate persistence ignorant POCOs. There are two things that are really cool about this:

1. The T4 template uses as its “data source” the .dbml file created when you add a “LINQ to SQL Classes” item to your Visual Studio project. This means that you can drag specific tables and columns from a data connection in the Server Explorer onto the DBML design surface, and the T4 template will create POCOs matching what you’ve defined in the DBML file.

2. LINQ to SQL has the capability of using POCO’s instead of L2S entities. All you have to do is generate an XML mapping file using the SqlMetal command-line utility and feed it into the constructor when creating a strongly-typed DataContext. This completely eliminates the need for a separate set of DTOs!

A T4 template is just a text file with a .tt file extension that you can add to a Visual Studio project. For an L2S project, first add a new “LINQ to SQL Classes” item (which is a DBML file) and configure it, then add the “L2S-POCO.tt” T4 template and rename it to have the same name as the DBML file. Lastly, set the BuildAction of the DBML file to None, so that your app will use classes generated by the T4 template instead. That’s all there is to it!

After creating the T4 POCO template for LINQ to SQL, I thought I might as well go ahead and do one for the Entity Framework. Thankfully, Danny Simmons had already created a template to generate EF entities, so I used it as a starting point to generate POCOs for use with the Entity Framework. The main difference here is that EF cannot use POCO’s currently, so you will need both the EF-specific entities created by the EDMX file, as well as POCO’s created by the T4 template, which you can use as DTOs. This means that you will not change the BuildAction of the EDMX file.

You can download my T4 POCO templates and sample apps here. Enjoy.