1.创建扩展方法

首先我们需要创建自定义的扩展方法如下:

 public static class DbFunctionsExtensions

    {

        /// <summary>

        /// 调用数据库的加密方法

        /// </summary>

        /// <param name="_"></param>

        /// <param name="context"></param>

        /// <param name="typeid"></param>

        /// <param name="key"></param>

        /// <returns></returns>

        public static string DmAlgorithmsEncrypt(this DbFunctions _, string context, int typeid, string key)

        {

            throw new InvalidOperationException(

                "该方法仅用于实体框架核心，没有内存实现。");

        }

        /// <summary>

        /// 调用数据库的解密方法

        /// </summary>

        /// <param name="_"></param>

        /// <param name="context"></param>

        /// <param name="typeid"></param>

        /// <param name="key"></param>

        /// <returns></returns>

        public static string DmAlgorithmsDecrypt(this DbFunctions _, string context, int typeid, string key)

        {

            throw new InvalidOperationException(

                "该方法仅用于实体框架核心，没有内存实现。");

        }

很简单,我们只需要定义2个静态扩展方法,并且抛出一个InvalidOperationException异常即可.

2.创建调用方法转换器(IMethodCallTranslator)

这里记住IMethodCallTranslator这个接口,我们需要实现它,如下:

 public class DmDbFunctionsTranslateImpl : IMethodCallTranslator

    {

        private readonly ISqlExpressionFactory _expressionFactory;

        private static readonly MethodInfo _dmAlgorithmsEncryptMethod

            = typeof(DbFunctionsExtensions).GetMethod(

                nameof(DbFunctionsExtensions.DmAlgorithmsEncrypt),

                new[] { typeof(DbFunctions), typeof(string), typeof(int), typeof(string) });

        private static readonly MethodInfo _dmAlgorithmsDecryptMethod

            = typeof(DbFunctionsExtensions).GetMethod(

                nameof(DbFunctionsExtensions.DmAlgorithmsDecrypt),

                new[] { typeof(DbFunctions), typeof(string), typeof(int), typeof(string) });

        public DmDbFunctionsTranslateImpl(ISqlExpressionFactory expressionFactory)

        {

            _expressionFactory = expressionFactory;

        }

        public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)

        {

            //判断方法是否一致

            if (method == _dmAlgorithmsEncryptMethod)

            {

                var args = new List<SqlExpression> { arguments[1], arguments[2], arguments[3] };

                return _expressionFactory.Function(instance, "CFALGORITHMSENCRYPT", args, typeof(string));

            }

            if (method == _dmAlgorithmsDecryptMethod)

            {

                var args = new List<SqlExpression> { arguments[1], arguments[2], arguments[3] };

                return _expressionFactory.Function(instance, "CFALGORITHMSDECRYPT", args, typeof(string));

            }

            return null;

        }

    }

3.创建调用转换器提供程序(RelationalMethodCallTranslatorProvider)

 public sealed class DmAlgorithmsMethodCallTranslatorPlugin : RelationalMethodCallTranslatorProvider

    {

        public DmAlgorithmsMethodCallTranslatorPlugin(RelationalMethodCallTranslatorProviderDependencies dependencies)

            : base(dependencies)

        {

            ISqlExpressionFactory expressionFactory = dependencies.SqlExpressionFactory;

            AddTranslators(

                new IMethodCallTranslator[]

                {

    //这里,将刚刚的方法转换器添加到扩展

                    new DmDbFunctionsTranslateImpl(expressionFactory)

                });

        }

    }

这个类主要是将我们刚刚创建的方法转换器添加SQL表达式工厂(SqlExpressionFactory)当中.

4.创建DbContext扩展类(IDbContextOptionsExtension)

代码如下,关键点加了注释,自行参考..

 public class DmDbContextOptionsExtension : IDbContextOptionsExtension

    {

        private DbContextOptionsExtensionInfo _info;

        public void Validate(IDbContextOptions options)

        {

        }

        public DbContextOptionsExtensionInfo Info

        {

            get

            {

                return this._info ??= new MyDbContextOptionsExtensionInfo(this);

            }

        }

        void IDbContextOptionsExtension.ApplyServices(IServiceCollection services)

        {

            //这里将转换器注入到服务当中.

            services.AddSingleton<IMethodCallTranslatorProvider, DmAlgorithmsMethodCallTranslatorPlugin>();

        }

        private sealed class MyDbContextOptionsExtensionInfo : DbContextOptionsExtensionInfo

        {

            public MyDbContextOptionsExtensionInfo(IDbContextOptionsExtension instance) : base(instance) { }

            public override bool IsDatabaseProvider => false;

            public override string LogFragment => "";

            public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)

            {

            }

            public override long GetServiceProviderHashCode()

            {

                return 0;

            }

        }

    }

5.创建DbContext生成时的扩展方法

    public static class DmDbContextOptionsBuilderExtensions

    {

        public static DbContextOptionsBuilder UseDmAlgorithmsEncryptionFunctions(

            this DbContextOptionsBuilder optionsBuilder)

        {

            //将自定义的配置类添加到配置选项中

            var extension = GetOrCreateExtension(optionsBuilder);

            ((IDbContextOptionsBuilderInfrastructure)optionsBuilder).AddOrUpdateExtension(extension);

            return optionsBuilder;

        }

        //生成创建扩展类

        private static DmDbContextOptionsExtension GetOrCreateExtension(DbContextOptionsBuilder optionsBuilder)

            => optionsBuilder.Options.FindExtension<DmDbContextOptionsExtension>()

               ?? new DmDbContextOptionsExtension();

    }

6.编写测试代码,查看使用效果

我们先在数据库插入一条加密数据如下:

insert into "tab"."tab"( "XingMing", "ZhengJianHao", "ShouJiHao")

VALUES( '测试数据1', CFALGORITHMSENCRYPT('123456789',514,'ABC'),'77777');

然后我们编写查询代码:

var ddd= Context.Where(a => EF.Functions.DmAlgorithmsDecrypt(a.ZhengJianHao, 514, "ABC") == "123456789").First();

这里,我们将数据解密后在对比

查询效果如下:

我们通过监控SQL语句可以看到如下SQL语句:

这里,已经将我们的自定义扩展函数转换成了SQL函数并在数据库执行了.

写在最后

这里我们就完成了整个SQL函数的扩展. 写这篇主要是为了抛砖引玉..

目前这种扩展方式,在查询的时候可以正常的生成SQL语句,

但是在ADD 和Update的时候并不会生成对应的语句,所以想问问各位大佬,有没有更好的实现方式.

巴特西

EFCore3.1+编写自定义的EF.Functions扩展方法