DbUpdateException: Which field is causing "String or binary data would be truncated"

Question

I am getting a DbUpdateException with message

String or binary data would be truncated

I understand that one of the fields in the entity won't fit the length of the column in the database. And that I could go down and check them manually.

What I am trying to do however, is get a sensible error message which might tell me which field it actually is! E.g.

String or binary would be truncated at field ProspectName.

I am able to print out a lot of random information. and have tried various stuff. But nothing points to the field name.

Please note this is NOT of type DbEntityValidationException, it is a DbUpdateException

// DbUpdateException exception
foreach (var entry in exception.Entries)
{ 
    builder.AppendLine(String.Format("Error at: Type {0}", entry.Entity.GetType().Name));

    if ((exception.InnerException is System.Data.Entity.Core.UpdateException) &&
        (exception.InnerException.InnerException is System.Data.SqlClient.SqlException))
    {
        var updateException = (System.Data.Entity.Core.UpdateException)exception.InnerException;

        var sqlException = (System.Data.SqlClient.SqlException)exception.InnerException.InnerException;
        var result = new List<ValidationResult>();

        for (int i = 0; i < sqlException.Errors.Count; i++)
        {
            builder.AppendLine(String.Format("Error code: {0} ", sqlException.Errors[i].Number));
            builder.AppendLine(String.Format("Source: {0} ", sqlException.Errors[i].Source));
            builder.AppendLine(String.Format("Message: {0} ", sqlException.Errors[i].Message));
            builder.AppendLine(String.Format("State: {0} ", sqlException.Errors[i].State));
            builder.AppendLine(String.Format("Procedure: {0} ", sqlException.Errors[i].Procedure));
        } 
    }
}

Complete error:

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

Error at: Type tree_1ECACDBB4458C7A9DEC7CD183FD8B8C3473502FEFFACF160E17AD47718DCE5EA
Error code: 8152
Source: .Net SqlClient Data Provider
Message: String or binary data would be truncated.
State: 14
Procedure:
Error code: 3621
Source: .Net SqlClient Data Provider
Message: The statement has been terminated.
State: 0
Procedure:

Answer 1

One "ugly" solution (but functional and using ONLY C# code) to find exactly which property is giving you that error would be:

In case you are doing an update do this:

 var myDBObj = db.Mytables.Where(x=>x.Id == myId).FirstOrDefaul();
 if(myDBObj == null) return false; // or something else with the error msg

 myDBObj.Property1 = myObjToSave.Property1;
 db.SaveChanges();

 myDBObj.Property2 = myObjToSave.Property2;
 db.SaveChanges();

 myDBObj.Property3 = myObjToSave.Property3;
 db.SaveChanges();
 .... // EACH PROPERTY....
 myDBObj.PropertyX = myObjToSave.PropertyX;
 db.SaveChanges();

Now, with a brake point or an incremental variable to track the "position", etc... you will know exactly in this specific case where you have the exception....

NOTE: this is an ugly solution JUST to track down where it is failling... NEVER use this in production... and of course IMO there are other more friendly things like having a sql profiler and see the generated SQL and then try to run it on the sql management studio and then see the error there......

UPDATE #1

Something like this (note: I did not compile it) :P

Type type = obj.GetType();
PropertyInfo[] properties = type.GetProperties();  
string lastPropertyWithError = ""; // You can replace this with a list or so  
foreach (PropertyInfo property in properties)
{
   try{
    property.SetValue(myDBObj, property.GetValue(myObj, null));
    db.SaveChanges();
   }catch()
   {
     lastPropertyWithError  = property.Name;
   }
}

Answer 2

Using Entity framework profiler to capture the sql queries from the entity framework, find your update query and run it against the database directly, you have good chances of finding the field.

Answer 3

ProspectName is the field that could be truncated. I don't know how that is mapped to your database, though. You could look that up in FluentApi or DataAnnotation depending on how you set it up.

You'll need to see what the MaxLength is set for this field and make sure it matches with your database max length. If it does, then you'll need to make sure you don't pass a too long of a string on this field. It could also be that the data type doesn't match between the entity and database, so check that too.

Right now, I just don't have a lot of other information to help you further.

Answer 4

Following on from @Shaik:

public MyDbContext(string connectionString, ILogger<PeopleHrDbContext> logger)
    : base(connectionString)
{
    _logger = logger;
    Database.Log = logger.Debug;
}

I am using NLog and Ioc, so you can pass a logger to your ef context and in your configured log output, you can see what the generated queries looks like. For me that is the debug output window. I have pasted in below my NLog set-up to support this.

  <targets>
    <target xsi:type="Debugger" name="d" 
            layout="${longdate} ${logger} ${uppercase:${level}} ${message}" />
  </targets>

  <rules>
    <logger name="*" minlevel="Trace" writeTo="d" />
  </rules>