Mocking DbFunctions.Like

Question

I've tried to mock DbFunctions.Like function by following most popular answer from this ticket and creating it's local implementation like this:

public static class DbFunctions
{
    [DbFunction("Edm", "TruncateTime")]
    public static DateTime? TruncateTime(DateTime? dateValue)
        => dateValue?.Date;

    [DbFunction("Edm", "Like")]
    public static bool Like(string searchString, string likeExpression)
        => Regex.IsMatch(searchString, $"^{likeExpression.Replace("%", ".*")}$");

    [DbFunction("Edm", "Right")]
    public static string Right(string stringArgument, long? length)
        => stringArgument.Substring(stringArgument.Length - ((int?) length ?? 0));
}

And using this function instead of System.Entity.DbFunctions in queries:

var query = Context.Items.AsQueryable();

if (!string.IsNullOrWhiteSpace(Number))
{
     var valuesToSearch = Number.Split(';')
                .Select(number => number.Trim())
                .AsEnumerable();

     query = query.Where(x => valuesToSearch.Any(v => DbFunctions.Like(x.Number, v)));
}

It works fine for e.g. "TruncateTime" or "Right" functions.

When I'm debugging the solution the sql versions of the functions are invoked and when I'm running unit tests the local one is invoked and tests are passing.

When it comes to "Like" I'm still getting NotSupportedException:

Is it impossible to mock DbFunctions.Like in the same manner like other system functions?

I'm using EF6 v6.4.4, Moq v4.14.1 and nUnit v3.12.0.

Answer 1

DbFunctions.Like does not have the DbFunctionAttribute, and therefore can't be mocked that way. You can, as a workaround, use SqlFunctions.PatIndex. PatIndex will return the position of the first occurrence of the given pattern in a string or 0 if it does not occur at all.

[DbFunction("SqlServer", "PATINDEX")]
public static int? Like(string searchString, string likeExpression)
    => Regex.IsMatch(searchString, $"^{likeExpression.Replace("%", ".*")}$") ? 1 : 0;

and

query = query.Where(x => valuesToSearch.Any(v => DbFunctions.Like(x.Number, v) > 0));

could work for you. It's not great in terms of readability though.