String or binary data would be truncated. linq exception, cant find which field has exceeded max length.
i have around 350 fields. i checked each and every textbox maxlength with database field maxlength, everything seems to be correct, but i still get the exception.
please help
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);
...
...
And, hey, why not have the same solution (@shaunmartin's) in VB.Net, too? Sometimes you just gotta debug someone else's code!
Use:
For Each update as Object In context.GetChangeSet().Updates
FindLongStrings(update)
Next
For Each insert as Object In context.GetChangeSet().Inserts
FindLongStrings(insert)
Next
And core
Public Shared Sub FindLongStrings(ByVal testObject As Object)
Dim propInfo As PropertyInfo
For Each propInfo In testObject.GetType().GetProperties()
Dim attribute As ColumnAttribute
For Each attribute In propInfo.GetCustomAttributes(GetType(ColumnAttribute), True)
If attribute.DbType.ToLower().Contains("varchar") Then
Dim dbType As String = attribute.DbType.ToLower()
Dim numberStartIndex As Integer = dbType.IndexOf("varchar(") + 8
Dim numberEndIndex As Integer = dbType.IndexOf(")", numberStartIndex)
Dim lengthString As String = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex))
Dim maxLength As Integer = 0
Integer.TryParse(lengthString, maxLength)
Dim currentValue As String = CType(propInfo.GetValue(testObject, Nothing), String)
If Not String.IsNullOrEmpty(currentValue) AndAlso maxLength <> 0 AndAlso currentValue.Length > maxLength Then
Console.WriteLine(testObject.GetType().Name & "." & propInfo.Name & " " & currentValue & " Max: " & maxLength)
End If
End If
Next
Next
End Sub
But with a lot of data or a lot of fields this takes a lot of time for sure. The debugging side of linq is lacking - the exception thrown should tell you the field!
