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

.net entity-framework entity-framework-6 sql trim

Question

All text values in the third-party database I'm using are kept aschar(n) . While some of these text values are primary keys, others are simply plain text that can be read by humans. I want the latter to automatically cut the values it retrieves.

I'm certain I can addTrim however this is clumsy, unstable, and unmaintainable. to all of my LINQ to Entities queries. I want to set up Entity Framework in some way to automatically trim values that are fetched from particular columns.

But I have no idea how to do it. I'm utilizing the fluent API from EF. The closest idea I can come up with is to create other properties to surround the real properties in.Trim method calls, although this is disorganized and is still difficult to manage. The trimming should, in my opinion, take place in the database rather than the application.

1
26
11/21/2013 11:06:11 PM

Popular Answer

A nice solution to this problem that makes use of interceptors was recently posted by Rowan Miller, program manager for Entity Framework at Microsoft. Obviously, this is only applicable to EF 6.1+. In essence, the method as done cleanly removes trailing strings from all of the string attributes in your models, automatically, without appreciably reducing performance. His post is about trailing strings in joins.

Post from the beginning: addressing the string joins problem with trailing blanks

I urge you to read his blog post, but the pertinent code is reprinted here. (You should read his blog even if you don't use EF, by the way.)

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 goes on: "We must instruct EF to use the interceptor now that we have one. The best way to do this is through code-based configuration. The following class can simply be dropped into the same assembly or project as our context, and EF will take it up."

using System.Data.Entity;

namespace FixedLengthDemo
{
    public class MyConfiguration : DbConfiguration
    {
        public MyConfiguration()
        {
            AddInterceptor(new StringTrimmerInterceptor());
        }
    }
}
37
2/6/2015 11:22:36 PM


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