Case insensitive custom filter operation

[naufilshaikh09]

Hey, if you want to add case insensitive custom filter operation in hotchocolate then you can find solution below.

For ex :

query {
  users(where: { username: { contains: "admin" } }) {
    firstname
    lastname
  }
}

Solution :

protected override void Configure(IFilterConventionDescriptor descriptor)
{
    descriptor.AddDefaults();
    descriptor.Provider(
        new QueryableFilterProvider(
            x => x
                .AddDefaultFieldHandlers()
                .AddFieldHandler<QueryableStringInvariantContainsHandler>()));
}

public class QueryableStringInvariantContainsHandler : QueryableStringOperationHandler
{
    private static readonly MethodInfo _contains = typeof(string).GetMethod("IndexOf",
            new[] { typeof(string), typeof(StringComparison) });

    protected override int Operation => DefaultFilterOperations.Contains;
    public override Expression HandleOperation(QueryableFilterContext context, IFilterOperationField field, IValueNode value, object parsedValue)
    {
        Expression property = context.GetInstance();
        if (parsedValue is string str)
        {
            return Expression.NotEqual(
                    Expression.Call(
                        property,
                        _contains,
                        Expression.Constant(str, typeof(string)),
                        Expression.Constant(StringComparison.InvariantCultureIgnoreCase, typeof(StringComparison))
                    ),
                    Expression.Constant(-1, typeof(int))
                );
        }
        throw new InvalidOperationException();
    }
}

Add filter convention in Startup,cs

services.AddGraphQLServer()
                    .AddFiltering<CustomFilteringConvention>()
                    .AddConvention<IFilterConvention>(
                        new FilterConventionExtension(
                            x => x.AddProviderExtension(
                                new QueryableFilterProviderExtension(
                                    y => y.AddFieldHandler<QueryableStringInvariantContainsHandler>()))));

you can also refer this link too : C# Dynamic lambda problem with implementing "IndexOf" ignore case

[lucas-garrido]

i got error "message: "'_s0.Name.IndexOf("test", InvariantCultureIgnoreCase)' cannot be converted to SQL.""

[PascalSenn]

@lucas-garrido Expressions are a complex thing. You can think about an expression tree as you would write c# by code.
When we compile a query, we compile expressions at runtime. The filter interceptor can intercept this compilation and add own parts to it.

With this you get the reference to the Method IndexOf with the parameters string and StringComparison

    private static readonly MethodInfo _indexOf = typeof(string).GetMethod("IndexOf",
            new[] { typeof(string), typeof(StringComparison) });

Lets say you now have something like string.IndexOf. what we need is yourPorpertyName.IndexOf(str, StringComparison.InvariantCultureIgnoreCase)

This is the bit

 Expression.Call(
    property,   // yourPropertyName.
    _indexOf, // IndexOf(
    Expression.Constant(str, typeof(string)), // str, 
    Expression.Constant(StringComparison.InvariantCultureIgnoreCase, typeof(StringComparison)) // StringComparison.InvariantCultureIgnoreCase)
                    ),

[lucas-garrido]

@PascalSenn @naufil09 Thank you guys, it works ! 🥳

[kbiefang]

Hey, I am having a similar problem. Im getting the following error:

"Additional information: Translation of method 'string.IndexOf' failed.

I am using Postgres 11.12 with EFCore and our resolvers return an IQueryable. As far as I understand LINQ to Entities is responsible for storing our statement in the DB to allow for deferred execution. As I read here the used overload IndexOf(string, StringComparison) is not canonical. Why is this working for you, @naufil09 but not for me?

In another Stackoverflow post I have read something about changing to collation of the database. Might this help?

[naufilshaikh09]

Hey @kbiefang, sorry for late reply.

Your problem is resolved or facing same problem ?

I don't know why it isn't working for you, can you please explain more ?
You can follow this link https://chillicream.com/docs/hotchocolate/api-reference/extending-filtering/#extending-iqueryable or ask question in slack channel too https://hotchocolategraphql.slack.com/.

I am having still little problem in method. With data it's working properly but if there is no data then it is not working properly.

Example :

[kbiefang]

Hey,

as I said I think LINQ to Entities is unable to store the IndexOf(string, StringComparison) operation, because there is no equivalent in the database implementation.

I worked around this by rewriting the expression to use ToLower() and IndexOf(string), which might not be the best solution, but for now it is working.

[arjundnair-fingent]

@naufilshaikh09 This works for me, but not for the inner collections.

For example, my class is

public class User
{
    public string[] Roles { get; set; }

    public IEnumerable<Address> Addresses { get; set; }
}

public class Address
{
    public string Street { get; set; }

    public bool IsPrimary { get; set; }
}

And I need to apply case-insensitive filter on the Addresses object. In that case this solution does not work. Can you please help me by finding a solution for that? Thanks in advance.

Note: My HotChocolate version is V11.

Regards,
Arjun

[europanda]

Try this query:

query
{
users(where: {addresses: {some: {street: {contains: "baker"}}}})
 {
  items
  {
    name
    addresses
    {
      street
      isPrimary
    }
  }
 }
}
``
![Query](https://user-images.githubusercontent.com/25351137/153588179-f481abf5-b556-4832-96d6-6f2b06360bb9.PNG)
`

[europanda]

A fully working and tested project https://github.com/europanda/GraphQL-Example
Сonfigure the connection string in appsettings.json and run.

[lev-smplt]

@europanda Thank you for your solution. But I noticed that this code

public class QueryableStringInvariantContainsHandler : QueryableStringOperationHandler
    {
        public QueryableStringInvariantContainsHandler(InputParser inputParser) : base(inputParser) { }

        protected override int Operation => DefaultFilterOperations.Contains;
        public override Expression HandleOperation(QueryableFilterContext context, IFilterOperationField field, IValueNode value, object parsedValue)
        {
            Expression property = context.GetInstance();
            if (parsedValue is string str)
            {
                var toLower = Expression.Call(property, typeof(string).GetMethod("ToLower", Type.EmptyTypes));
                return Expression.Call(toLower, typeof(string).GetMethod(nameof(string.Contains), new Type[] { typeof(string) }), Expression.Constant(str.ToLower()));
            }
            throw new InvalidOperationException();
        }
  }

will generate sql (for postgresql) like this:
... strpos(lower(a.Name), 'requested_string')>0
As I know strpos lacks the ability to leverage indexes on a.Name and requires scanning the entire table and performing the substring search for each row. It would be better to generate something like this: a.Name ilike '%requested_string%'.
I found problem but not solution, have not got yet how to force generate sql with ilike.

[ciprian-aw]

@lev-smplt this is what worked for me:

public class QueryableStringInvariantContainsHandler : QueryableStringOperationHandler
{
    private static readonly Regex sWhitespace = new Regex(@"\s+");
    private static readonly MethodInfo _ilike = typeof(NpgsqlDbFunctionsExtensions).GetMethod("ILike",
            new[] { typeof(DbFunctions), typeof(string), typeof(string) });

    public QueryableStringInvariantContainsHandler(InputParser inputParser)
        : base(inputParser)
    {
    }

    protected override int Operation => DefaultFilterOperations.Contains;

    public override Expression HandleOperation(
        QueryableFilterContext context,
        IFilterOperationField field,
        IValueNode value,
        object parsedValue)
    {
        Expression property = context.GetInstance();
        var source = Expression.Constant(EF.Functions);
        if (parsedValue is string str)
        {
            var pattern = sWhitespace.Replace(parsedValue as string, "%");
            pattern = $"%{pattern}%";

            return Expression.AndAlso(
                Expression.NotEqual(property, Expression.Constant(null, typeof(object))),
                Expression.Call(
                    null,
                    _ilike,
                    source,
                    property,
                    // Expression.Constant(pattern)
                    CreateParameter(pattern, property.Type)
                )
            );
        }
        throw new InvalidOperationException();
    }

    private static Expression CreateAndConvertParameter<T>(object value)
    {
        Expression<Func<T>> lambda = () => (T)value;
        return lambda.Body;
    }

    private static Expression CreateParameter(object? value, Type type)
        => (Expression)_createAndConvert
            .MakeGenericMethod(type)
            .Invoke(null,
                new[] { value })!;

    private static readonly MethodInfo _createAndConvert =
        typeof(FilterExpressionBuilder)
            .GetMethod(nameof(CreateAndConvertParameter), NonPublic | Static)!;
}

[lev-smplt]

@ciprian-aw, thanks for reply and for solution :) I'll try it.

[arjundnair-fingent]

@europanda This is not what I seek. This is the query to filter the parent Users with the Addresses child. I just want to filter the Addresses child without affecting the Users parent.

[arjundnair-fingent]

@europanda My friend has found out another solution. Thanks for sparing some of your time for me.

[jackdh]

Can you share that solution @arjundnair-fingent ?

[VILLAN3LL3]

Hey, if you want to add case insensitive custom filter operation in hotchocolate then you can find solution below.

For ex :

query {
  users(where: { username: { contains: "admin" } }) {
    firstname
    lastname
  }
}

Solution :

protected override void Configure(IFilterConventionDescriptor descriptor)
{
    descriptor.AddDefaults();
    descriptor.Provider(
        new QueryableFilterProvider(
            x => x
                .AddDefaultFieldHandlers()
                .AddFieldHandler<QueryableStringInvariantContainsHandler>()));
}

public class QueryableStringInvariantContainsHandler : QueryableStringOperationHandler
{
    private static readonly MethodInfo _contains = typeof(string).GetMethod("IndexOf",
            new[] { typeof(string), typeof(StringComparison) });

    protected override int Operation => DefaultFilterOperations.Contains;
    public override Expression HandleOperation(QueryableFilterContext context, IFilterOperationField field, IValueNode value, object parsedValue)
    {
        Expression property = context.GetInstance();
        if (parsedValue is string str)
        {
            return Expression.NotEqual(
                    Expression.Call(
                        property,
                        _contains,
                        Expression.Constant(str, typeof(string)),
                        Expression.Constant(StringComparison.InvariantCultureIgnoreCase, typeof(StringComparison))
                    ),
                    Expression.Constant(-1, typeof(int))
                );
        }
        throw new InvalidOperationException();
    }
}

Add filter convention in Startup,cs

services.AddGraphQLServer()
                    .AddFiltering<CustomFilteringConvention>()
                    .AddConvention<IFilterConvention>(
                        new FilterConventionExtension(
                            x => x.AddProviderExtension(
                                new QueryableFilterProviderExtension(
                                    y => y.AddFieldHandler<QueryableStringInvariantContainsHandler>()))));

you can also refer this link too : C# Dynamic lambda problem with implementing "IndexOf" ignore case

This doesn't seem to work if there are null values in the database. Any suggestions?

[VILLAN3LL3]

Change the HandleOperation expression as follows to make it work with null values in the database:

public override Expression HandleOperation(
            QueryableFilterContext context,
            IFilterOperationField field,
            IValueNode value,
            object parsedValue)
        {
            Expression property = context.GetInstance();
            if (parsedValue is string str)
            {
                return Expression.AndAlso(
                    Expression.NotEqual(property, Expression.Constant(null, typeof(object))),
                    Expression.NotEqual(
                        Expression.Call(
                            property,
                            typeof(string).GetMethod("IndexOf", new[] { typeof(string), typeof(StringComparison) }),
                            Expression.Constant(str),
                            Expression.Constant(StringComparison.OrdinalIgnoreCase)
                        ),
                        Expression.Constant(-1)
                    )
                );
            }

            throw new InvalidOperationException();
        }

[VILLAN3LL3]

If you want to also ignore accents, you can use the following code (caballé == caballe, Fuß == fuss):

public override Expression HandleOperation(
            QueryableFilterContext context,
            IFilterOperationField field,
            IValueNode value,
            object parsedValue)
        {
            Expression property = context.GetInstance();
            if (parsedValue is string str)
            {
                // Bad way of doing it but Using ILIKE on DB level is somehow not working.
                // https://github.com/npgsql/efcore.pg/issues/618
                return Expression.AndAlso(
                    Expression.NotEqual(property, Expression.Constant(null, typeof(object))),
                    Expression.NotEqual(
                        Expression.Call(
                            Expression.Constant(CultureInfo.InvariantCulture.CompareInfo),
                            typeof(CompareInfo).GetMethod(nameof(CompareInfo.IndexOf), [typeof(string), typeof(string), typeof(CompareOptions)]),
                            property,
                            Expression.Constant(str),
                            Expression.Constant(CompareOptions.IgnoreNonSpace | CompareOptions.IgnoreCase)
                        ),
                        Expression.Constant(-1)
                    )
                );
            }

            throw new InvalidOperationException();
        }