none
關於LinqExtensions自訂搜尋條件的問題 RRS feed

  • 問題

  • 如果問題發錯版的話, 麻煩版主幫我搬版

    1.我找到一個範例
    Using jqGrid’s search toolbar with multiple filters in ASP.NET MVC
    http://www.codeproject.com/Articles/58357/Using-jqGrid-s-search-toolbar-with-multiple-filter

    可是它的 LinqExtensions.cs 與 WhereOperation.cs
    並沒有完整實作
    它只有做了, 等於, 不等於, 包含

    2.所以我想擴充它們
    可是有幾個功能, 我不知道該怎麼寫?
    有人可以幫忙看看嗎?

    WhereOperation.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    
    namespace MvcGrid.Models.Helpers
    {
        /// <summary>
        /// The supported operations in where-extension
        /// </summary>
        public enum WhereOperation
        {
            //等於
            [StringValue("eq")]
            Equal,
    
            //不等於
            [StringValue("ne")]
            NotEqual,
    
            //小於
            [StringValue("lt")]
            LessThan,
    
            //小於等於
            [StringValue("le")]
            LessThanOrEqual,
    
            //大於
            [StringValue("gt")]
            GreaterThan,
    
            //大於等於
            [StringValue("ge")]
            GreaterThanOrEqual,
    
            //開始於
            [StringValue("bw")]
            BeginsWith,
    
            //不開始於
            [StringValue("bn")]
            NotBeginsWith,
    
            //在其中
            [StringValue("in")]
            In,
            
            //不在其中
            [StringValue("ni")]
            NotIn,
    
            //結束於
            [StringValue("ew")]
            EndWith,
    
            //不結束於
            [StringValue("en")]
            NotEndWith,
    
            //包含
            [StringValue("cn")]
            Contains,
    
            //不包含
            [StringValue("nc")]
            NotContains,
    
            //Null
            [StringValue("nu")]
            Null,
    
            //不Null
            [StringValue("nn")]
            NotNull
        }
    }

    LinqExtensions.cs 只有改
    public static IQueryable<T> Where<T>(this IQueryable<T> query,
                string column, object value, WhereOperation operation)

     public static IQueryable<T> Where<T>(this IQueryable<T> query,
                string column, object value, WhereOperation operation)
            {
                if (string.IsNullOrEmpty(column))
                    return query;
    
                ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
    
                MemberExpression memberAccess = null;
                foreach (var property in column.Split('.'))
                    memberAccess = MemberExpression.Property
                       (memberAccess ?? (parameter as Expression), property);
    
                //change param value type
                //necessary to getting bool from string
                ConstantExpression filter = Expression.Constant
                    (
                        Convert.ChangeType(value, memberAccess.Type)
                    );
    
                //switch operation
                Expression condition = null;
                LambdaExpression lambda = null;
                switch (operation)
                {
                    //等於 equal ==
                    case WhereOperation.Equal:
                        condition = Expression.Equal(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
                    //不等於 not equal !=
                    case WhereOperation.NotEqual:
                        condition = Expression.NotEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
                    //小於
                    case WhereOperation.LessThan:
                        condition = Expression.LessThan(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //小於等於
                    case WhereOperation.LessThanOrEqual:
                        condition = Expression.LessThanOrEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //大於
                    case WhereOperation.GreaterThan:
                        condition = Expression.GreaterThan(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //大於等於
                    case WhereOperation.GreaterThanOrEqual:
                        condition = Expression.GreaterThanOrEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //開始於
                    //不開始於
                    //在其中
                    //不在其中
                    //結束於
                    //不結束於
    
                    //包含 string.Contains()
                    case WhereOperation.Contains:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("Contains"),
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不包含
              
                    //Null
                    //不Null
                }
    
                
                MethodCallExpression result = Expression.Call(
                       typeof(Queryable), "Where",
                       new[] { query.ElementType },
                       query.Expression,
                       lambda);
    
                return query.Provider.CreateQuery<T>(result);
            }


    player

    2013年5月24日 下午 12:52

解答

  • 參考看看...
                    //開始於
                    case WhereOperation.BeginsWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("StartsWith"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不開始於
                    case WhereOperation.NotBeginsWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("StartsWith"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //在其中 string.Contains()
                    case WhereOperation.In:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不在其中 string.Contains()
                    case WhereOperation.NotIn:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //結束於
                    case WhereOperation.EndWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Endswith"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不結束於
                    case WhereOperation.NotEndWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Endswith"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //包含 string.Contains()
                    case WhereOperation.Contains:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不包含 string.Contains()
                    case WhereOperation.NotContains:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //Null
                    case WhereOperation.Null:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("IsNullOrEmpty"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不Null
                    case WhereOperation.NotNull:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("IsNullOrEmpty"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;

    • 已標示為解答 p1ayer 2013年5月30日 上午 09:57
    2013年5月30日 上午 08:59

所有回覆

  • 參考看看...
                    //開始於
                    case WhereOperation.BeginsWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("StartsWith"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不開始於
                    case WhereOperation.NotBeginsWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("StartsWith"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //在其中 string.Contains()
                    case WhereOperation.In:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不在其中 string.Contains()
                    case WhereOperation.NotIn:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //結束於
                    case WhereOperation.EndWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Endswith"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不結束於
                    case WhereOperation.NotEndWith:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Endswith"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //包含 string.Contains()
                    case WhereOperation.Contains:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不包含 string.Contains()
                    case WhereOperation.NotContains:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("Contains"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //Null
                    case WhereOperation.Null:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("IsNullOrEmpty"), Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不Null
                    case WhereOperation.NotNull:
                        condition = Expression.Call(memberAccess, typeof(string).GetMethod("IsNullOrEmpty"), Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;

    • 已標示為解答 p1ayer 2013年5月30日 上午 09:57
    2013年5月30日 上午 08:59
  • 使用過後, 發現還是有點問題
    修正如下, 不確定有修正完,先這樣了

    另整理一份在

    http://www.player.idv.tw/prog/index.php/MvcGrid.Models

    1.檔案: Filter.cs

    使用Encoding.Default.GetBytes 會導致傳入中文字串時, 無法解析中文字, 彈出例外, 因此要改用 Encoding.Unicode.GetBytes



    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Runtime.Serialization;
    using System.Runtime.Serialization.Json;
    
    namespace MvcGrid.Models.Grid
    {
        [DataContract]
        public class Filter
        {
            [DataMember]
            public string groupOp { get; set; }
            [DataMember]
            public Rule[] rules { get; set; }
    
            public static Filter Create(string jsonData)
            {
                try
                {
                    var serializer = new DataContractJsonSerializer(typeof(Filter));
                    System.IO.StringReader reader = new System.IO.StringReader(jsonData);
                    System.IO.MemoryStream ms =
                        new System.IO.MemoryStream(Encoding.Unicode.GetBytes(jsonData));
                    
                    return serializer.ReadObject(ms) as Filter;
                }
                catch
                {
                    return null;
                }
            }
        }
    }

    2.檔案: LinqExtensions.cs
    修正一些  Expression.Call , 指定繫結的參數格式為 new[] { typeof(string) }


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    
    namespace MvcGrid.Models.Helpers
    {
    
        public static class LinqExtensions
        {
            /// <summary>Orders the sequence by specific column and direction.</summary>
            /// <param name="query">The query.</param>
            /// <param name="sortColumn">The sort column.</param>
            /// <param name="ascending">if set to true [ascending].</param>
            public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
            {
                string methodName = string.Format("OrderBy{0}",
                    direction.ToLower() == "asc" ? "" : "Descending");
    
                ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
    
                MemberExpression memberAccess = null;
                foreach (var property in sortColumn.Split('.'))
                    memberAccess = MemberExpression.Property
                       (memberAccess ?? (parameter as Expression), property);
    
                LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);
    
                MethodCallExpression result = Expression.Call(
                          typeof(Queryable),
                          methodName,
                          new[] { query.ElementType, memberAccess.Type },
                          query.Expression,
                          Expression.Quote(orderByLambda));
    
                return query.Provider.CreateQuery<T>(result);
            }
    
    
            public static IQueryable<T> Where<T>(this IQueryable<T> query,
                string column, object value, WhereOperation operation)
            {
                if (string.IsNullOrEmpty(column))
                    return query;
    
                ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
    
                MemberExpression memberAccess = null;
                foreach (var property in column.Split('.'))
                    memberAccess = MemberExpression.Property
                       (memberAccess ?? (parameter as Expression), property);
    
                //change param value type
                //necessary to getting bool from string
                ConstantExpression filter = Expression.Constant
                    (
                        Convert.ChangeType(value, memberAccess.Type)
                    );
    
                //switch operation
                Expression condition = null;
                LambdaExpression lambda = null;
                switch (operation)
                {
                    //等於 equal ==
                    case WhereOperation.Equal:
                        condition = Expression.Equal(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不等於 not equal !=
                    case WhereOperation.NotEqual:
                        condition = Expression.NotEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //小於
                    case WhereOperation.LessThan:
                        condition = Expression.LessThan(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //小於等於
                    case WhereOperation.LessThanOrEqual:
                        condition = Expression.LessThanOrEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //大於
                    case WhereOperation.GreaterThan:
                        condition = Expression.GreaterThan(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //大於等於
                    case WhereOperation.GreaterThanOrEqual:
                        condition = Expression.GreaterThanOrEqual(memberAccess, filter);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //開始於
                    case WhereOperation.BeginsWith:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不開始於
                    case WhereOperation.NotBeginsWith:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("StartsWith", new[] { typeof(string) }), 
                            Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //在其中 string.Contains()
                    case WhereOperation.In:
                        condition = Expression.Call(memberAccess, 
                            typeof(string).GetMethod("Contains", new[] { typeof(string) }), 
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break
                            ;
                    //不在其中 string.Contains()
                    case WhereOperation.NotIn:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("Contains", new[] { typeof(string) }), 
                            Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //結束於
                    case WhereOperation.EndWith:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不結束於
                    case WhereOperation.NotEndWith:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
                            Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //包含 string.Contains()
                    case WhereOperation.Contains:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("Contains", new[] { typeof(string) }),
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不包含
                    case WhereOperation.NotContains:
                        condition = Expression.Call(memberAccess,
                            typeof(string).GetMethod("Contains", new[] { typeof(string) }),
                            Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //Null
                    case WhereOperation.Null:
                        condition = Expression.Call(memberAccess, 
                            typeof(string).GetMethod("IsNullOrEmpty"), 
                            Expression.Constant(value));
                        lambda = Expression.Lambda(condition, parameter);
                        break;
    
                    //不Null
                    case WhereOperation.NotNull:
                        condition = Expression.Call(memberAccess, 
                            typeof(string).GetMethod("IsNullOrEmpty"), 
                            Expression.Constant(value));
                        condition = Expression.Not(condition);
                        lambda = Expression.Lambda(condition, parameter);
                        break;
                }
    
                
                MethodCallExpression result = Expression.Call(
                       typeof(Queryable), "Where",
                       new[] { query.ElementType },
                       query.Expression,
                       lambda);
    
                return query.Provider.CreateQuery<T>(result);
            }
        }
    }


    player


    • 已編輯 p1ayer 2013年7月30日 上午 10:14 update
    2013年7月30日 上午 09:19