Dynamic Criteria Problem

6 posts, 1 answers
  1. Visoot
    Visoot avatar
    6 posts
    Member since:
    Jan 2009

    Posted 18 Aug 2010 Link to this post

    Now I am doing search page. the requirement is user can input keyword seperated by comma. The system must find content that match in 'or' case for example user input a,b,c... The system should generate SQL like this

    SELECT * FROM [Table] WHERE [Keyword] LIKE '%a%' OR [Keyword] LIKE '%b%' OR [Keyword] LIKE '%c%'

    so I implemented like this

    string[] keywords = searchKeyword.Split(',');
    Expression<Func<Property, bool>> predicate = PredicateBuilder.False<Property>();
    foreach ( string temp in keywords)
    {
        if (string.IsNullOrEmpty(temp))
            continue;
        string keyword = temp;
        predicate = predicate.Or(obj => obj.SearchKeyword.Contains(keyword));
    }
    query = query.Where(predicate);

    The problem is when I try to test it with input a,b,c the SQL will be like this. I have monitored from SQL Server Profiler.

    SELECT * FROM [Table] WHERE [Keyword ]LIKE '%a%' OR [Keyword] LIKE '%a%' OR [Keyword] LIKE '%a%'

    I have tried to change query from this link http://blogs.telerik.com/blogs/posts/10-04-16/dynamic_filter_expressions_in_an_openaccess_linq_query.aspx because I thought the problem will come from compiled LINQ however the result still wrong.

    Could you please suggest me how to fix this issue or let me know the sample or other way to support in this case.

    Thank you & Regards,
    Visoot Soisang
  2. Visoot
    Visoot avatar
    6 posts
    Member since:
    Jan 2009

    Posted 18 Aug 2010 Link to this post

    I have tried this one it works fine

    string[] keywords = searchKeyword.Split(','); 
    Expression<Func<Property, bool>> predicate = PredicateBuilder.False<Property>(); 
    predicate = predicate.Or(obj => obj.SearchKeyword.Contains("a")); 
    predicate = predicate.Or(obj => obj.SearchKeyword.Contains("b")); 
    predicate = predicate.Or(obj => obj.SearchKeyword.Contains("c")); 
    query = query.Where(predicate);

    The system generated SQL like this

    SELECT * FROM [Table] WHERE [Keyword] LIKE '%a%' OR [Keyword] LIKE '%b%' OR [Keyword] LIKE '%c%'

    Please suggest me how to implement for using the same variable for each expression.

    Thank you & Regards,
    Visoot Soisang
  3. DevCraft banner
  4. Visoot
    Visoot avatar
    6 posts
    Member since:
    Jan 2009

    Posted 19 Aug 2010 Link to this post

    Dear Support Team,

    Could anyone support for me in this case?

    Best regards,
    Visoot Soisang
  5. Answer
    Jordan
    Admin
    Jordan avatar
    547 posts

    Posted 20 Aug 2010 Link to this post

    Hello Visoot,

    It turns out that OpenAccess cannot resolve correctly the field expressions in the combined predicate when they are variables in some loop (foreach for example).
    In order to solve this  you can use a very simple expression visitor to replace the field expressions with constant expressions.

    Here is the expression visitor:
    public class MemberAccessToConstantVisitor : ExpressionVisitor
    {
        private string memberName;
        private object value;
     
        public MemberAccessToConstantVisitor(string memberName, object value)
        {
            this.memberName = memberName;
            this.value = value;
        }
     
        protected override Expression VisitMember(MemberExpression node)
        {
            if (node.Member.Name == this.memberName)
            {
                return Expression.Constant(this.value);
            }
            return base.VisitMember(node);
        }
    }

    And you use it like bellow:
    string[] someIDs = new string[] { "C", "P" };
     
    for (int i = 0; i < someIDs.Length; i++)
    {
        string firstChar = someIDs[i];
     
        Expression<Func<Product, bool>> filterExpression = p => p.ProductName.StartsWith(firstChar);
        MemberAccessToConstantVisitor visitor = new MemberAccessToConstantVisitor("firstChar", firstChar);
        filterExpression = visitor.Visit(filterExpression) as Expression<Func<Product, bool>>;
         
        filterExpressions.Add(filterExpression);
        operators.Add(Expression.OrElse);
    }
    notice how in the above snippet the visitor is used to replace the field expression with a constant just before it is added to the filterExpressions collection.


    Kind regards,
    Jordan
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  6. Visoot
    Visoot avatar
    6 posts
    Member since:
    Jan 2009

    Posted 20 Aug 2010 Link to this post

    Dear Jordan,

    Thank you for your help. Now, It works fine for me however it would be better to have document or tutorial about dynamic criteria like my issue.

    Thank you & Regards,
    Visoot Soisang
  7. Jordan
    Admin
    Jordan avatar
    547 posts

    Posted 23 Aug 2010 Link to this post

    Hello Visoot,

    I am glad that I could help.
    Yes, you are absolutely right.
    And we will actually be making a new example specifically to demonstrate dynamic predicates as it seems that this approach is becoming popular.

    Regards,
    Jordan
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top
DevCraft banner