Passing filter value on CustomOperator Expression creation - for ContainsAny operator

[Kuchasz]

There is one thing in that awesome library that i struggle with as much i can't overcome on my own.

The problem

What i try to achieve is a filter that allows finding rows containing any of values i look for. Look at the example below:

//the model i want to filter out
class Artist {
    public string[] FavouriteColors { get; set; }
}

//my custom operator (already registered)
class ContainsAnyOperator : IGridifyOperator
{
    public string GetOperator()
    {
        return "#ContainsAny";
    }

    public Expression<OperatorParameter> OperatorHandler()
    {
        return (prop, value) => ((string[])prop).Any(p => ((string[])value).Contains(p));
    }
}

//somewhere in my data fetching level
var mapper = new GridifyMapper<Artist>()
    .GenerateMappings()
    .AddMap(nameof(Artist.FavouriteColors), h => h.FavouriteColors, value => value.Split(";", StringSplitOptions.RemoveEmptyEntries).ToArray());

var result = query.GridifyQueryable(queryModel.ToGridifyQuery(), mapper);

//filter expression sent from the client side
//favouriteColors #ContainsAny red;green;blue

EFCore can't handle such expressions properly. It just throws an exceptions that such expressions may not be translated into sql queries.

Proposed solution

A workaround may be combining multiple .Contains statements into single Expression with Or operator. I'd like to achieve that at custom operator level but it seems to be impossible. Custom operators API gives no information about the filter value during Expression instance creation. Maybe passing the filter value (OperatorParameter.value) as parameter to OperatorHandler function would allow me to create such aggregated expression? I'd just turn every collection item into Expression like prop.Contains(item) and then Aggregate them with Expression.OrElse. Value used in filters is known ahead of SQL query execution, passing it to the Expression "builder" function should be possible (i hope).

The bad workaround

That issue may be also overcomed by creation of IN style operator you described few months ago #135 and combining multiple its statements at the client side but it looks like overuse of APIs provided by the library:

(favouriteColors #In red|favouriteColors #In green|favouriteColors #In blue)

[alirezanet]

Hi @Kuchasz,
I wouldn't recommend using a Custom operator in this case unless you want to use the EF.Functions,
look at this example and let me know if it works for you:

var dataSource = new List<Artist>() { new() {FavouriteColors = ["Green", "Blue"]}}.AsQueryable();

var mapper = new GridifyMapper<Artist>()
		.AddMap("fc", w=> w.FavouriteColors.Select(fc => fc)); // Gridify NestedCollection feature


var query = dataSource.ApplyFiltering("fc=Red|fc=Blue", mapper);

var lst = query.ToList().Dump();

class Artist
{
	public string[] FavouriteColors { get; set; }
}

With a similar mapping, you should also be able to create a custom mapper for the #In operator I think.

[Kuchasz]

Your snippet of code works well but with IQueryable datasource tied to in-memory collection. It does not work with IQueryable coming from EFCore DBContext. The exception i get is the same exception like that one in case of CustomOperator trying to do both .Any and .Contains at the same time:

...query... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

It would be perfect to define single filter statement with multiple values in it. instead of defining multiple filter statements with single value each "fc #Operator Red,Blue" vs fc=Red|fc=Blue. I was able to achieve first query working but only with fc being single value not collection of values.

NOTE: Im using EFCore 7.x, i've seen some articles about primitives collections search improvements in EFCore 8. Maybe such queries work well on newer version.

[alirezanet]

Since Gridify is just converting string queries to LINQ expressions, it would be nice if you share your desired LINQ version of what you expect, so we can compare Gridify results with it and see why EntityFramework is complaining ...
for example, some expressions no matter generated by Gridify or it is native LINQ are not supported by EntityFramework and we can't do anything about that.
So the question is how do you do it with LINQ if you were not using Gridify?

[Kuchasz]

LINQ queries to EFCore will have exactly the same issues. I assume the same mechanisms are used underneath by Gridify. It is possible to build LINQ Expression the way that it could be handled by EFCore. The only solution for now is generation of multiple where in statements combined with OR. I want Gridify to do that for me when i specify 'ContainsAny' operator. I dont want the need to specify the filters this way: fc=Red|fc=Blue|fc=Green|fc=Foo everywhere, client side multiselect components don't work this way. I'd like to define that filter this way: fc #In Red;Blue;Green;Foo.

Gridify's custom operator mechanisms let the users build their own LINQ expressions. Filter's value (array of desired values) is not passed to method that builds the Expression but to expression body. With values known one level higher it should possible to build operator i need. Treat below code as a pseudocode, its something generated by chatgpt. It creates below expression dynamically.

query.Where(a =>a.FavouriteColors.Contains("Red") 
    || a.FavouriteColors.Contains("Blue") 
    || a.FavouriteColors.Contains("Green") 
    || a.FavouriteColors.Contains("Foo"));

    public Expression<OperatorParameter> OperatorHandler(string[] values)
    {
        // Parameters for the input arrays
        var paramProp = Expression.Parameter(typeof(object), "propertyReference");
        var paramValues = Expression.Parameter(typeof(object), "value");

        // Convert parameters to string arrays
        var propAsStringArray = Expression.Convert(paramProp, typeof(string[]));
        var valuesAsStringArray = Expression.Convert(paramValues, typeof(string[]));

        // Start with a false expression
        Expression containsAnyExpression = Expression.Constant(false);

        // Iterate through values to build the Contains expressions
        foreach (var str in values)
        {
            // Expression for prop.Contains(str)
            var containsExpression = Expression.Call(
                typeof(Enumerable),
                nameof(Enumerable.Contains),
                new Type[] { typeof(string) },
                propAsStringArray,
                Expression.Constant(str)
            );

            // Combine with the existing expressions using Expression.OrElse
            containsAnyExpression = Expression.OrElse(containsAnyExpression, containsExpression);
        }

        // Create and return the final lambda expression
        return Expression.Lambda<OperatorParameter>(containsAnyExpression, paramProp, paramValues);
    }

[alirezanet]

LINQ queries to EFCore will have exactly the same issues.

This is EntityFramework limitation, and I think it is fixed in EF8 so honestly I don't think it would be a useful feature in the near future since first, no one is going to use it anymore because #In operator works as expected even in EntityFramework latest versions and second the syntax is just different and this fc=Red|fc=Blue syntax is working as expected.

although, if you want to use Expression to alter the query you have access to the FilteringExpressions and SyntaxTree in Gridify, but unfortunately most of the classes in the Syntax namespace at this moment are internal:

// generates the whole syntax tree
var syntaxTree = SyntaxTree.Parse("fc=Blue;Red", null);

var mapper = new GridifyMapper<Artist>().AddMap("fc", q=>q.FavouriteColors.Select(fc=>fc));

// generates the query expression 
var expression = syntaxTree.CreateQuery<Artist>(mapper);

---

I'll try to make some internal APIs public in the next versions so you can use them in these situations.