Using the LINQ ColumnAttribute to Get Field Lengths from your Database

Introduction

With LINQ, it's now really easy to design your database in an interactive tool like SQL Server Management Studio, drag your tables into a DBML in Visual Studio and then get to work on all the classes and relationships that have been created for you.

This works great and ensures that there is 'one fact once place' concerning how data is persisted - you don't need to maintain a data layer AND a database and struggle to keep them in sync. But when it comes to metadata about the columns in your database, up to now, you've had to maintain that information in two (or more) places. The length of a text field in your UI for example should be limited to the length of the column that will store it in the database.

Today, you probably have the length defined in your database and you have the length defined in your UI. You might also have the length defined in some code that truncates data when storing it. Change it in the database and you have to go change it everywhere else.

This brief article shows you how to get column metadata from the properties on LINQ objects allowing you to have a single master (the database) that defines the allowed length of every property. Now your UI, your business layer, your data layer and your database can all be in synch all the time.

Notes

Auto-truncating data is rarely the right thing to do; normally you would only use the first of the two methods presented here to get the length limit and then pass it up through your business layer to your UI so the UI can validate the user's input. Auto-truncate might be used during some batch input process where there is, say, a field that is OK to truncate either with or without a warning to the user, like, say, a comments field.

Note also that this article isn't prescribing any particular system design, it's meant as an illustration as to how to get to the column metadata; it's up to you to decide how to use it. In an advanced, distributed system where the UI isn't talking directly to LINQ objects, this code might find use in the hands of your testers who can automate the generation of max-length and max-length+1 inputs to ensure that max-length data can pass through all layers of the system and that max-length+1 data is properly rejected in your validation code and in your business layer.

Using the Code

Add these two static methods to your Utilities assembly:

/// <summary>
/// Gets the length limit for a given field on a LINQ object ... or zero if not known
/// </summary>
/// <remarks>
/// You can use the results from this method to dynamically 
/// set the allowed length of an INPUT on your web page to
/// exactly the same length as the length of the database column.  
/// Change the database and the UI changes just by
/// updating your DBML and recompiling.
/// </remarks>
public static int GetLengthLimit(object obj, string field)
{
    int dblenint = 0;   // default value = we can't determine the length

    Type type = obj.GetType();
    PropertyInfo prop = type.GetProperty(field);
    // Find the Linq 'Column' attribute
    // e.g. [Column(Storage="_FileName", DbType="NChar(256) NOT NULL", CanBeNull=false)]
    object[] info = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
    // Assume there is just one
    if (info.Length == 1)
    {
        ColumnAttribute ca = (ColumnAttribute)info[0];
        string dbtype = ca.DbType;

        if (dbtype.StartsWith("NChar") || dbtype.StartsWith("NVarChar"))
        {
            int index1 = dbtype.IndexOf("(");
            int index2 = dbtype.IndexOf(")");
            string dblen = dbtype.Substring(index1 + 1, index2 - index1 - 1);
            int.TryParse(dblen, out dblenint);
        }
    }
    return dblenint;
}

/// <summary>
/// If you don't care about truncating data that you are setting on a LINQ object, 
/// use something like this ...
/// </summary>
public static void SetAutoTruncate(object obj, string field, string value)
{
    int len = GetLengthLimit(obj, field);
    if (len == 0) throw new ApplicationException("Field '" + field + 
            "'does not have length metadata");

    Type type = obj.GetType();
    PropertyInfo prop = type.GetProperty(field);
    if (value.Length > len)
    {
        prop.SetValue(obj, value.Substring(0, len), null);
    }
    else
        prop.SetValue(obj, value, null);
} 

Using them is easy. Suppose you have an instance 'customer' of LINQ type called 'Customer' and you want to get the length of the 'Name' field:

int len = GetLengthLimit (customer, "Name"); 

You would probably implement this at the lowest level in your solution and then provide methods to pass the length metadata up through your business logic to your UI. LINQ's partial classes might be the right place to implement this. You might, for example, add an int NameLength property to complement your Name property.

Or suppose you don't care about truncation (a very rare and usually ill-advised approach) and you want to store a value in the database in a guaranteed to succeed fashion (with no annoying exceptions about data being truncated):

SetAutoTruncate (song, "Comments", "Really long comments about the song 
  that someone else put in to the song metadata but which you really don't care about");

Points of Interest

It's trivial to add a cache allowing you to go from <Type + Field Name> to <length> without having to reflect on the Type every single time, but as always, optimization like that is nearly always best left until you need it.

Don't forget to include the appropriate using statements:

using System.Reflection;
using System.Data.Linq;
using System.Data.Linq.Mapping;

History

• 30^th June, 2008: First version
• 1^st July, 2008: Second version incorporating initial feedback