Case insensitive filtering

[mickoymouse]

Is there a way to have the library do case insensitive filtering by using ILIKE operator instead of doing a lower(column) LIKE?

I do understand ILIKE is a postgresql specific and since that's what we are using, I was wondering if there is a way to handle =* operation with it instead.

[alirezanet]

Hi @mickoymouse,
Have you tried the case-insensitive operator?

[alirezanet]

No, you can't change the existing operators, but you can add new ones to extend the functionality

[alirezanet]

this also should work: public string GetOperator() => "#=*";

[mickoymouse]

I did try doing this

public class ILikeOperator : IGridifyOperator
    {
        public string GetOperator()
        {
            return "#=*";
        }

        public Expression<OperatorParameter> OperatorHandler()
        {
            return (prop, value) => EF.Functions.ILike((prop as string)!, (value as string)!);
        }
    }

and registering it like this (same way I did for my IN operator):
GridifyGlobalConfiguration.CustomOperators.Register<ILikeOperator>();

but I am getting the plain LIKE query: where column LIKE @__Value_0_contains.

Am I missing a step here?

[alirezanet]

It is up to the entity framework postgreSql provider to translate the expression you provided to an ILike query,
I never used the ILike function before, but if this example or this one is accurate, apparently you should put some % after or before the values ...

to avoid any confusion that is a Gridify problem or EntityFramework, I suggest testing your query with pure Linq, and then comparing the results with the Gridify-generated expression.
something like:

var linqVersion = context.Company.Where(i => EF.Functions.ILike(i.Name, "xxx")).ToList();

does this work as expected?

[mickoymouse]

Yes. That works as expected. With that I am planning to isolate the =* operator and apply EF.Functions.ILike for it. I have tested it but only with 1 filter so far. I'll test and see if there would be any issue if I fully combine it with the rest of Gridify filters (I don't see why there would be an Issue but I haven't tested yet). Hopefully there isn't as the paging and ordering are all working as expected with it.

If there's no other way, I think I will just work with this workaround for now unless you have more ideas I can try?

I appreciate your responses and thank you for your time.