Sql – LINQ2SQL: How to implement a generic maximum string length validation

linq-to-sqlvalidation

A common problem in LINQ2SQL is while the .NET String allows assigning any length to its variable, your database may have a specific max length constraint (like VARCHAR(5)). This will lead to the SQL error message "String or binary data would be truncated.", which is extremely unhelpful since it doesn't tell you which fields is the culprit.

Obviously, validating the input for the maximum string length will be the correct way. The main problem I faced is creating the necessary validation for every LINQ object in my project, and updating the validation if the maximum length of the field is updated.

Ideally, I need to work out a way to dynamically determine the max length of a generated field, so I do not risk forgetting to update the validation later.

The best possible implementation I can find so far is "Integrating xVal Validation with Linq-to-Sql", which is already far superior to anything I can think. The only uncertain point is the dynamically determine the max length.

Has anyone seen or implemented anything similar?

Best Answer

The LINQ2SQL code generator places an attribute on property fields similar to:

[Column(Storage="_Message", DbType="NVarChar(20)")]

It would be simple to extract and use this information at runtime:

public class Row
{
    // normally generated by LINQ2SQL
    [Column(Storage = "_Message", DbType = "NVarChar(20)")]
    public string Message
    {
        get;
        set;
    }

    // normally generated by LINQ2SQL
    [Column(Storage = "_Property", DbType = "NVarChar(20) NOT NULL")]
    public string Property
    {
        get;
        set;
    }
}

public class VarCharInfo
{
    public int? MaxLen;
    public bool IsNullable;
}

public static VarCharInfo GetVarCharInfo(PropertyInfo propertyInfo)
{
    var attrib = propertyInfo.GetCustomAttributes(typeof(ColumnAttribute), false)
        .OfType<ColumnAttribute>()
        .FirstOrDefault();

    if (attrib == null || attrib.DbType == null)
    {
        return null;
    }

    var match = Regex.Match(attrib.DbType, @"VarChar\((?'len'\d+)\)(?'notnull' NOT NULL)?");

    if (!match.Success)
    {
        return null;
    }

    var rvl = new VarCharInfo();

    rvl.MaxLen = int.Parse(match.Groups["len"].Captures[0].Value);
    rvl.IsNullable = match.Groups["notnull"].Success;

    return rvl;
}

public static bool ValidateVarCharColumns(object dataObject)
{
    bool success = true;

    foreach (var propertyInfo in dataObject.GetType()
        .GetProperties()
        .Where(pi => pi.PropertyType == typeof(string)))
    {
        var vci = GetVarCharInfo(propertyInfo);

        if (vci != null)
        {
            var currentValue = propertyInfo.GetGetMethod()
                .Invoke(dataObject, null) as string;

            if (currentValue == null)
            {
                if (!vci.IsNullable)
                {
                    // more work: value is null but it shouldn't be
                    success = false;
                }
            }
            else if (vci.MaxLen != null && currentValue.Length > vci.MaxLen)
            {
                // more work: value is longer than db allows
                success = false;
            }
        }
    }

    return success;
}

static void UsageTest()
{
    Row t = new Row();
    t.Message = "this message is longer than 20 characters";
    // t.Property is null

    ValidateVarCharColumns(t);  // returns false!
}
Related Topic