// Dynamic LINQ - stef

​

// @nuget: System.Linq.Dynamic.Core

// @nuget: Z.EntityFramework.Classic

​

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Entity;

using System.Linq.Dynamic.Core;

using System.Linq.Dynamic.Core.CustomTypeProviders;

using System.ComponentModel.DataAnnotations;

using System.Text;

using System.Globalization;

public class Program

{   

    public static void Main()

    {

        GenerateData();

​

        var context = new EntityContext();

        // System.Linq.Dynamic.GlobalConfig.CustomTypeProvider = new MyCustomTypeProvider();  NO LO ENCUENTRA COMO GLOBAL

        var config = new ParsingConfig

        {

            CustomTypeProvider = new MyCustomTypeProvider()

        };

​

        var query91 = new [] { new { Campo1 = "ÁÉÍÓÚ" }, new { Campo1 = "PÓÚ" } , new { Campo1 = "PATO" } }.AsQueryable();

        // Mejor sería la función "translate" https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-translate/

        var result91 = query91.Select(c=> c.Campo1.Replace("Á", "A").Replace("Ó", "O"));  

        FiddleHelper.WriteTable(result91);

​

        var result92 = context.X.Select(c=> c.Campo1.Replace("Á", "A").Replace("Ó", "O"));

        FiddleHelper.WriteTable(result92);

        var result93 = context.X.Select("Campo1.Replace(\"Á\", \"A\").Replace(\"Ó\", \"O\")");

        FiddleHelper.WriteTable(result93);

​

        // Sólo funciona con OBJETOS no con ENTIDADES

        var query2 = new [] { new { Value = (string) null }, new { Value = "100" } }.AsQueryable();

        var result2 = query2.Select("Utils.ParseAsInt(Value)");

        FiddleHelper.WriteTable(result2);

        // -----------------------------------------

        // No funciona con ENTIDADES sólo con Objetos

        // var result = context.X.Select(config, "Utils.ParseAsInt(Value)");

        // NO var result = context.X.Where(c=> Utils.ParseAsInt(c.Value) > 0 ).Select(c=> c.Value);

        // var result = context.X.Where(c=> EF.Functions.Like(c.Value, "%00%") ).Select(c=> c.Value); EF no lo encuentra

        // -----------------------------------------

        var result51 = context.X.Where(c=> DbFunctions.Like(c.Value, "%00%") ).Select(c=> c.Value);

        FiddleHelper.WriteTable(result51);

        var result52 = context.X.Where(config, " DbFunctions.Like(Value, \"_2_\") ").Select("Value");

        FiddleHelper.WriteTable(result52);

        var result53 = context.X.Where(config, " DbFunctions.Like(Value, \"%00%\") ").Select(c=> c.Value);

        FiddleHelper.WriteTable(result53);

        // OK var result = context.X.Where(c=> DbFunctions.Right(c.Value, 2) == "00").Select(c=> c.Value);

        // OK var result = context.X.Where(c=> DbFunctions.Right(c.Value, 2) == "00").Select(c=> DbFunctions.Left(c.Value, 2));

        // ------------

        // OK pero si se quita result.Add(typeof(System.Data.Entity.DbFunctions));  de MyCustomTypeProvider entonces

        // da el error "Enum type 'DbFunctions' not found"

        // var result = context.X.Where(c=> DbFunctions.Right(c.Value, 2) == "00").Select(config, "DbFunctions.Left(Value, 2)");

        // ------------

        //var result = context.X.Select(config, "Utils.ParseAsString(Value)");

        //FiddleHelper.WriteTable(result);

    }

    public static void GenerateData()

    {

        var list = new List<X>

        {

            new X { Value = null, Campo1="ÁÉÍÓÚ" },

            new X { Value = "100", Campo1="PÉPe" },

            new X { Value = "222", Campo1="LÁÚ" },

            new X { Value = "300", Campo1="LÉÓ" },

            new X { Value = "320", Campo1="SÁÓÚ" }

        };

        using (var context = new EntityContext())

        {

            context.X.AddRange(list);

            context.BulkSaveChanges();

        }

    }

    public class EntityContext : DbContext

    {

        public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer())

        {

        }

        public DbSet<X> X { get; set; }

​

        //[DbFunction("FUNCPRUEBA")]

        //public static string FuncPrueba(string s) => throw new Exception();

    }

    public class X

    {

        public int Id { get; set; }

        public string Value { get; set; }

        public string Campo1 { get; set; }

    }

    [DynamicLinqType]

    public static class Utils

    {

        public static string ParseAsString(string value)

        {

            return "hola";

        }

        public static int ParseAsInt(string value)

        {

            if (value == null)

            {

                 return 0;

            }

​

            return int.Parse(value);

        }

​

        public static string RemoveDiacritics(string text)

        {

            var normalizedString = text.Normalize(NormalizationForm.FormD);

            var stringBuilder = new StringBuilder(capacity: normalizedString.Length);

​

            for (int i = 0; i < normalizedString.Length; i++)

            {

                char c = normalizedString[i];

                var unicodeCategory = CharUnicodeInfo.GetUnicodeCategory(c);

                if (unicodeCategory != UnicodeCategory.NonSpacingMark)

                {

                    stringBuilder.Append(c);

                }

            }

​

            return stringBuilder

                .ToString()

                .Normalize(NormalizationForm.FormC);

        }

​

    }

    class MyCustomTypeProvider : DefaultDynamicLinqCustomTypeProvider 

    {

        public override HashSet<Type> GetCustomTypes()

        {

            //get the original set of custom types

            var result = base.GetCustomTypes(); 

            // result.Add(typeof(Utils)); NO HACE FALTA PONERLO, FUNCIONA TAMBIÉN

​

            //Add DbFunctions as a custom type

            // Si se quita dará error "Enum type 'DbFunctions' not found" en las sentencias sql.

            result.Add(typeof(System.Data.Entity.DbFunctions)); 

​

            return result;

        }

    }

}