Problem

While this error isn’t strictly a C# (the error is actually generated by SQL Server), it’s quite likely that it’ll crop up if you interface with your database using LINQ to SQL. It essentially means that you are trying to insert or update a row where a string/varchar field has a length longer than the database defined maximum – e.g if a column ‘Description’ was an nvarchar(50) and you tried putting a string of length 51 in the field, the exception would occur.

While any database inputs should always be validated on the client, this wouldn’t  guarantee this error will never occur as it could be a string you’re generating server side, or the client side validation could fail.

The problem with this error is that it doesn’t tell you which database column has caused it. If you have a large table, then it’ll be tedious at best to try to find out.

Solution

After investigating the classes that LINQ to SQL generates for the entities, I found that each field (column in the database) had an attribute like the below

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Name", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]

So for each string field, we should be able to look at this attribute, specifically look at the DbType property and extract the ’50’ from there. From there, we can check the property value on a given object to ensure it’s not exceeding the maximum length, and if it is throw a nice, descriptive error. See below extension method:

public static void ValidateStringLengthsOnEntity(this object item)
{
    var exceptions = new List<StringLengthExceedsDbRestrictionException>();
    var properties = item.GetType().GetProperties();
    foreach (var property in properties.Where(a => a.PropertyType == typeof (string)))
    {
        var attrs = (ColumnAttribute[]) property.GetCustomAttributes(typeof (ColumnAttribute), false);
        if (attrs.Length > 0)
        {
            var attr = attrs[0];
            int startBracketIndexattr = attr.DbType.IndexOf('(');
            int endBracketIndexattr = attr.DbType.IndexOf(')');
            string lengthStr = attr.DbType.Substring(startBracketIndexattr + 1,
                endBracketIndexattr - startBracketIndexattr - 1);
            int length = lengthStr.Equals("max", StringComparison.CurrentCultureIgnoreCase)
                ? int.MaxValue
                : int.Parse(lengthStr);

            var propertyValue = (string) property.GetValue(item, null);
            int propertyLength = propertyValue?.Length ?? 0;

            if (propertyLength > length)
            {
                exceptions.Add(new StringLengthExceedsDbRestrictionException(
                    $"Property {property.Name} with value '{propertyValue}' has a length of {propertyLength} but the max is {length}"));
            }
        }
    }

    if (exceptions.Any())
    {
        if (exceptions.Count == 1)
            throw exceptions[0];
        else
            throw new StringLengthExceedsDbRestrictionException(
                $"Multiple fields have string length issues:{string.Join(Environment.NewLine, exceptions.Select(a => "\t" + a.Message).ToArray())}");
    }
}

Note that for the ‘ColumnAttribute’ type to be found, you will need to add this using to the top of your class

using System.Data.Linq.Mapping;

You’ll notice that it also uses a custom class called ‘StringLengthExceedsDbRestrictionException’ – this class is basic, and could probably be extended to have a few more properties

public class StringLengthExceedsDbRestrictionException : Exception
{
    public StringLengthExceedsDbRestrictionException(string message) : base(message) { }
    public StringLengthExceedsDbRestrictionException(string message, Exception innerException) : base(message, innerException) { }
}

This method can this be invoked on any object (ensuring that the relevant ‘using’ for the extension method is added), and I advise calling it before a ‘SubmitChanges’ is called on the data context. In the case of the contrived example at the start with the ‘Description’ field being an nvarchar(50), see below example:

using (var dc = new DataContext())
{
    var order = dc.Orders.First();
    order.Description = "A long description of over 50 characters here - should trigger exception";
    order.ValidateStringLengthsOnEntity();
    dc.SubmitChanges();
}

The exception thrown would be

Property Description with value ‘A long description of over 50 characters here – should trigger exception’ has a length of 72 but the max is 50

Which I hope you would agree is a lot more descriptive than ‘String or binary data may be truncated’!

Performance

I was initially concerned about any performance implications due to the use of reflection, however after profiling this on a large table (20+ varchar columns with limits), it would take between 8 and 20 milliseconds to run. However you may want to consider profiling this method call if you use it and start getting performance issues

Improvements

You could likely replace the code that extracts the number (by looking for the starting and closing brackets) with regex, however I find regex terrifying so I didn’t opt for that route!

If performance was an issue, I’d consider implementing a cache that will store the maximum length of every string column on the table, so that after the first run it doesn’t need to look at a fields attributes again. However as performance was generally very good, it seems overkill

You can also tweak this method to trim any string to the required length,  however I’m not particularly a fan of this approach as the user would not be aware of the data loss.