Extending DbFunctions in Entity Framework Core?

Question

With .NET Core 3.1.1 and Entity Framework Core 3.1.1, I have:

var query = from user in context.Users
            join userRole in userRoleView on user.Id equals userRole.UserId into gj
            from p in gj.DefaultIfEmpty()
            select new
                   {
                        user.Id,
                        user.UserName,
                        RoleName = p.Rolename,
                        user.CreatedUtc,
                        user.ModifiedUtc,
                  };

if (!String.IsNullOrWhiteSpace(conditions.Keyword))
{
    query = query.Where(d => EF.Functions.Like(d.UserName, "%" + conditions.Keyword + "%"));
}

This is working well, and then I would like to have EF.Functions.Contains(d.UserName, conditions.Keyword), so I wrote an extension:

public static class DbFunctionsExtensions
{
    public static bool Contains(this DbFunctions _, string matchExpression, string keyword)
    {
        return _.Like(matchExpression, "%" + keyword + "%");
    }
}

However, when running

query.Where(d => EF.Functions.Contains(d.UserName, conditions.Keyword))

I get this exception:

System.InvalidOperationException ... could not be translated.

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Source=Microsoft.EntityFrameworkCore

StackTrace: at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator() at System.Collections.Generic.LargeArrayBuilder1.AddRange(IEnumerable1 items) at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) at APS.WebPos.DAL.SearchOperations.GetActivePeopleByKeyword(String keyword) in C:\VSProjects\ApsCloudTrunk\APS.WebPos.DALCore\SearchOperations.cs:line 96 at APS.WebPos.WebApi.Controllers.SearchController.GetActivePeopleByKeyword(String keyword) in C:\VSProjects\ApsCloudTrunk\APS.WebPos.WebApiCore\Controllers\SearchController.cs:line 25 at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

Is it possible to extend DbFunctions in application with Entity Framework Core, and use it in LINQ? How?

Remarks:

String.Contains() is case sensitive in EF Core query, though it is case insensitive in EF being translated into LIKE in SQL.

Answer 1

You can use simple one -> context.Users.Where(x => (conditions.Keyword == null || x.UserName.Contains(conditions.Keyword))). If conditions.Keyword is null, it skips filter.

var query = from user in context.Users.Where(x => (conditions.Keyword == null || x.UserName.Contains(conditions.Keyword)))
                        join userRole in userRoleView
                        on user.Id equals userRole.UserId into gj
                        from p in gj.DefaultIfEmpty()
                        select new
                        {
                            user.Id,
                            user.UserName,
                            RoleName = p.Rolename,
                            user.CreatedUtc,
                            user.ModifiedUtc,
                        };

Answer 2

The parameter of the Where method is an Expression and its body is irrelevant when the query is being translated into SQL. That is why you are getting an exception.

To make it work you need to construct an Expression dynamically.

public static Expression<Func<T, bool>> Like<T>(Expression<Func<T, string>> prop, string keyword)
{
    var concatMethod = typeof(string).GetMethod(nameof(string.Concat), new[] { typeof(string), typeof(string) });
    return Expression.Lambda<Func<T, bool>>(
        Expression.Call(
            typeof(DbFunctionsExtensions),
            nameof(DbFunctionsExtensions.Like),
            null,
            Expression.Constant(EF.Functions),
            prop.Body,
            Expression.Add(
                Expression.Add(
                    Expression.Constant("%"),
                    Expression.Constant(keyword),
                    concatMethod),
                Expression.Constant("%"),
                concatMethod)),
        prop.Parameters);
}

And then use it in your query

if (!String.IsNullOrWhiteSpace(conditions.Keyword))
{
    query = query.Where(Like<User>(d => d.UserName, conditions.Keyword));
}

P.S. The way the question is titled looks close to what Scalar function mapping is, but its not applicable in the case of LIKE clause.