This is a migrated thread and some comments may be shown as answers.

Dynamic Criteria Problem

5 Answers 129 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Visoot
Top achievements
Rank 1
Visoot asked on 18 Aug 2010, 09:23 PM

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

5 Answers, 1 is accepted

Sort by
0
Visoot
Top achievements
Rank 1
answered on 18 Aug 2010, 09:34 PM
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
0
Visoot
Top achievements
Rank 1
answered on 20 Aug 2010, 04:31 AM
Dear Support Team,

Could anyone support for me in this case?

Best regards,
Visoot Soisang
0
Accepted
Jordan
Telerik team
answered on 20 Aug 2010, 08:14 AM
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
0
Visoot
Top achievements
Rank 1
answered on 20 Aug 2010, 10:46 AM
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
0
Jordan
Telerik team
answered on 23 Aug 2010, 09:48 AM
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
Tags
LINQ (LINQ specific questions)
Asked by
Visoot
Top achievements
Rank 1
Answers by
Visoot
Top achievements
Rank 1
Jordan
Telerik team
Share this question
or