How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?

Question

I'm working with a third-party database in which all text values are stored as char(n). Some of these text values are primary keys, whereas others are just normal human-readable text. For the latter, I want retrieved values to be automatically trimmed.

I know I can add Trim to all of my LINQ to Entities queries, but this is messy, unreliable and unmaintainable. I would like to somehow configure Entity Framework to automatically trim values retrieved from specific columns.

However, I don't know how to do this. I'm using EF's fluent API. The closest thing I've thought of so far is creating additional properties to wrap the real properties with Trim method calls, but this is messy and still not very maintainable. I would also prefer for the trimming to occur in the database rather than the application.

Answer 1

Rowan Miller (program manager for Entity Framework at Microsoft) recently posted a good solution to this which uses Interceptors. Admittedly this is only valid in EF 6.1+. His post is about trailing strings in joins, but basically, the solution as applied neatly removes trailing strings from all of the string properties in your models, automatically, without noticeably affecting performance.

Original blog post: Working around trailing blanks issue in string joins

The relevant code is reposted here, but I encourage you to read his blog post. (Also if you use EF, you should read his blog anyway).

using System.Data.Entity.Core.Common.CommandTrees;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure.Interception;
using System.Linq;

namespace FixedLengthDemo
{
    public class StringTrimmerInterceptor : IDbCommandTreeInterceptor
    {
        public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
            {
                var queryCommand = interceptionContext.Result as DbQueryCommandTree;
                if (queryCommand != null)
                {
                    var newQuery = queryCommand.Query.Accept(new StringTrimmerQueryVisitor());
                    interceptionContext.Result = new DbQueryCommandTree(
                        queryCommand.MetadataWorkspace,
                        queryCommand.DataSpace,
                        newQuery);
                }
            }
        }

        private class StringTrimmerQueryVisitor : DefaultExpressionVisitor
        {
            private static readonly string[] _typesToTrim = { "nvarchar", "varchar", "char", "nchar" };

            public override DbExpression Visit(DbNewInstanceExpression expression)
            {
                var arguments = expression.Arguments.Select(a =>
                {
                    var propertyArg = a as DbPropertyExpression;
                    if (propertyArg != null && _typesToTrim.Contains(propertyArg.Property.TypeUsage.EdmType.Name))
                    {
                        return EdmFunctions.Trim(a);
                    }

                    return a;
                });

                return DbExpressionBuilder.New(expression.ResultType, arguments);
            }
        }
    }
}

Rowan continues: "Now that we have an interceptor, we need to tell EF to use it. This is best done via Code-Based Configuration. We can just drop the following class in the same assembly/project as our context and EF will pick it up."

using System.Data.Entity;

namespace FixedLengthDemo
{
    public class MyConfiguration : DbConfiguration
    {
        public MyConfiguration()
        {
            AddInterceptor(new StringTrimmerInterceptor());
        }
    }
}

Answer 2

If you are using Entity Framework Core, you can use Conversion like this:

entity.Property(e => e.Name)
             .HasConversion(
                new ValueConverter<string, string>(v => v.TrimEnd(), v => v.TrimEnd()));

Answer 3

Use properties with backing fields instead of automatic properties on your entities.

Add the "Trim()" in the property setter, like so:

    protected string _name;
    public String Name
    {
        get { return this._name; }
        set { this._name = (value == null ? value : value.Trim()); }
    }

I wrote my own POCO generator that just does this automatically, but if you don't have an option like that, ReSharper can add backing fields to automatic properties in like two keystrokes. Just do it for strings, and you can do a global (at the file scope) find/replace for " = value;" with "= value.Trim();".

Answer 4

Entity Framework does not supply hooks to change the way it composes SQL statements, so you can't tell it to fetch and Trim string fields from the database.

It would be possible to trim string properties in the ObjectContext.ObjectMaterialized event, but I think this would greatly affect performance. Also, it would take a lot of if-else or switch code to do this for specific properties (as you intend to do). But it could be worth a try if you want to do this for nearly all properties (except the keys, for instance).

Otherwise I would go for the additional properties.

Answer 5

I had the same problem. And I resolved it by this simple way in DbContext:

public partial class MyDbContext : DbContext
{
    public override int SaveChanges()
    {
        foreach (var entity in this.ChangeTracker.Entries())
        {
            foreach (PropertyEntry property in entity.Properties.ToList().Where(o => !o.Metadata.IsKey()))
                TrimFieldValue(property);
        }

        return base.SaveChanges();
    }

    private void TrimFieldValue(PropertyEntry property)
    {
        var metaData = property.Metadata;
        var currentValue = property.CurrentValue == null ? null : property.CurrentValue.ToString();
        var maxLength = metaData.GetMaxLength();

        if (!maxLength.HasValue || currentValue == null) return;

        if (currentValue.Length > maxLength.Value)
            property.CurrentValue = currentValue.Substring(0, maxLength.Value);
    }       
}

Answer 6

I relied on Pavel Lobkov's solution and got the result with this:

public class MyDbContext : DbContext
{
    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        TrimFieldValue();

        return base.SaveChangesAsync(cancellationToken);
    }

    public override int SaveChanges()
    {
        TrimFieldValue();

        return base.SaveChanges();
    }

    private void TrimFieldValue()
    {
        foreach (var entity in this.ChangeTracker.Entries())
        {
            foreach (PropertyEntry property in entity.Properties.ToList().Where(o => o.Metadata.ClrType.Name.Equals("String") && o.CurrentValue is not null))
            {
                var currentValue = property.CurrentValue.ToString();
                property.CurrentValue = currentValue.Trim();
            }
        }
    }
}

Answer 7

I used the approach given by Stuart Grassie but it didn't work at first because the column type only contained the "char","varchar" etc. The columns are actually "char(30)", "varchar(10)", etc. Once I changed the line that follows it worked like a charm!

from: if (propertyArg != null && _typesToTrim.Contains(propertyArg.Property.TypeUsage.EdmType.Name))

to: if (propertyArg != null && _typesToTrim.Any(t => propertyArg.Property.TypeUsage.EdmType.Name.Contains(t)))

Thanks Stuart!