Case Insensitive search

[markosimulak]

Hi!

I'm using PostgreSQL and as I can see, search is case sensitive.

How can I apply search which is case insensitive?

I'm looking at class SpecificationBuilderExtensions and SearchByKeyword<T> method and as I can see only property name ignoring the case.

Is it the right place to modify and how to modify query?

[fretje]

Hmm that's strange... SearchByKeyword is adding a SearchExpressionInfo with $"%{value}%" as value to the specification which should be translated on the db side to a LIKE '%value%' clause (which should be case insensitive, right?)... it's doing it for sql server for sure... if it doesn't for prostgresql then this will be an issue with ardalis.specification.entityframeworkcore or the postgresql provider itself I'm afraid...

Note: this is where that translation happens: https://github.com/ardalis/Specification/blob/main/Specification.EntityFrameworkCore/src/Ardalis.Specification.EntityFrameworkCore/Extensions/SearchExtension.cs

PS: you can uncomment some lines in BaseDbContext (at least in the latest source) to see the exact queries that efcore is executing...

Btw, just so you know: Specifications only define what exactly it is it needs to select/filter/sort, they must be seen as lightweight dto's which just contain the metadata about a query. They don't execute anything. The execution itself is up to the SpecificationEvaluator which lives in infrastructure, while the specfications live in the application/domain layer...
So where you are looking in specificationbuilderextensions (which lives in application), that code is actually only adding a searchcriteria to the specification (defining what it is it needs to search for). The actual searching doesn't happen there.

[fretje]

It seems postgresql's default collation plays a part in it... here's some interesting links on the topic:
https://stackoverflow.com/questions/7005302/how-to-make-case-insensitive-query-in-postgresql
https://stackoverflow.com/questions/43277868/entity-framework-core-contains-is-case-sensitive-or-case-insensitive

I've created an issue on the ardalis specification repo ;-)

[markosimulak]

Hmm okk...

I used EF.Functions.ILike extension for EF and that worked fine.

Thank you for explanation about specifications. This is the first time I faced up with them, so in case I fail to figure out some "cleaner" solution, I have backup plan..

I added two searchCriteria next to existing one:

// existing one
((List<(Expression<Func<T, string>> Selector, string SearchTerm, int SearchGroup)>)specificationBuilder.Specification.SearchCriterias) .Add((selector, $"%{search.Keyword}%", 1));

// for lower case
((List<(Expression<Func<T, string>> Selector, string SearchTerm, int SearchGroup)>)specificationBuilder.Specification.SearchCriterias) .Add((selector, $"%{search.Keyword.ToLower()}%", 1));

// for upper case
((List<(Expression<Func<T, string>> Selector, string SearchTerm, int SearchGroup)>)specificationBuilder.Specification.SearchCriterias) .Add((selector, $"%{search.Keyword.ToUpper()}%", 1));

[274188A]

most databases by default are not set up as case sensitive - do we really need case sensitivity?

[matthewcorven]

most databases by default are not set up as case sensitive - do we really need case sensitivity?

I disagree @274188A. Support for unicode is a necessity when developing for locales communicating outside what ASCII provides. With a unicode database comes case sensitivity.

[fretje]

I fail to see how that's a solution to your problem... if you do ToUpper/ToLower on the value, you need to do ToUpper/ToLower on the selector as well...
Adding those searchcriteria will not give you the result you want...

[markosimulak]

Exactly :D

At the end I removed added search criteria and created specification only for this specific search.
Inside that specification I used EF.Functions.ILike method, which is case insensitive ;)
That should be much safer solution, because it cover cases when some entries are title case.

[fretje]

Hmm... that doesn't sound "clean"... your specifications shouldn't contain references to infrastructure related things... You should put that code in an evaluator.

[fretje]

ardalis/Specification#231 ;-)

[markosimulak]

Wow, nice :)

I tried to figure out how to extend, but without any success...so far.
With custom specification, which I mentioned before, it's too slow.

[fretje]

It has been answered already! This is how to do it...

[snax4a]

Hi @fretje,
I saw your discussion and custom specification evaluator example here ardalis/Specification#231 but Im still not able to glue it with the EF.Functions.ILike.
Have you managed to implement this case insensitive search for postgresql with custom evaluator?

I also wonder where this evaluator class should be placed - in the Infrastructure or Application layer?

[fretje]

Definitely let me know if you get it working! Also would be nice if you could share the resulting code... can maybe use it as an example on the specifications docs ;-)

[snax4a]

@fretje
I managed to get this working with the following code:
SearchExtension is using NpgsqlDbFunctionsExtensions.ILike instead of DbFunctionsExtensions.Like

using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using Ardalis.Specification;
using Microsoft.EntityFrameworkCore;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Extensions;

public static class SearchExtension
{
    private static readonly MethodInfo LikeMethodInfo = typeof(NpgsqlDbFunctionsExtensions)
        .GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { typeof(DbFunctions), typeof(string), typeof(string) })
        ?? throw new TargetException("The EF.Functions.Like not found");

    private static readonly MemberExpression Functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions))
        ?? throw new TargetException("The EF.Functions not found!"));

    /// <summary>
    /// Filters <paramref name="source"/> by applying an 'SQL LIKE' operation to it.
    /// </summary>
    /// <typeparam name="T">The type being queried against.</typeparam>
    /// <param name="source">The sequence of <typeparamref name="T"/>.</param>
    /// <param name="criterias">
    /// <list type="bullet">
    ///     <item>Selector, the property to apply the SQL LIKE against.</item>
    ///     <item>SearchTerm, the value to use for the SQL LIKE.</item>
    /// </list>
    /// </param>
    /// <returns></returns>
    public static IQueryable<T> Search<T>(this IQueryable<T> source, IEnumerable<SearchExpressionInfo<T>> criterias)
    {
        Expression? expr = null;
        var parameter = Expression.Parameter(typeof(T), "x");

        foreach (var criteria in criterias)
        {
            if (string.IsNullOrEmpty(criteria.SearchTerm))
                continue;

            var propertySelector = ParameterReplacerVisitor.Replace(criteria.Selector, criteria.Selector.Parameters[0], parameter) as LambdaExpression;
            _ = propertySelector ?? throw new InvalidExpressionException();

            var likeExpression = Expression.Call(
                                    null,
                                    LikeMethodInfo,
                                    Functions,
                                    propertySelector.Body,
                                    Expression.Constant(criteria.SearchTerm));

            expr = expr == null ? (Expression)likeExpression : Expression.OrElse(expr, likeExpression);
        }

        return expr == null
            ? source
            : source.Where(Expression.Lambda<Func<T, bool>>(expr, parameter));
    }
}

using Ardalis.Specification;
using FSH.WebApi.Infrastructure.Persistence.Specification.Extensions;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Evaluators;
public sealed class MySearchEvaluator : IEvaluator
{
    private MySearchEvaluator()
    {
    }

    public static MySearchEvaluator Instance { get; } = new MySearchEvaluator();

    public bool IsCriteriaEvaluator { get; } = true;

    public IQueryable<T> GetQuery<T>(IQueryable<T> query, ISpecification<T> specification)
        where T : class
    {
        foreach (var searchCriteria in specification.SearchCriterias.GroupBy(x => x.SearchGroup))
        {
            query = query.Search(searchCriteria);
        }

        return query;
    }
}

Only this part is a bit different from the fiseni's example (ardalis/Specification#231 (comment))
Here I am passing evaluators array directly into constructor because the static field in fiseni's example was causing a runtime exception.

using Ardalis.Specification;
using Ardalis.Specification.EntityFrameworkCore;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Evaluators;

public sealed class MySpecificationEvaluator : SpecificationEvaluator
{
    public static MySpecificationEvaluator Instance { get; } = new MySpecificationEvaluator();

    private MySpecificationEvaluator()
        : base(new IEvaluator[]
        {
            WhereEvaluator.Instance,
            MySearchEvaluator.Instance,
            IncludeEvaluator.Default,
            OrderEvaluator.Instance,
            PaginationEvaluator.Instance,
            AsNoTrackingEvaluator.Instance,
            IgnoreQueryFiltersEvaluator.Instance,
            AsSplitQueryEvaluator.Instance,
            AsNoTrackingWithIdentityResolutionEvaluator.Instance
        })
    {
    }
}

At the end i am passing evaluator instance to repository constructor

public class Repository<T> : RepositoryBase<T>, IRepository<T> where T : class
{
    public Repository(AppDbContext dbContext)
        : base(dbContext, MySpecificationEvaluator.Instance)
    {
    }
}

[fretje]

Wow, super nice! And thanks for the write-up.

Maybe we could include this in the template somehow? But only use it when the database is progress?

Or otherwise simply add some comments in applicationdbrepository.cs "add MySpecificationEvaluator.Instance" to the base initializer if you want to use NpgSql's ILike functionality" or something like that... I would call the evaluator then something more appropriate... NpgSqlILikeSpecificationEvaluator? Hmm doesn't really roll of the tongue ;-)

Or we could somehow parameterize the evaluator, so we can use it for both cases (and then just call it FshSpecificationEvaluator). Then take it from configuration which "function" to use for searching? Or let it be dependent on the db provider type this way (which brings us back to point 1 full circle ;-))

Just doing some brainstorming here... any feedback welcome ;-)

[gokmensercan]

@fretje I managed to get this working with the following code: SearchExtension is using NpgsqlDbFunctionsExtensions.ILike instead of DbFunctionsExtensions.Like

using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using Ardalis.Specification;
using Microsoft.EntityFrameworkCore;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Extensions;

public static class SearchExtension
{
    private static readonly MethodInfo LikeMethodInfo = typeof(NpgsqlDbFunctionsExtensions)
        .GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { typeof(DbFunctions), typeof(string), typeof(string) })
        ?? throw new TargetException("The EF.Functions.Like not found");

    private static readonly MemberExpression Functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions))
        ?? throw new TargetException("The EF.Functions not found!"));

    /// <summary>
    /// Filters <paramref name="source"/> by applying an 'SQL LIKE' operation to it.
    /// </summary>
    /// <typeparam name="T">The type being queried against.</typeparam>
    /// <param name="source">The sequence of <typeparamref name="T"/>.</param>
    /// <param name="criterias">
    /// <list type="bullet">
    ///     <item>Selector, the property to apply the SQL LIKE against.</item>
    ///     <item>SearchTerm, the value to use for the SQL LIKE.</item>
    /// </list>
    /// </param>
    /// <returns></returns>
    public static IQueryable<T> Search<T>(this IQueryable<T> source, IEnumerable<SearchExpressionInfo<T>> criterias)
    {
        Expression? expr = null;
        var parameter = Expression.Parameter(typeof(T), "x");

        foreach (var criteria in criterias)
        {
            if (string.IsNullOrEmpty(criteria.SearchTerm))
                continue;

            var propertySelector = ParameterReplacerVisitor.Replace(criteria.Selector, criteria.Selector.Parameters[0], parameter) as LambdaExpression;
            _ = propertySelector ?? throw new InvalidExpressionException();

            var likeExpression = Expression.Call(
                                    null,
                                    LikeMethodInfo,
                                    Functions,
                                    propertySelector.Body,
                                    Expression.Constant(criteria.SearchTerm));

            expr = expr == null ? (Expression)likeExpression : Expression.OrElse(expr, likeExpression);
        }

        return expr == null
            ? source
            : source.Where(Expression.Lambda<Func<T, bool>>(expr, parameter));
    }
}

using Ardalis.Specification;
using FSH.WebApi.Infrastructure.Persistence.Specification.Extensions;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Evaluators;
public sealed class MySearchEvaluator : IEvaluator
{
    private MySearchEvaluator()
    {
    }

    public static MySearchEvaluator Instance { get; } = new MySearchEvaluator();

    public bool IsCriteriaEvaluator { get; } = true;

    public IQueryable<T> GetQuery<T>(IQueryable<T> query, ISpecification<T> specification)
        where T : class
    {
        foreach (var searchCriteria in specification.SearchCriterias.GroupBy(x => x.SearchGroup))
        {
            query = query.Search(searchCriteria);
        }

        return query;
    }
}

Only this part is a bit different from the fiseni's example (ardalis/Specification#231 (comment)) Here I am passing evaluators array directly into constructor because the static field in fiseni's example was causing a runtime exception.

using Ardalis.Specification;
using Ardalis.Specification.EntityFrameworkCore;

namespace FSH.WebApi.Infrastructure.Persistence.Specification.Evaluators;

public sealed class MySpecificationEvaluator : SpecificationEvaluator
{
    public static MySpecificationEvaluator Instance { get; } = new MySpecificationEvaluator();

    private MySpecificationEvaluator()
        : base(new IEvaluator[]
        {
            WhereEvaluator.Instance,
            MySearchEvaluator.Instance,
            IncludeEvaluator.Default,
            OrderEvaluator.Instance,
            PaginationEvaluator.Instance,
            AsNoTrackingEvaluator.Instance,
            IgnoreQueryFiltersEvaluator.Instance,
            AsSplitQueryEvaluator.Instance,
            AsNoTrackingWithIdentityResolutionEvaluator.Instance
        })
    {
    }
}

At the end i am passing evaluator instance to repository constructor

public class Repository<T> : RepositoryBase<T>, IRepository<T> where T : class
{
    public Repository(AppDbContext dbContext)
        : base(dbContext, MySpecificationEvaluator.Instance)
    {
    }
}

I implemented this in my project, but I couldn't get it to work in my project. I'm still having problems with case sensitivity in postgreSQL.

[jay-cascade]

Thanks for this, it helped me a lot.