EF Core 5 - How can I use EF.Functions.Like with a custom property that maps to a JSON string?

user_3314466 0
Viewed 1044

In one of my DB models I have a property with a custom type (Dictionary<string, string>), which is automatically converted to/from JSON with a custom converter, and is stored as a text field in the database. I would like to be able to use MySQL's LIKE comparer to search for a string within this JSON field, but I am getting an exception. I don't care about the JSON's structure, I'm fine with treating it as a simple text field and searching in it that way.

Here's how I try to do it (Sku is the complex type):

var responseSet = database.Products.Where(p => EF.Functions.Like(p.Sku, "%query%"));

And this is the exception I get:

An unhandled exception has occurred while executing the request.
System.InvalidOperationException: The LINQ expression 'DbSet<ProductObject>()
    .Where(p => __Functions_0
        .Like(
            matchExpression: p.Sku, pattern: __Format_1))' could not be translated. 
            Additional information: 
              Translation of method 'Microsoft.EntityFrameworkCore.MySqlDbFunctionsExtensions.Like' 
              failed. If this method can be mapped to your custom function, 
              see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.

The link in the exception points to a long git issue with tons of cross-references, but I haven't been able to find anything useful in it.

Is there a way I could prevent this error from happening and search within the complex field?

entity-framework-core asp.net-core entity-framework mysql c#
2 Answers

This isn't a supported feature yet, but there is a hacky workaround that might work for you. Define a custom database function to explicitly cast the expression to the underlying db type.

public static string Cast(YourComplexType t) => throw new NotSupportedException();

modelBuilder.HasDbFunction(() => Cast(default))
    .HasTranslation(args =>
    {
        var a = args.First();
        return new SqlUnaryExpression(
            ExpressionType.Convert, 
            a, 
            typeof(string), 
            new StringTypeMapping(a.TypeMapping.StoreType, DbType.String));
    });

EF.Functions.Like(Cast(p.Sku), "%query%")

Unfortunately you can't define generic db functions. The arguments all have to be types the provider can store, which rules out object or an interface. So you may have to define overloaded methods for every possible type.

user_41398090

The fundamental problem with the EF Core value converters is that the LINQ query is build against client type, which then is translated behind the scenes to provider type, and there is no standard way to specify provider type conversion inside the query.

However there is a simple trick with casting (presented in some my answers to other conversion related issues like How can a JSON_VALUE be converted to a DateTime with EF Core 2.2?, Expression tree to SQL with EF Core or Comparing strings as dates using EF core 3), which works for most of the EF Core relational database providers.

In this case, you know that the CLR equivalent of the provider type is string, so you can use the following cast

p => EF.Functions.Like((string)(object)p.Sku, "%query%")

The intermediate cast to object is needed to fool C# compiler to accept the actual cast. EF Core translator is smart enough to remove it (as well as others when not needed like here).

user_52025630
Related