EF Exception: String or binary data would be truncated. The statement has been terminated.?

I have read many posts related to this issue, but couldn't find an answer. I am trying to load a large amount of data from Excel into SQL Server. Thousands of records. And I am getting this exception:

> String or binary data would be truncated. The statement has been > terminated.

Obviously some values exceed the field size in the database. The error comes from SQL Server AFIK.

---

My question - How could I possibly know what record and what field value caused this?

There are no specific details in EF exception, except the one I mentioned.

Any help is appreciated.

Some asked for the code fragment, but it's actually very simple, the problem is not with the code:

// employees is a List<Employee> collection loaded from Excel
using (var context = new Entities())
{
    employees.ForEach(e => context.Employee.AddObject(e));
    context.SaveChanges();
}

Also the suggested approach to use DbEntityValidationException (which is only available in Entity Framework 5.0) is not working, the catch block didn't catch the exception.

try
{
    ImportData();
}
catch (DbEntityValidationException ex)
{
    foreach (var item in ex.EntityValidationErrors)
    {
        //...
    }
}

The only solution that I found so far is to use SQL Server Profiler, and define the following events to monitor:

Now I can see the Email is too long.

catch (DbEntityValidationException ex)
{
    foreach (var item in ex.EntityValidationErrors)
    {
        //... inspect here 
    }
}

You can find the information you need inside foreach loop.

Hope that helps.

You can't at that level. SQL Server is rejecting the entire query.

I would add some pre-checks to the data against your database constraints for string size, date formats, etc.

Alternatively you could TRIM each string field in the raw data to the corresponding field size before trying to insert.

You can check data before saving, using EF metadata, and raise appropriate error.

Not sure about truncation specifically, but here is a tip for when you get an exception that tells you to examine EntityValidationErrors. Usuaully when debugging it won't let you see that property (unless you already had an explicit catch). However, you can open up quick watch and type $exception. Now you should be able to drill in and find that property. You can also just type the following:

(System.Data.Entity.Validation.DbEntityValidationException)$exception

private static string 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) && currentValue.Length > maxLength && lengthString!="max")
                        return testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength;

                }
            }
        }
        return "";
    }


foreach (object insert in dtx.GetChangeSet().Inserts)
            {
                string result = FindLongStrings(insert);
                if (string.IsNullOrEmpty(result) == false)
                {
                    responseBuilder.Append(result);
                }
            }

If responseBuilder is not empty, then it contains the field name, allowed length and error message.

I have got that problem twice, and the problem is that when EF creates the table in the database, it sets varchar(1) to string attributes, so when i try to insert information it is not possible because of the length, I recommend you to check the fields in the table