Answered

How to create a Filter Expression with AND & OR?

(0) Share

Report

Posted on by Community Member

Hello everyone,

I'm using a Query Expression to filter some data, and I can't figure out how to combine the LogicalOperator.AND & LogicalOperator.OR together.

To explain this better I'll give you the following example. I get a string with keywords like this "MySQL Javascript". I split the string by spaces, and I add a condition for every word (Filter.FilterOperator = LogicalOperator.AND) - this way I will get the results that contain MySQL and Javascript in the same result.

I'm trying to do the same for the words that are lower case. That way I'll find the words that are not specifically written with the upper cases. I would've searched only in lower case, but my data is case sensitive.

So basically I need to know how to get all the results that contain the upper or lower case from the given words.

Anyone?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

You could always use a fetchxml statement ... then convert it into a query expression.

msdn.microsoft.com/.../hh547457.aspx

Was this reply helpful? Yes No
Verified answer

Aileen Gusni 44,524 on at

Like (3)

Report
Copy link

Link copied!
Hi Georgi,

This is the example to use complex query with multiple filter expression:

FilterExpression filter = new FilterExpression(LogicalOperator.Or); FilterExpression filter1 = new FilterExpression(LogicalOperator.And); filter1.Conditions.Add(new ConditionExpression("A_LogicalName", ConditionOperator.Equal, id1)); filter1.Conditions.Add(new ConditionExpression("B_LogicalName", ConditionOperator.Equal, id2)); FilterExpression filter2 = new FilterExpression(LogicalOperator.And); filter2.Conditions.Add(new ConditionExpression("B_LogicalName", ConditionOperator.Equal, id2)); filter2.Conditions.Add(new ConditionExpression("C_LogicalName", ConditionOperator.Equal, id3)); filter.AddFilter(filter1); filter.AddFilter(filter2); query.Criteria = filter;

and this:

Criteria = { Filters = { new FilterExpression { FilterOperator = LogicalOperator.And, Conditions = { new ConditionExpression("address1_stateorprovince", ConditionOperator.Equal, "WA"), new ConditionExpression("address1_city", ConditionOperator.In, new String[] {"Redmond", "Bellevue" , "Kirkland", "Seattle"}), new ConditionExpression("createdon", ConditionOperator.LastXDays, 30), new ConditionExpression("emailaddress1", ConditionOperator.NotNull) }, }, new FilterExpression { FilterOperator = LogicalOperator.Or, Conditions = { new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(206)%"), new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(425)%") } } } }

Source:

http://stackoverflow.com/questions/14318160/multiple-conditions-for-a-filterexpression

https://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.query.queryexpression.aspx

Or you can use LINQ, example here:

http://stackoverflow.com/questions/23142435/why-does-string-equals-work-for-case-insensitivity-by-default

Btw if your purpose want to get result not case sensitive you can use toLower as the conditional

Sample here:

if (!String.IsNullOrEmpty(fieldname)) query.Criteria.AddCondition("fieldname".ToLower(), ConditionOperator.Equal, fieldname.ToLower()); EntityCollection col = service.RetrieveMultiple(query);

http://crmonaroll.blogspot.in/2013/06/case-in-sensitive-search-in-mscrm-2011.html

Hope this helps you, Georgi!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

And once again, the day was saved!

Thank you Aileen : )

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

What's about Fluent Fetch XML ? github.com/.../DemoQueries.cs

opinions?

Was this reply helpful? Yes No
Mohammed Fakhri 402 on at

Like (0)

Report
Copy link

Link copied!

How many conditions can be added in one filter condition in query expression ?

Was this reply helpful? Yes No