如何配置实体框架以自动修剪为映射到 char(N) 字段的特定列检索的值? [英] How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?

问题描述

我正在使用第三方数据库，其中所有文本值都存储为 char(n).其中一些文本值是主键，而另一些只是普通的人类可读文本.对于后者，我希望自动修剪检索到的值.

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.

我知道我可以将 Trim 添加到我的所有 LINQ to Entities 查询中，但这很混乱、不可靠且无法维护.我想以某种方式配置实体框架以自动修剪从特定列检索到的值.

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.

但是，我不知道该怎么做.我正在使用 EF 的 fluent API.到目前为止，我想到的最接近的事情是创建附加属性以使用 Trim 方法调用来包装真实属性，但这很麻烦，而且仍然不是很容易维护.我也希望在数据库而不是应用程序中进行修剪.

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.

推荐答案

Rowan Miller(Microsoft 实体框架项目经理)最近发布了一个很好的解决方案，它使用了拦截器.诚然，这仅在 EF 6.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.

原始博客文章:解决尾随空白问题字符串连接

相关代码在这里转贴，但我鼓励您阅读他的博客文章.(另外，如果你使用 EF，你还是应该阅读他的博客).

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 继续说道:现在我们有了一个拦截器，我们需要告诉 EF 使用它.这最好通过基于代码的配置来完成.我们可以将以下类放在与我们的上下文相同的程序集/项目中，然后EF 会把它捡起来."

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

这篇关于如何配置实体框架以自动修剪为映射到 char(N) 字段的特定列检索的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！