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

I have changed current code

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


_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 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 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:

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.


  • 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.


    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);
                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))
            var regexPattern = stringBuilder.ToString();
            var regex = new Regex(
                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%"))

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

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