.net – Any clever way to fix ‘string or binary data would be truncated’ warning with LINQ

In general, there isn't a way to determine which particular statement caused the error. If you're running several, you could watch profiler and look at the last completed statement and see what the statement after that might be, though I have no idea if that approach is feasible for you.

In any event, one of your parameter variables (and the data inside it) is too large for the field it's trying to store data in. Check your parameter sizes against column sizes and the field(s) in question should be evident pretty quickly.

Troubleshooting this error with 350 fields can be extremely difficult, and SQL Server Profiler isn't much help in this case (finding the long string in the generated SQL is like finding a needle in a haystack).

So, here is an automated way to find the actual strings that are exceeding the database size limit. This is a solution that's out there on the internet, in various forms. You probably don't want to leave it in your production code, since the attribute/property searching is pretty inefficient, and it'll add extra overhead on every save. I'd just throw it in your code when you encounter this problem, and remove it when you're done.

How it works: it iterates over all properties on an object you're about to save, finding the properties with a LINQ to SQL ColumnAttribute. Then, if the ColumnAttribute.DbType contains "varchar", you know it's a string and you can parse that part of the attribute to find the maximum length.

Here's how to use it:

foreach (object update in context.GetChangeSet().Updates)
{
    FindLongStrings(update);
}

foreach (object insert in context.GetChangeSet().Inserts)
{
    FindLongStrings(insert);
}

context.SubmitChanges();

And here's the method:

public static void FindLongStrings(object testObject)
{
    foreach (PropertyInfo propInfo in testObject.GetType().GetProperties())
    {
        foreach (ColumnAttribute attribute in propInfo.GetCustomAttributes(typeof(ColumnAttribute), true))
        {
            if (attribute.DbType.ToLower().Contains("varchar"))
            {
                string dbType = attribute.DbType.ToLower();
                int numberStartIndex = dbType.IndexOf("varchar(") + 8;
                int numberEndIndex = dbType.IndexOf(")", numberStartIndex);
                string lengthString = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex));
                int maxLength = 0;
                int.TryParse(lengthString, out maxLength);

                string currentValue = (string)propInfo.GetValue(testObject, null);

                if (!string.IsNullOrEmpty(currentValue) && maxLength != 0 && currentValue.Length > maxLength)
                    Console.WriteLine(testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength);

            }
        }
    }
}

Update 12/03/2019 - This answer is referenced on Linqpad.net website for the same error. In Linqpad (version 5) (that uses LinqToSql) the columns are no longer listed as properties instead they are fields . Use the following to iterate through fields:

foreach (FieldInfo propInfo in testObject.GetType().GetFields())
...
...
         string currentValue = (string)propInfo.GetValue(testObject);
...
...