Ef Core增加Sql方法

    [AttributeUsage(AttributeTargets.Class|AttributeTargets.Method)]
    public class DbFunAttribute : Attribute
    {
        public DbFunAttribute( string functionName, Type returnType)
        {
            ReturnType = returnType;
            FunctionName = functionName;
        }

        public DbFunAttribute()
        {

        }

        public Type ReturnType { get; set; }
        public string FunctionName { get; set; }
    }

    public static class SqlFunctionExtension
    {
        public static ModelBuilder UseSqlFunction(this ModelBuilder modelBuilder)
        {
            foreach (Assembly assembly in AppDomain.CurrentDomain.GetAssemblies())
            {
                foreach (var type in assembly.GetTypes()
                .Where(item=>item.GetCustomAttribute<DbFunAttribute>() != null))
                {
                    List<MethodInfo> funList = type.GetMethods(BindingFlags.Public | BindingFlags.Static)
                        .Where(method => method.GetCustomAttribute<DbFunAttribute>() != null)
                        .ToList();

                    foreach (var method in funList)
                    {
                        modelBuilder.HasDbFunction(method).HasTranslation(arg =>
                        {
                            var argumentos = arg.ToList();
                            var attribute = method.GetCustomAttribute<DbFunAttribute>();
                            var funcionName = attribute.FunctionName;
                            return new SqlFunctionExpression(
                                funcionName,
                                attribute.ReturnType,
                                argumentos);
                        });
                    }
                }
            }

            return modelBuilder;
        }
    }

调用的Sql方法

    [DbFun]
    public class Function
    {
        public static int DateDiff(string part, DateTime inicio, DateTime fim)
            => 0;

        [DbFun("REPLACE", typeof(string))]
        public static string Replace(object dados, string substituir, string por)
            => string.Empty;
    }

 

启用

Ef的DbContext对象下面的

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.HasDbFunction(typeof(Function).GetMethod("DateDiff")).HasTranslation((arg =>
            {
                var argumentos = arg.ToList();
                argumentos[0] = new SqlFragmentExpression((string)((ConstantExpression)argumentos.First()).Value);
                return new SqlFunctionExpression(
                    "DATEDIFF",
                    typeof(int),
                    argumentos);
            }));

            modelBuilder.UseSqlFunction();
}

调用

            var serviceProvider = services.BuildServiceProvider();
            var context = serviceProvider.GetService(typeof(CoreWebContext)) as CoreWebContext;

            var query = context.EntityOrder.Where(item => Function.DateDiff("MONTH", DateTime.Parse("2018-06-01"), item.PayTime.Value) == 0).Select(item =>
                Function.Replace(item.OrderNo, "2018", "xyzx")
            );
            var list = query.ToList();