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.

Solution 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());
        }
    }
}

Solution 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()));

Solution 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();".

Solution 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.

Solution 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);
    }       
}

View more solutions

Comments

[Łukasz Bańcarz]

It's bettet to check null first so I'd do it that way: set { _name = (value == null ? null : value.Trim());}

[Stuart Grassie]

This is no longer true in EF 6.1. See my answer.

[Shibbz]

I had to use the StringLength attribute on my model but this approach did work for me.

[nramirez]

This doesn't look like a valid answer because you're basically using the same solution Stuart added + a slightly different case, I suggest you add it as a comment in Stuart's question.

[Jpsy]

Starting with C# 6.0 the setter can be nicely simplified using the null-conditional operator: set { this._name = value?.Trim(); }

[esmoore68]

Please use this with caution. We deployed this exact solution and it brought our website to its knees with CPU usage. Profiler showed it to be the cause, and removing it removed the problem. I'm sure the effect is related to application specific workload, but be advised that this is a very CPU intensive solution.

[Admin]

Please note that you do not Trim but Truncate the string.

[Alexander]

The cleanest solution :)