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.

Does this query work against a real database? Since you added the `SQLite` tag, have you tried testing it by targeting a SQLite in-memory database instead of mocking? It looks like you want to produce the equivalent of `AND (field like @p1 or field like @p2 or field like @p3 or ....)` — Panagiotis Kanavos, Aug 24 '20 at 10:51
@PanagiotisKanavos 1) sql-like not SQLite and 2) For "Right" and "TruncateTime" function it works against real db but for "Like" I'm getting NotSupportedException. — Kamil Stadryniak, Aug 24 '20 at 10:56
Note that in SQL, `LIKE` is not a function but an operator. Therefore, in EF's source code, it has a special path from `DbFunctions` via the expression to the final SQL translation. In `DbFunctions` the `Like` method is not decorated by `DbFunctionAttribute`. — Gert Arnold, Aug 24 '20 at 21:00
@GertArnold right, so the only solution that came in my mind here is to CREATE a function which would be a wrapper over sql like operator (simply invoke LIKE inside that function) and then use that function in EF but I'm afraid it would impact the performance of the queries :/ — Kamil Stadryniak, Aug 25 '20 at 08:12

philpsc · Answer 1 · 2020-08-31T13:52:25.713

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.

edited Aug 31 '20 at 13:52

answered Aug 31 '20 at 13:46

philpsc

11
3

3

PATINDEX isn't LIKE. The server can use any existing indexes with prefix searches (LIKE 'abc%'`) while PATINDEX will force a full table scan. It's debatable whether DbFunctions.Like needs mocking in the first place, but replacing it with a *different* function and changing the application's behavior isn't a good idea – Panagiotis Kanavos Aug 31 '20 at 13:54

Mocking DbFunctions.Like

1 Answers1