LINQ to SQL: Getting String Field Maximum Lengths With a T4 Template

by Andrew Barber 26. October 2011 09:22

If you use LINQ to SQL (link) for data access in your applications, you may have noticed one thing 'missing': an easy way to automatically get the maximum field lengths for NVarChar and similar columns. This article describes a T4 template (link) I have created for doing just that. This template generates C# code, but it would be fairly simple to convert it to produce VB code, if you prefer.

First, if you would like to download the file, here it is: L2SStringFieldLengths.zip (1.33 kb) To get detailed info on its use and creation, read on!

Using the T4 Template

Using the .tt file is as simple as unzipping it into your project, setting a few variables in the file, and letting it rip. You should be sure the template is applied each time you generate or re-generate your L2S classes. The template reads from the .dbml files XML to find the information it needs, and creates classes with const values for you to use. One class is created per L2S class, using the same name as that class, with one const int field containing the length of each of the length-limited fields in the database.

The following variables should be set in the template before letting it run:

string RootNameSpace = "Namespace";
bool IncludeDatabaseNames = false;
string[] Folders = new string[]{
	"Models"
};
bool IncludeToStringMembers = true;
string ToStringSuffix = "_ts";

RootNameSpace will be the namespace of the classes that are generated. It can be set to a compound namespace, such as Models.Lengths, for example.

IncludeDatabaseNames determines whether the generated classes will be nested within a class named after the database. This is most useful for when you have multiple databases, which may have naming collisions.

Folders is a string array of the names of directories in which to search for .dbml files, relative to the location of the .tt template file. Folders will not be recursively searched, so Models will not cause Models\Linq to be searched. Leaving this array empty will cause all sub-folders to be searched, recursively.

IncludeToStringMembers will cause there to be an extra string field which will contain the length value, for use in places where a string is required, to avoid the need to call ToString(), first. ToStringSuffix will be the suffix added to those string fields.

Sample Output

namespace Namespace {
public partial class TableName {
	public const int Field1 = 50;
	public const string Field1_ts = "50";
	public const int Field2 = 256;
	public const string Field2_ts = "256";
}

How It Works

The template starts with some basic plumbing. Keep in mind that the template code itself will have no assemblies included or namespaces imported by default, so:

<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ output extension=".cs" #>
 
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Xml.Linq" #>
<#@ assembly name="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Text.RegularExpressions" #>

After the aforementioned variables and some other very basic things, the code starts by building a list of directories to be searched. Each one is searched for .dbml files, and those files are loaded into a LINQ to XML XElement object. If you open a .dbml file directly in a text editor, you'll find it is an XML file with a fairly simple schema, and the template code parses it to find the tables and fields that apply. Note that because there is a namespace declared in the XML, we have to use a slightly more complex version of XElement.Elements() than simply passing a string name; we have to actually create an XName object with XName.Get(), like so:

foreach (XElement table in element.Elements(XName.Get("Table", DbmlNamespace))) {

DbmlNamespace is the string "http://schemas.microsoft.com/linqtosql/dbml/2007", which is the XML namespace used in the .dbml files.

From there, the template is really quite simple; it tests the type of each field, then verifies that it is one with an actual length set (as opposed to, say, NVarChar(MAX)), and outputs the appropriate field(s) for each one.

Download the Code

To get the template to use in your own projects, click here: L2SStringFieldLengths.zip (1.33 kb)

Comments are closed
Disclaimer
The opinions expressed herein are my own personal opinions and do not represent those of my partners, clients or contractors in any way.

© Copyright 2014 AndrewBarber.com