Click here to Skip to main content
13,345,254 members (46,343 online)
Click here to Skip to main content
Add your own
alternative version


23 bookmarked
Posted 30 Jun 2008

Using the LINQ ColumnAttribute to Get Field Lengths from your Database

, 30 Jun 2008
Rate this:
Please Sign up or sign in to vote.
Reflecting on LINQ properties gets you information about the length of your database fields


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.


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:

/// <span class="code-SummaryComment"><summary></span>
/// Gets the length limit for a given field on a LINQ object ... or zero if not known
/// <span class="code-SummaryComment"></summary></span>
/// <span class="code-SummaryComment"><remarks></span>
/// 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.
/// <span class="code-SummaryComment"></remarks></span>
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;

/// <span class="code-SummaryComment"><summary></span>
/// If you don't care about truncating data that you are setting on a LINQ object, 
/// use something like this ...
/// <span class="code-SummaryComment"></summary></span>
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);
        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;


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

United States United States
I have been writing code and managing teams of developers for more years than I care to remember.

You may also be interested in...


Comments and Discussions

QuestionPropertyInfo prop = type.GetProperty(field); always returns null Pin
thabomoj11-Sep-12 4:36
memberthabomoj11-Sep-12 4:36 
QuestionHow to show Db Type for all columns in a table + VS Type + Col Name all together [modified] Pin
Guttorm Haaversen7-Aug-11 12:28
memberGuttorm Haaversen7-Aug-11 12:28 
QuestionDon't we also want to check for char and nchar? Pin
Mark Kamoski23-Jul-09 5:36
memberMark Kamoski23-Jul-09 5:36 
QuestionWhy use TryParse? Pin
Mark Kamoski23-Jul-09 5:33
memberMark Kamoski23-Jul-09 5:33 
GeneralNeed to using System.Data.Linq.Mapping; Pin
Mark Kamoski23-Jul-09 5:15
memberMark Kamoski23-Jul-09 5:15 
GeneralVB code to do the same thing! Pin
oughtsix30-Sep-08 15:24
memberoughtsix30-Sep-08 15:24 
GeneralValidation handling Pin
existenz_1-Jul-08 3:46
memberexistenz_1-Jul-08 3:46 
GeneralRe: Validation handling Pin
HightechRider1-Jul-08 5:34
memberHightechRider1-Jul-08 5:34 
Generalif (value.Length &gt; len) check len for 0 Pin
Steve Hansen1-Jul-08 2:15
memberSteve Hansen1-Jul-08 2:15 
GeneralRe: if (value.Length &gt; len) check len for 0 [modified] Pin
HightechRider1-Jul-08 5:36
memberHightechRider1-Jul-08 5:36 
Yes, it ought to throw an exception if you use the auto truncate method on a property where we don't have any metadata. I should stress this isn't production code, more of an illustration as to how to get at the column attribute and use it to avoid having to update max-length in multiple places in your solution. I've added the exception to the article in case someone uses it as-is. Thanks!

modified on Tuesday, July 1, 2008 11:10 AM

GeneralInteresting, but... Pin
Pete O'Hanlon30-Jun-08 23:00
mvpPete O'Hanlon30-Jun-08 23:00 
GeneralRe: Interesting, but... Pin
Seishin#1-Jul-08 1:09
memberSeishin#1-Jul-08 1:09 
GeneralRe: Interesting, but... Pin
HightechRider1-Jul-08 5:41
memberHightechRider1-Jul-08 5:41 
GeneralRe: Interesting, but... Pin
Pete O'Hanlon1-Jul-08 10:31
mvpPete O'Hanlon1-Jul-08 10:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 1 Jul 2008
Article Copyright 2008 by HightechRider
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid