Dynamic Predicate - Kendo Grid - ToDataSourceResult

[farzinmonsef]

Dynamic Predicate - Kendo Grid - ToDataSourceResult

[farzinmonsef]

ExpressionBuilder

public class testExpBldr {

    private List<Kendo.Mvc.FilterDescriptor> GetAllFilterDescriptors(Kendo.Mvc.UI.DataSourceRequest request)
    {
        var allFilterDescriptors = new List<Kendo.Mvc.FilterDescriptor>();
        RecurseFilterDescriptors(request.Filters, allFilterDescriptors);
        return allFilterDescriptors;
    }
    private List<Kendo.Mvc.SortDescriptor> GetAllSortDescriptors(Kendo.Mvc.UI.DataSourceRequest request)
    {
        var allSortDescriptors = new List<Kendo.Mvc.SortDescriptor>();
        foreach (var SortDescriptor in request.Sorts)
        {
            allSortDescriptors.Add((Kendo.Mvc.SortDescriptor)SortDescriptor);
        }
        return allSortDescriptors;
    }

    private void RecurseFilterDescriptors(IList<Kendo.Mvc.IFilterDescriptor> requestFilters, List<Kendo.Mvc.FilterDescriptor> allFilterDescriptors)
    {
        foreach (var filterDescriptor in requestFilters)
        {
            if (filterDescriptor is Kendo.Mvc.FilterDescriptor)
            {
                allFilterDescriptors.Add((Kendo.Mvc.FilterDescriptor)filterDescriptor);
            }
            else if (filterDescriptor is Kendo.Mvc.CompositeFilterDescriptor)
            {
                RecurseFilterDescriptors(((Kendo.Mvc.CompositeFilterDescriptor)filterDescriptor).FilterDescriptors, allFilterDescriptors);
            }
        }
    }

    //public IQueryable<Transaction> FilterTrans()
    //{
    //    const string exp = @"(Person.Age > 3 AND Person.Weight > 50) OR Person.Age < 3";
    //    var p = Expression.Parameter(typeof(Transaction), "Person");
    //    var e = System.Linq.Dynamic.DynamicExpression.ParseLambda(new[] { p }, null, exp);

    //    var result = e.Compile().DynamicInvoke(bob);
    //    return (IQueryable<Transaction>)transaction;
    //}
    private string ConvertFilterDescriptorToString(List<Kendo.Mvc.FilterDescriptor> lfd)
    {
        string s = "", pre = "", post = "", oprtr = "", strChar = "";
        bool prepost = false;
        foreach (var filter in lfd)
        {
            var descriptor = (Kendo.Mvc.FilterDescriptor)filter;
            Debug.WriteLine("Operator" + Convert.ToString(descriptor.Operator));
            if (descriptor != null && descriptor.Member == "SomeField")
            {
                descriptor.Member = "SomeOtherField";
            }
            pre = ""; post = ""; oprtr = ""; prepost = false; strChar = "";
            switch (Convert.ToString(descriptor.Operator))
            {
                case "IsLessThan":
                    oprtr = " < ";
                    break;
                case "IsLessThanOrEqualTo":
                    oprtr = " <= ";
                    break;
                case "IsEqualTo":
                    oprtr = " = ";
                    strChar = (filter.Value is string ? "@": "");
                    break;
                case "IsNotEqualTo":
                    oprtr = " != ";
                    break;
                case "IsGreaterThanOrEqualTo":
                    oprtr = " >= ";
                    break;
                case "IsGreaterThan":
                    oprtr = " > ";
                    break;
                case "StartsWith":
                    oprtr = " like ";
                    strChar = (filter.Value is string ? "@" : "");
                    pre = "%"; post = "";
                    break;
                case "EndsWith":
                    oprtr = " like ";
                    strChar = (filter.Value is string ? "@" : "");
                    pre = ""; post = "%";
                    break;
                case "Contains":
                    oprtr = " like ";
                    strChar = (filter.Value is string ? "@" : "");
                    pre = "%"; post = "%";
                    break;
                case "IsContainedIn":
                case "DoesNotContain":
                case "IsNull":
                case "IsNotNull":
                case "IsEmpty":
                case "IsNotEmpty":
                case "IsNullOrEmpty":
                case "IsNotNullOrEmpty":
                    break;
            }
            s += "(" + "Trans." + filter.Member + oprtr + pre + strChar + filter.Value + strChar + post + ")";
        }
        if (s == "") { s = "1=1"; }
        s = s.Replace('@', '"');
        return s;
    }

    //public List<T> querytestFilter(EQMEntities db, Kendo.Mvc.UI.DataSourceRequest request)
    //{
    //    try
    //    {
    //        List<Kendo.Mvc.FilterDescriptor> lfd = new List<Kendo.Mvc.FilterDescriptor>();
    //        lfd = GetAllFilterDescriptors(request);
    //        string exp = ConvertFilterDescriptorToString(lfd);

    //        var p = Expression.Parameter(typeof(T), "Trans");
    //        var e = (Expression<Func<T, bool>>)System.Linq.Dynamic.DynamicExpression.ParseLambda(new[] { p }, null, exp);

    //        var transaction = db.Transactions
    //            .Where(e)
    //            .OrderBy(t=>t.Id)
    //            .Skip(request.PageSize * (request.Page-1))
    //            .Take(request.PageSize)
    //            .Select(t=>t)
    //            .AsQueryable()
    //            .ToList()
    //            ;
    //        return (List<T>)transaction;
    //    }catch(Exception e)
    //    {
    //        Debug.WriteLine (Convert.ToString(e));
    //        return (List<T>)null;
    //    }
    //}
    public List<Transaction> querytestFilter(EQMEntities db, Kendo.Mvc.UI.DataSourceRequest request)//List<Kendo.Mvc.FilterDescriptor> lfd)
    {
        try
        {
            //var predicate = PredicateBuilder.True<Transaction>();

            //if (!string.IsNullOrEmpty(str))
            //if (lfd.Count > 0)
            //{

            //}
            //Type sponsorResultType = typeof(Transaction);

            //if (sponsorResultType.GetProperties().Any(prop => prop.Name.ToLower() == lfd[0].Member.ToLower()))
            //{
            //    System.Reflection.PropertyInfo pinfo = sponsorResultType.GetProperty(lfd[0].Member);
            //    orderByExpr = (data => pinfo.GetValue(data, null));
            //}

            //var parameter = Expression.Parameter(typeof(Transaction), "__");
            //var comparison = Expression.GreaterThan(Expression.Property(parameter, 
            //    Type.GetType("ConsoleApp6.Album").GetProperty("ReserveName")), Expression.Constant(100));
            //Func<Transaction, bool> exp = Expression.Lambda<Func<Transaction, bool>>(comparison, parameter).Compile();

            //var transaction = db.Transactions.Where(exp)
            //    .Select(t => t)
            //    .ToList()
            //    .AsQueryable()
            //    ;

            //// t
            //var parameter = Expression.Parameter(typeof(Transaction), "t");
            //// t.Total
            //var propertyExpression = Expression.PropertyOrField(parameter, "ReserveName");
            //var constant = Expression.Constant(100M, typeof(decimal));
            //// t.Total == 100.00M 
            //var equalExpression = Expression.Equal(propertyExpression, constant);
            //// t => t.Total == 100.00M
            //var lambda = Expression.Lambda(equalExpression, parameter);
            //// calls where.
            //var whereExpression = Expression.Call(typeof(Queryable), "Where", new[] { query.ElementType }, query.Expression, lambda);
            //// add where to query.
            //query = query.Provider.CreateQuery(whereExpression) as IQueryable<Mock>;
            //Console.ReadKey();

            //Farzin
            List<Kendo.Mvc.FilterDescriptor> lfd = new List<Kendo.Mvc.FilterDescriptor>();
            lfd = GetAllFilterDescriptors(request);
            string exp = ConvertFilterDescriptorToString(lfd);

            var p = Expression.Parameter(typeof(Transaction), "Trans");
            var e = (Expression<Func<Transaction, bool>>)System.Linq.Dynamic.DynamicExpression.ParseLambda(new[] { p }, null, exp);

            //string exp = @"(Transaction.ReserveName = ""sg"")";
            //var result = e.Compile().DynamicInvoke(bob);

            //predicate = predicate.And(s => s.
            //ReserveName
            ////(typeof(Transaction).GetField("ReserveName", BindingFlags.NonPublic | BindingFlags.Instance))
            //.ToLower().StartsWith("ds"));

            //var srt = "";

            var transaction = db.Transactions
                .Where(e)
                .OrderBy(t => t.Id)
                .Skip(request.PageSize * (request.Page - 1))
                .Take(request.PageSize)
                .Select(t => t)
                //.Select(t => new Transaction
                //{
                //    Id = t.Id,
                //    TransactionType = t.TransactionType,
                //    TodayDate = t.TodayDate,
                //    TransactionEffectiveDate = t.TransactionEffectiveDate,
                //    RequisitionNumber = t.RequisitionNumber,
                //    ReserveName = t.ReserveName,
                //    TotalVestedShares = t.TotalVestedShares,
                //    Memo = t.Memo,
                //    Attachments = t.Attachments,
                //    Status = t.Status,
                //    Actions = t.Actions,
                //})
                .AsQueryable()
                .ToList()
                ;
            return (List<Transaction>)transaction;
        }
        catch (Exception e)
        {
            Debug.WriteLine(Convert.ToString(e));
            return (List<Transaction>)null;
        }
    }

    public void startit(EQMEntities db)
    {
        Console.WriteLine("Specify the property to filter");
        string propertyName = Console.ReadLine();
        Console.WriteLine("Value to search against: " + propertyName);
        string value = Console.ReadLine();

        //1: With Func delegate
        //var dynamicExpression = GetDynamicQueryWithFunc(propertyName, value);
        //var output = userData.Where(dynamicExpression).ToList();

        //2: With Expression trees that generate Func and handles dynamic types with TypeDescriptor
        var dn = GetDynamicQueryWithExpresionTrees(propertyName, value);
        var output = db.Transactions.Where(dn).ToList();

        foreach (var item in output)
        {
            Console.WriteLine("Filtered result:");
            //Console.WriteLine("\t ID: " + item.ID);
            //Console.WriteLine("\t First Name: " + item.FirstName);
            //Console.WriteLine("\t Last Name: " + item.LastName);
        }

        Console.WriteLine("\n");
        Console.WriteLine("==== DONE =====");
    }

    private Func<Transaction, bool> GetDynamicQueryWithFunc(string propName, object val)
    {
        Func<Transaction, bool> exp = (t) => true;
        switch (propName)
        {
            case "ID":
                //exp = d => d.ID == Convert.ToInt32(val);
                break;
            case "FirstName":
                //exp = f => f.FirstName == Convert.ToString(val);
                break;
            case "LastName":
                //exp = l => l.LastName == Convert.ToString(val);
                break;
            default:
                break;
        }
        return exp;
    }

    private static Func<Transaction, bool> GetDynamicQueryWithExpresionTrees(string propertyName, string val)
    {
        //x =>
        var param = Expression.Parameter(typeof(Transaction), "x");

        #region Convert to specific data type
        MemberExpression member = Expression.Property(param, propertyName);
        UnaryExpression valueExpression = GetValueExpression(propertyName, val, param);
        #endregion
        Expression body = Expression.Equal(member, valueExpression);
        var final = Expression.Lambda<Func<Transaction, bool>>(body: body, parameters: param);
        return final.Compile();
    }

    private static UnaryExpression GetValueExpression(string propertyName, string val, ParameterExpression param)
    {
        var member = Expression.Property(param, propertyName);
        var propertyType = ((PropertyInfo)member.Member).PropertyType;
        var converter = TypeDescriptor.GetConverter(propertyType);

        if (!converter.CanConvertFrom(typeof(string)))
            throw new NotSupportedException();

        var propertyValue = converter.ConvertFromInvariantString(val);
        var constant = Expression.Constant(propertyValue);
        return Expression.Convert(constant, propertyType);
    }

}

[farzinmonsef]

PredicateBuilder

public static class ExpBldr_ { public static IOrderedQueryable OrderBy<TSource, TKey>(this IQueryable source, System.Linq.Expressions.Expression<Func<TSource, TKey>> keySelector, System.ComponentModel.ListSortDirection sortOrder ) { if (sortOrder == System.ComponentModel.ListSortDirection.Ascending) return source.OrderBy(keySelector); else return source.OrderByDescending(keySelector); }

    public static IEnumerable<T> IfThenElse<T>(
        this IEnumerable<T> elements,
        Func<bool> condition,
        Func<IEnumerable<T>, IEnumerable<T>> thenPath,
        Func<IEnumerable<T>, IEnumerable<T>> elsePath, string AscDesc)
    {
        return condition()
            ? thenPath(elements)
            : elsePath(elements);
    }
}

/// <summary>    
/// Enables the efficient, dynamic composition of query predicates.    
/// </summary>    
public static class PredicateBuilder_
{
    /// <summary>    
    /// Creates a predicate that evaluates to true.    
    /// </summary>    
    public static Expression<Func<T, bool>> True<T>() { return param => true; }

    /// <summary>    
    /// Creates a predicate that evaluates to false.    
    /// </summary>    
    public static Expression<Func<T, bool>> False<T>() { return param => false; }

    /// <summary>    
    /// Creates a predicate expression from the specified lambda expression.    
    /// </summary>    
    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

    /// <summary>    
    /// Combines the first predicate with the second using the logical "and".    
    /// </summary>    
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }

    /// <summary>    
    /// Combines the first predicate with the second using the logical "or".    
    /// </summary>    
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }

    /// <summary>    
    /// Negates the predicate.    
    /// </summary>    
    public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
    {
        var negated = Expression.Not(expression.Body);
        return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
    }

    /// <summary>    
    /// Combines the first expression with the second using the specified merge function.    
    /// </summary>    
    static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // zip parameters (map from parameters of second to parameters of first)    
        var map = first.Parameters
            .Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with the parameters in the first    
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

        // create a merged lambda expression with parameters from the first expression    
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    class ParameterRebinder : ExpressionVisitor
    {
        readonly Dictionary<ParameterExpression, ParameterExpression> map;

        ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;

            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }

            return base.VisitParameter(p);
        }
    }
}