EF Core - String or binary data would be truncated

c# entity-framework-core

Question

How do you determine which column is the culprit when you have 80(+/-) columns to choose from? Using .Net Core (netcoreapp2.2) and EF Core 2.2.4.

Picked up some existing code and there was an attempt to track columns that failed. However, it does not work. I've looked at dozens of examples here and elsewhere and have not found a way to do this in EF Core 2.x.

public int GetColumnMaxLength(string table, EntityEntry entityEntry)
{
    // Just a rough to get the right data - always returns 0 for the moment...
    int result = 0;
    var modelContext = entityEntry.Context;
    var entityType = modelContext.Model.FindEntityType(table); // THIS IS ALWAYS NULL!

    if (entityType != null)
    {
        // Table info 
        var tableName = entityType.Relational().TableName;
        var tableSchema = entityType.Relational().Schema;

        // Column info 
        foreach (var property in entityType.GetProperties())
        {
            var columnName = property.Relational().ColumnName;
            var columnType = property.Relational().ColumnType;
            var isFixedLength = property.Relational().IsFixedLength;
        };
    }
    return result;
}

The above code is being called by this catch portion of a try/catch around the db.SaveAsync(); statement.

catch (Exception ex)
{
    // -----------------------------------------
    // no idea what this was really trying to 
    // do as it barfs out all columns...
    // -----------------------------------------

    var dataInfo = new DataInfo();

    var strLargeValues = new List<Tuple<int, string, string, string>>();

    foreach (var entityEntry in _db.ChangeTracker.Entries().Where(et => et.State != EntityState.Unchanged))
    {
        // -----------------------------------------
        // try to get the column info for all 
        // columns on this table...
        // -----------------------------------------
        dataInfo.GetColumnMaxLength("Subscription", entityEntry);

        foreach (var entry in entityEntry.CurrentValues.Properties)
        {
            var value = entry.PropertyInfo.GetValue(entityEntry.Entity);
            if (value is string s)
            {
                strLargeValues.Add(Tuple.Create(s.Length, s, entry.Name, entityEntry.Entity.GetType().Name));
            }
        }
    }

    var l = strLargeValues.OrderByDescending(v => v.Item1).ToArray();

    foreach (var x in l.Take(100))
    {
        Trace.WriteLine(x.Item4 + " - " + x.Item3 + " - " + x.Item1 + ": " + x.Item2);
    }

    throw;
}

So, the crux of the question is: How do I get the SQL column definition from EF Core?

I want to be able to log the specific table and column when incomingData.Length > targetColumnDefinition.Length


FINAL SOLUTION:

public override int SaveChanges()
{
    using (LogContext.PushProperty("DbContext:Override:Save", nameof(SaveChanges)))
    {
        try
        {
            return base.SaveChanges();
        }
        catch (Exception ex)
        {
            var errorMessage = String.Empty;
            var token = Environment.NewLine;

            foreach (var entityEntry in this.ChangeTracker.Entries().Where(et => et.State != EntityState.Unchanged))
            {
                foreach (var entry in entityEntry.CurrentValues.Properties)
                {
                    var result = entityEntry.GetDatabaseDefinition(entry.Name);
                    var value = entry.PropertyInfo.GetValue(entityEntry.Entity);
                    if (result.IsFixedLength && value.ToLength() > result.MaxLength)
                    {
                        errorMessage = $"{errorMessage}{token}ERROR!! <<< {result.TableName}.{result.ColumnName} {result.ColumnType.ToUpper()} :: {entry.Name}({value.ToLength()}) = {value} >>>";
                        Log.Warning("Cannot save data to SQL column {TableName}.{ColumnName}!  Max length is {LengthTarget} and you are trying to save something that is {LengthSource}.  Column definition is {ColumnType}"
                            , result.TableName
                            , result.ColumnName
                            , result.MaxLength
                            , value.ToLength()
                            , result.ColumnType);
                    }
                }
            }
            throw new Exception(errorMessage, ex);
        }
    }
}
1
2
6/7/2019 5:24:57 PM

Accepted Answer

As mentioned in the comments, you need the full name and this can be read from the metadata.

public int GetColumnMaxLength(EntityEntry entityEntry)
{
    int result = 0;

    var table = entityEntry.Metadata.Model.FindEntityType(entityEntry.Metadata.ClrType);

    // Column info 
    foreach (var property in table.GetProperties())
    {
        var maxLength = property.GetMaxLength();

        // For sql info, e.g. ColumnType = nvarchar(255):
        var sqlInfo = property.SqlServer();
    };
    return result;
}
1
5/14/2019 8:37:20 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow