How tu mock EF.Functions.ILike during Unit Tests by EF's user defined function mapping

Question

I have changed current code

_context.TABLE.Where(x => x.COLUMN1.Contains("xxx") || x.COLUMN2.Contains("xxx"))

into

_context.TABLE.Where(x => EF.Functions.ILike(x.COLUMN1, "%xxx%") || EF.Functions.ILike(x.COLUMN2, "%xxx%")))

This get me what I needed - case insensitivity searches. But it broke lots of unit tests that have been already done.

Exception message: 

System.InvalidOperationException : The LINQ expression '...' could not be translated. Additional information: Translation of method 'Microsoft.EntityFrameworkCore.NpgsqlDbFunctionsExtensions.ILike' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. ... 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.

I would like to inject the atomic logic of the ILike function into a provider, so it can use it - just in case of unit tests. I have read a lot around, and it seems it can't be mocked as I imagine it to do. But reading the link from exception messages seems to explain how to do it: https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping#mapping-a-method-to-a-custom-sql

Since my EF query is translated into an SQL query with ILIKE function on database side:

SELECT ... FROM ... AS t WHERE ... AND ((t."COLUMN1" ILIKE '%xxx%' ESCAPE '') OR t."COLUMN2" ILIKE '%xxx%' ESCAPE '');

I tried to inject it into a context:

modelBuilder.HasDbFunction(typeof(MYDBCONTEXT).GetMethod(nameof(XXX), new[] { typeof(string), typeof(string) })).HasTranslation(args => args[0]);

Under XXX I have tried EF.Functions.ILike but nameof does not work for extension methods. Then I tried PostgresILikeExpression, however this is actually class, so surely some syntax here is missing. As well as translation is not ready.

I am not sure if I got proper understanding of user defined function mapping. I can be totally wrong and perhaps it can't be solved as I have imagined. However would like to assure myself. Maybe someone have eventually achieved what I try to do?

Making workarounds like mocking the whole query does not have a sense in my situation. Also switching to tests on a database itself are impossible in current state of the project and available time.

Answer 1

I've spent about a month to solve this problem. And the solution is so simple...

You just need to implement in-memory search and check a static flag to call it.

Example:

public static class UnitTestChecker
{
    public static bool IsTest { get; set; }
}

public static class PostgreSqlExtensions
{
    public static bool ILike(string input, string pattern)
    {
        if (UnitTestChecker.IsTest)
            return InMemoryExtensions.ILike(input, pattern);
        else
            return EF.Functions.ILike(input, pattern);
    }
}

public static class InMemoryExtensions
{
    private static readonly IReadOnlyDictionary<char, string> _patternMapping = new Dictionary<char, string>
    {
        { '%', ".*" },
        { '_', "?*" },
        { '\\', "" },
    };

    public static bool ILike(string input, string postgreSqlPattern)
    {
        var capacity = postgreSqlPattern.Length + postgreSqlPattern.Length / 2; // presumably
        var stringBuilder = new StringBuilder(capacity);

        foreach (var character in postgreSqlPattern)
        {
            if (_patternMapping.TryGetValue(character, out var newCharacters))
                stringBuilder.Append(newCharacters);
            else
                stringBuilder.Append(character);
        }

        var regexPattern = stringBuilder.ToString();
        var regex = new Regex(
            regexPattern,
            options: RegexOptions.IgnoreCase | RegexOptions.CultureInvariant,
            matchTimeout: TimeSpan.FromMilliseconds(50));

        return regex.IsMatch(input);
    }
} 

Then replace EF.Functions.ILike with PostgreSqlExtensions.ILike in code. For example:

var mercedesCars = await _db.Cars
    .Where(x => PostgreSqlExtensions.ILike(x.Name, "Mercedes%"))
    .ToListAsync(cancellationToken);

And finally, in a unit test or a in place with initial test setups just set the flag:

if (!UnitTestChecker.IsTest)
    UnitTestChecker.IsTest = true;