Support 'contains' for nested collections

[AlekseyMartynov]

Requested by DevExtreme users:

• https://devexpress.com/issue=T629085
• https://devexpress.com/issue=T637631

Potentially useful for non-relational LINQ providers (Mongo, etc).

[statler]

First up, this is essential, but I don't think that 'contains' is the right operator. I would prefer to see the collection annotated as such, and normal operators used - for example for a person object with a collection of tasks

[['Tasks[].DateActioned', '=', null] 

to get open tasks.

The reason for this is that it provides full access to all operators. Also, it ensures that grouping is available with the original intent. For example, if you apply two contains operators to the same collection, it is unclear whether you want records where the collection contains at least one object meeting each condition separately, or the same object meeting both conditions. In contrast, the following is unambiguous.

[['Tasks[].ActionRequiredDate', '<=', new Date],'and', ['Tasks[].DateActioned', '<>', null]])

Anyway, it turns out that this is also far easier and more concise to code. If you replace your FilterExpressionCompiler with the following, then filtering by nested collections works. It would be great if you could tidy it up as you require and integrate this into the code base. Anyone else can feel free to use it.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DevExtreme.AspNet.Data {

	class FilterExpressionCompiler<T> : ExpressionCompiler {
		const string
			CONTAINS = "contains",
			NOT_CONTAINS = "notcontains",
			STARTS_WITH = "startswith",
			ENDS_WITH = "endswith";

		bool _stringToLower;
		ParameterExpression rootParamExpression;

		public FilterExpressionCompiler(bool guardNulls, bool stringToLower = false)
			: base(guardNulls) {
			rootParamExpression = CreateItemParam(typeof(T));
			_stringToLower = stringToLower;
		}

		public LambdaExpression Compile(IList criteriaJson) {
			var result = Expression.Lambda(CompileCore(rootParamExpression, criteriaJson), rootParamExpression);
			var a = result.Compile();
			return result;
		}

		Expression CompileCore(ParameterExpression dataItemExpr, IList criteriaJson) {
			if(IsCriteria(criteriaJson[0]))
				return CompileGroup(dataItemExpr, criteriaJson);

			if(IsUnary(criteriaJson)) {
				return CompileUnary(dataItemExpr, criteriaJson);
			}

			var clientAccessor = Convert.ToString(criteriaJson[0]);
			if(clientAccessor.Contains("[]")) return CompileCollection(dataItemExpr, criteriaJson);
			return CompileBinary(dataItemExpr, criteriaJson);
		}

		Expression CompileGroup(ParameterExpression dataItemExpr, IList criteriaJson) {
			var operands = new List<Expression>();
			var isAnd = true;
			var nextIsAnd = true;

			ArrayList CollectionCriteria = new ArrayList();
			foreach(var item in criteriaJson) {
				var operandJson = item as IList;

				if(IsCriteria(operandJson)) {
					if(operands.Count > 1 && isAnd != nextIsAnd)
						throw new ArgumentException("Mixing of and/or is not allowed inside a single group");

					isAnd = nextIsAnd;
					bool isCollection = !(operandJson[0] is IList) && operandJson[0].ToString().Contains("[]");
					if(isCollection) CollectionCriteria.Add(item);
					else operands.Add(CompileCore(dataItemExpr, operandJson));
					nextIsAnd = true;
				} else {
					nextIsAnd = Regex.IsMatch(Convert.ToString(item), "and|&", RegexOptions.IgnoreCase);
				}
			}

			if(CollectionCriteria.Count > 0) {
				List<Expression> CollectionOperands = GetCollectionOperands(dataItemExpr, CollectionCriteria, isAnd);
				operands.AddRange(CollectionOperands);
			}

			Expression result = null;
			var op = isAnd ? ExpressionType.AndAlso : ExpressionType.OrElse;

			foreach(var operand in operands) {
				if(result == null)
					result = operand;
				else
					result = Expression.MakeBinary(op, result, operand);
			}

			return result;
		}

		//This doesnt get called but left it here for reference. Work is done in CompileGroups
		Expression CompileCollection(ParameterExpression dataItemExpr, IList criteriaJson) {
			var CollectionOperators = GetCollectionOperands(dataItemExpr, new ArrayList() { criteriaJson }, true);
			return CollectionOperators[0];
		}

		Expression CompileBinary(ParameterExpression dataItemExpr, IList criteriaJson) {
			var hasExplicitOperation = criteriaJson.Count > 2;

			var clientAccessor = Convert.ToString(criteriaJson[0]);
			var clientOperation = hasExplicitOperation ? Convert.ToString(criteriaJson[1]).ToLower() : "=";
			var clientValue = criteriaJson[hasExplicitOperation ? 2 : 1];
			var isStringOperation = clientOperation == CONTAINS || clientOperation == NOT_CONTAINS || clientOperation == STARTS_WITH || clientOperation == ENDS_WITH;

			var accessorExpr = CompileAccessorExpression(dataItemExpr, clientAccessor, progression => {
				if(isStringOperation || clientAccessor is String && progression.Last().Type == typeof(Object))
					ForceToString(progression);
				if(_stringToLower)
					AddToLower(progression);
			});

			if(isStringOperation) {
				return CompileStringFunction(accessorExpr, clientOperation, Convert.ToString(clientValue));
			} else {
				var useDynamicBinding = accessorExpr.Type == typeof(Object);
				var expressionType = TranslateBinaryOperation(clientOperation);

				if(!useDynamicBinding) {
					try {
						clientValue = Utils.ConvertClientValue(clientValue, accessorExpr.Type);
					} catch {
						return Expression.Constant(false);
					}
				}

				if(clientValue == null && !Utils.CanAssignNull(accessorExpr.Type)) {
					switch(expressionType) {
						case ExpressionType.GreaterThan:
						case ExpressionType.GreaterThanOrEqual:
						case ExpressionType.LessThan:
						case ExpressionType.LessThanOrEqual:
							return Expression.Constant(false);

						case ExpressionType.Equal:
						case ExpressionType.NotEqual:
							accessorExpr = Expression.Convert(accessorExpr, Utils.MakeNullable(accessorExpr.Type));
							break;
					}
				}

				if(_stringToLower && clientValue is String)
					clientValue = ((string)clientValue).ToLower();

				Expression valueExpr = Expression.Constant(clientValue, accessorExpr.Type);

				if(accessorExpr.Type == typeof(String) && IsInequality(expressionType)) {
					var compareMethod = typeof(String).GetMethod(nameof(String.Compare), new[] { typeof(String), typeof(String) });
					accessorExpr = Expression.Call(null, compareMethod, accessorExpr, valueExpr);
					valueExpr = Expression.Constant(0);
				} else if(useDynamicBinding) {
					accessorExpr = Expression.Call(typeof(Utils).GetMethod(nameof(Utils.DynamicCompare)), accessorExpr, valueExpr);
					valueExpr = Expression.Constant(0);
				}

				return Expression.MakeBinary(expressionType, accessorExpr, valueExpr);
			}
		}

		bool IsInequality(ExpressionType type) {
			return type == ExpressionType.LessThan || type == ExpressionType.LessThanOrEqual || type == ExpressionType.GreaterThanOrEqual || type == ExpressionType.GreaterThan;
		}

		Expression CompileStringFunction(Expression accessorExpr, string clientOperation, string value) {
			if(_stringToLower && value != null)
				value = value.ToLower();

			var invert = false;

			if(clientOperation == NOT_CONTAINS) {
				clientOperation = CONTAINS;
				invert = true;
			}

			if(GuardNulls)
				accessorExpr = Expression.Coalesce(accessorExpr, Expression.Constant(""));

			var operationMethod = typeof(String).GetMethod(GetStringOperationMethodName(clientOperation), new[] { typeof(String) });

			Expression result = Expression.Call(accessorExpr, operationMethod, Expression.Constant(value));

			if(invert)
				result = Expression.Not(result);

			return result;
		}

		Expression CompileUnary(ParameterExpression dataItemExpr, IList criteriaJson) {
			return Expression.Not(CompileCore(dataItemExpr, (IList)criteriaJson[1]));
		}

		ExpressionType TranslateBinaryOperation(string clientOperation) {
			switch(clientOperation) {
				case "=":
					return ExpressionType.Equal;

				case "<>":
					return ExpressionType.NotEqual;

				case ">":
					return ExpressionType.GreaterThan;

				case ">=":
					return ExpressionType.GreaterThanOrEqual;

				case "<":
					return ExpressionType.LessThan;

				case "<=":
					return ExpressionType.LessThanOrEqual;
			}

			throw new NotSupportedException();
		}

		bool IsCriteria(object item) {
			return item is IList && !(item is String);
		}

		internal bool IsUnary(IList criteriaJson) {
			return Convert.ToString(criteriaJson[0]) == "!";
		}

		string GetStringOperationMethodName(string clientOperation) {
			if(clientOperation == STARTS_WITH)
				return nameof(String.StartsWith);

			if(clientOperation == ENDS_WITH)
				return nameof(String.EndsWith);

			return nameof(String.Contains);
		}

		static void AddToLower(List<Expression> progression) {
			var last = progression.Last();

			if(last.Type != typeof(String))
				return;

			var toLowerMethod = typeof(String).GetMethod(nameof(String.ToLower), Type.EmptyTypes);
			var toLowerCall = Expression.Call(last, toLowerMethod);

			if(last is MethodCallExpression lastCall && lastCall.Method.Name == nameof(ToString))
				progression.RemoveAt(progression.Count - 1);

			progression.Add(toLowerCall);
		}

		private List<Expression> GetCollectionOperands(ParameterExpression dataItemExpr, IList CollectionCriteria, bool isAnd) {
			Dictionary<string, IList> dctCollection = GetCollectionCriteriaDictionary(isAnd, CollectionCriteria);

			var operands = new List<Expression>();
			foreach(KeyValuePair<string, IList> coll in dctCollection) {
				Expression fieldCondition = CompileCollectionFunction(dataItemExpr, coll.Key, coll.Value);

				operands.Add(fieldCondition);
			}
			return operands;
		}

		private Expression CompileCollectionFunction(ParameterExpression dataItemExpr, string CollectionProperty, IList CollectionCriteria) {
			var accessorExpr = CompileAccessorExpression(dataItemExpr, CollectionProperty, progression => {
				if(CollectionProperty is String && progression.Last().Type == typeof(Object))
					ForceToString(progression);

				if(_stringToLower)
					AddToLower(progression);
			});

			Type genericType = accessorExpr.Type.GetGenericArguments().Single();
			var fieldParameter = Expression.Parameter(genericType, "field");
			var oper = CompileCore(fieldParameter, CollectionCriteria as IList);
			var anyPredicate = Expression.Lambda(oper, fieldParameter);
			var fieldCondition = Expression.Call(typeof(Enumerable), "Any", new[] { fieldParameter.Type }, accessorExpr, anyPredicate);
			return fieldCondition;
		}

		private static Dictionary<string, IList> GetCollectionCriteriaDictionary(bool isAnd, IList CollectionCriteria) {
			Dictionary<string, IList> dctCollection = new Dictionary<string, IList>();
			foreach(object c in CollectionCriteria) {
				var operandJson = c as IList;
				if(operandJson is null) continue;
				var collSplit = operandJson[0].ToString().Split(new string[] { "[]." }, StringSplitOptions.None);

				ArrayList newoperandJson = new ArrayList();
				for(int i = 0; i < operandJson.Count; i++) {
					if(i == 0) newoperandJson.Add(collSplit[1]);
					else newoperandJson.Add(operandJson[i]);
				}

				if(collSplit.Count() > 1) {
					string collection = collSplit[0];
					if(dctCollection.ContainsKey(collection)) {
						dctCollection[collection].Add(isAnd ? "and" : "or");
						dctCollection[collection].Add(newoperandJson);
					} else dctCollection.Add(collection, new ArrayList() { newoperandJson });
				}
			}
			return dctCollection;
		}
	}

}

[AlekseyMartynov]

Hello @statler
I assisted Maxim in his original answer.
Let me elaborate.

Your suggestion is to extend the filter expression syntax so that

[ "Tasks[].DateActioned", "<>", null ]

is translated into

Tasks.Any(i => i.DateActioned != null)

Adding such a new syntax is a more broad task, because it's used not only in this library but across the entire DevExtreme Data Layer (docs).

My idea is that if you can encapsulate a condition in a calculated property:

public bool HasActionedDate {
    get { return Tasks.Any(i => i.DateActioned != null); }
}

- or describe a helper collection of primitives:

public IEnumerable<DateTime?> TaskActionedDates {
    get { return Tasks.Select(i => i.DateActioned); }
}

then you can use filter capabilities that are currently supported or that are candidates for implementation.

[Bykiev]

@AlekseyMartynov, any progress on it? We need support search on collection of objects (also filtering if possible)

P.s. I've tried to implement it on backend by reading Filter value, then I searched for target collection, apply contains (or not contains) filter and remove it from the filter. But it's quite complicated when search and grouping applied to table (in this case Filter item is array of arrays).

[AlekseyMartynov]

@Bykiev
This task hasn't been started yet.
Would you please share an example of an object graph and a filter that you need to evaluate on it?

[Bykiev]

@AlekseyMartynov, thx!

I've a Product which can be manufactured by multiple factory. So, I have one-to-many relation between Product and Factory tables. I need to display products table with manufacturers (right now I'm using calculateCellValue method to display factory names in one column separated by comma). Because of it, I need to search on collection of objects (allow contains/notcontains search), which is not supported by DevExtreme.AspNet.Data library.
Regarding to the filter, simple 'contains' would be enough.

[AlekseyMartynov]

In the context of this ticket, we plan to support search in collections of values (tags, keywords, numbers, etc). For example:

[
  [ "Tags", "contains", "release" ], 
  "or",
  [ "Tags", "contains", "news" ]
]

Search in a one-to-many collection, if I understand your requirements correctly, is an analog of LINQ Any:

_nwind.Categories.Where(c => c.Products.Any(p => p.ProductName.Contains("che")));

To add this, we first need to extend DevExtreme filter expression syntax.

If you share a runnable sample project (here or via Support Center), we can come up with a quick workaround for your specific case.

[Bykiev]

@AlekseyMartynov, you're right, this LINQ query example is exactly what I need.
Same here with 'not contains':

_nwind.Categories.Where(c => c.Products.All(p => !p.ProductName.Contains("che")));

If the property type is number 'equal', 'not equal', 'greater' and 'lower' filter should be supported:

_nwind.Categories.Where(c => c.Products.Any(p => p.Price == somePrice);

I'll provide a runnable example tomorrow via support center and link back here

[Bykiev]

@AlekseyMartynov, my collegue posted a runnable sample with this issue on support center: https://www.devexpress.com/Support/Center/Question/Details/T745959

[AlekseyMartynov]

Version 2.3.0 introduces a new API to intercept and customize the translation of filter parts. Check code samples in the release notes.

[Bykiev]

@AlekseyMartynov, thank you!

[frankiDotNet]

Sorry, but I have to say it: This was one of the best extensions idea !!! It helped me in many situations!
🥇

[AlekseyMartynov]

@Franki1986

It helped me in many situations!

Great! I know that CustomFilterCompilers helped you solve #210. Do you build any other interesting custom filter expressions?

[frankiDotNet]

Yes.. I am still experimenting to check all error cases but with using System.LinqDynamic.Core:

CustomFilterCompilers.RegisterBinaryExpressionCompiler(info => {
            // If access list operations for Any():
            // FilterSyntax = ["[{childlist}].{listElement}", "{operator}", "{value}"]
           // Filter = ["[ChildList].Id", "=", "1"]
            if (info.AccessorText.StartsWith("[") && info.AccessorText.IndexOf("]") > 0 && info.AccessorText.IndexOf(".") == (info.AccessorText.IndexOf("]") + 1))
            {
               var elements = info.AccessorText.Split('.');
               var propName = elements[1];

               var enumerableName = elements[0].TrimStart('[').TrimEnd(']');
               var enumerableInfo = info.DataItemExpression.Type.GetProperty(enumerableName);
               var enumerableType = enumerableInfo.PropertyType.GetGenericArguments()[0];
               var propertyTypeInfo = enumerableType.GetProperty(propName);

               var exprString = "";
               switch (info.Operation)
               {
                  case "<>":
                     exprString = $"{propName} != @0";
                     break;
                  case ">":
                     exprString = $"{propName} > @0";
                     break;
                  case "<":
                     exprString = $"{propName} < @0";
                     break;
                  case ">=":
                     exprString = $"{propName} >= @0";
                     break;
                  case "<=":
                     exprString = $"{propName} <= @0";
                     break;
                  case "contains":
                     exprString = $"{propName}.Contains(@0)";
                     break;
                  case "endswith":
                     exprString = $"{propName}.Contains(@0)";
                     break;
                  case "startswith":
                     exprString = $"{propName}.Contains(@0)";
                     break;
                  case "notcontains":
                     exprString = $"!{propName}.Contains(@0)";
                     break;
                  case "&":
                     exprString = $"({propName} & @0) == @0";
                     break;
                  default:
                     exprString = $"{propName} == @0";
                     break;
               }

               var convertedValue = Convert.ChangeType(info.Value, propertyTypeInfo.PropertyType);
               var valueExpression = Expression.Constant(convertedValue);
               var innerLambda = DynamicExpressionParser.ParseLambda(enumerableType, typeof(bool), exprString, valueExpression);
               var result = Expression.Call(
                  typeof(Enumerable), nameof(Enumerable.Any), new[] { enumerableType },
                  Expression.Property(info.DataItemExpression, enumerableName), innerLambda
               );
               return result;
            }
            // if an element is an Enumerable, convert not null and null comparison to Any() and not Any()
            if (info.Value == null && (info.Operation == "<>" || info.Operation == "="))
            {
               try
               {
                  var accessorInfo = info.DataItemExpression.Type.GetProperty(info.AccessorText);
                  if (accessorInfo.PropertyType.GetInterface("ICollection") != null)
                  {
                     var enumerableType = accessorInfo.PropertyType.GetGenericArguments()[0];
                     var result = Expression.Call(
                        typeof(Enumerable), nameof(Enumerable.Any), new[] { enumerableType },
                        Expression.Property(info.DataItemExpression, info.AccessorText));
                     if (info.Operation == "=")
                     {
                        return Expression.Not(result);
                     }

                     return result;
                  }
               }
               catch (Exception) { }
            }
            if (info.Operation == "&")
            {
               var value = Convert.ToInt32(info.Value);
               return Expression.Equal(
                  Expression.MakeBinary(
                     ExpressionType.And,
                     Expression.Property(info.DataItemExpression, info.AccessorText),
                     Expression.Constant(value)
                  ),
                  Expression.Constant(value)
               );
            }
            return null;
         });
      }

[AlekseyMartynov]

@shahabfar

Our Support Team will address your questions in the context of your ticket (ID T848079).

I kindly ask you to avoid cross-posting issues in the future. This will save time and allow us to process all your inquiries in the most efficient manner.

[statler]

I wrote a neat little helper for this that allows the use of dot notation on collections for any collection on the object. Figured others would probably find it useful. This is for querying across a many:many relationship. Saves a mountain of code and genericises this method significantly.

Use with annotation like on the orders collection to get all orders containing any product with id 184, of products with Id 184 or 189,

    ["ProductOrders.ProductId","contains",184]
    ["ProductOrders.ProductId","contains",[184,189]]

Register a CustomFilter like this

    public static void RegisterFilters()
    {
        CustomFilterCompilers.RegisterBinaryExpressionCompiler(info =>
        {
			if (info.Operation == "contains")
			{
				var propertySplit = info.AccessorText.Split('.').ToList();
				if (propertySplit.Count == 2)
				{
					var collectionName = propertySplit[0];
					var selectorName = propertySplit[1];

					var pBaseExp = Expression.Parameter(info.DataItemExpression.Type, "qryType");
					var pBaseProperty = Expression.PropertyOrField(pBaseExp, collectionName);
					var collectionItemType = pBaseProperty.Type.GetGenericArguments().Single();
					var pCollExp = Expression.Parameter(collectionItemType, "qryColl");
					var selectorProperty = Expression.PropertyOrField(pCollExp, selectorName);

					LambdaExpression anyPredicate = null;
					//Contains Expression
					if (info.Value is JArray lstJId)
					{
						var arrayId = lstJId.Select(jv => jv.ToObject(selectorProperty.Type)).ToArray();
						var listType = typeof(List<>);
						var constructedListType = listType.MakeGenericType(selectorProperty.Type);
						var lstQry = (IList)Activator.CreateInstance(constructedListType);
						foreach (JToken jToken in lstJId)
						{
							lstQry.Add(jToken.ToObject(selectorProperty.Type));
						}

						var method = lstQry.GetType().GetMethod("Contains");
						var call = Expression.Call(Expression.Constant(lstQry), method, selectorProperty);
						anyPredicate = Expression.Lambda(call, pCollExp);
					}
					else
					{
						anyPredicate = Expression.Lambda(
							Expression.Equal(selectorProperty,
							Expression.Convert(Expression.Constant(info.Value), selectorProperty.Type)),
						pCollExp);
					}                        

					var result = Expression.Call(
						typeof(Enumerable), "Any", new[] { pCollExp.Type },
						Expression.PropertyOrField(info.DataItemExpression, collectionName), anyPredicate);

					return result;
				}
			}
		}
	}

[MattDBP]

@statler Your solution is very helpful, thanks for sharing.

Any chance for support for contains?

e.g. matching is there is a ProductName that contains 'ba' instead of if a Product name equals 'ba'?

["ProductOrders.ProductName","contains","ba"]

[statler]

You just need to modify the else clause

	else
	{
		anyPredicate = Expression.Lambda(
			Expression.Equal(selectorProperty,
			Expression.Convert(Expression.Constant(info.Value), selectorProperty.Type)),
		pCollExp);
	}  

and change anyPredicate so it tests for contains instead of equality. A google search will help out here - I am on the road for a week or so, so cant give specifics as I am stretched for time - but hopefully that points you in the right direction