How to use DbFunction translation in EF Core?

Question

I'm looking for something like EF.Functions.FreeText that was implemented in SQL Server but using the MATCH...AGAINST syntax of MySQL.

This is my current workflow:
AspNetCore 2.1.1
EntityFrameworkCore 2.1.4
Pomelo.EntityFrameworkCore.MySql 2.1.4

The problem is that MySQL uses two functions and I don't know how to interpret that with DbFunction and separate the arguments for each one. Does anyone know how to implement this?

This should be the Linq syntax:

query.Where(x => DbContext.FullText(new[] { x.Col1, x.Col2, x.Col3 }, "keywords"));

And this should be the result generated in SQL:

SELECT * FROM t WHERE MATCH(`Col1`, `Col2`, `Col3`) AGAINST('keywords');

I'm trying to follow the following examples using the HasTranslation function: https://github.com/aspnet/EntityFrameworkCore/issues/11295#issuecomment-511440395 https://github.com/aspnet/EntityFrameworkCore/issues/10241#issuecomment-342989770

Note: I know it can be solved with FromSql, but it's not what I'm looking for.

Answer 1

Your use case is very similar to mine when I needed ROW_NUMBER support in EF Core.

Example:

// gets translated to
// ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY OrderId, Count)
DbContext.OrderItems.Select(o => new {
  RowNumber = EF.Functions.RowNumber(o.ProductId, new {
    o.OrderId,
    o.Count
  })
})

Use anonymous classes instead of arrays

The first thing you have to do is to switch from using an array to an anonymous class, i.e. you change the call from

DbContext.FullText(new[] { x.Col1, x.Col2, x.Col3 }, "keywords")

to

DbContext.FullText(new { x.Col1, x.Col2, x.Col3 }, "keywords")

The sort order of the parameters will stay as it is defined by in the query, i.e new { x.Col1, x.Col2 } will be translated to Col1, Col2 and new { x.Col2, x.Col1 } to Col2, Col1.

You can even to the following: new { x.Col1, _ = x.Col1, Foo = "bar" } that is going to be translated to Col1, Col1, 'bar'.

Implement custom IMethodCallTranslator

If you need some hints then you can look through my code on Azure DevOps: RowNumber Support or if you can wait a few days then I will provide a blog post about the implementation of custom functions.

Updated (31 july, 2019)

Blog posts:

• Entity Framework Core: Custom Functions (using IMethodCallTranslator)
• Entity Framework Core: Custom Functions (using HasDbFunction)

Updated (july 27, 2019)

Thanks to the comments below I see that some clarification is required.

1) As pointed out in the comment below there is another approach. With HasDbFunction I could save me some typing like the code for registration of the translator with EF but I would still need the RowNumberExpression because the function has 2 sets of parameters (for PARTITION BY and ORDER BY) and the existing SqlFunctionExpression doesn't support that. (or did I missed something?) The reason I've chosen the approach with IMethodCallTranslator is because I want the configuration of this feature to be done during setting up of the DbContextOptionsBuilder and not in OnModelCreating. That is, it’s a personal preference of mine.

In the end the thread creator can use HasDbFunction to implement the desired feature as well. In my case the code would look something like the following:

// OnModelCreating
  var methodInfo = typeof(DemoDbContext).GetMethod(nameof(DemoRowNumber));

  modelBuilder.HasDbFunction(methodInfo)
            .HasTranslation(expressions => {
                 var partitionBy = (Expression[])((ConstantExpression)expressions.First()).Value;
                 var orderBy = (Expression[])((ConstantExpression)expressions.Skip(1).First()).Value;

                 return new RowNumberExpression(partitionBy, orderBy);
});

// the usage with this approach is identical to my current approach
.Select(c => new {
    RowNumber = DemoDbContext.DemoRowNumber(
                                  new { c.Id },
                                  new { c.RowVersion })
    })

2) An anonymous type can’t enforce the type(s) of its members, so you can get a runtime exception if the function is called with, say, integer instead of string. Still, it can be valid solution. Depending on the customer you are working for the solution may be more or less viable, in the end the decision lies with the customer. Not providing any alternatives is a possible solution as well but not a satisfying one. Especially, if the usage of SQL is not desired (because you get even less support from compiler) so the runtime exception may be a good compromise after all.

But, if the compromise is still not acceptable then we can make a research on how to add support for arrays. First approach could be the implementation of a custom IExpressionFragmentTranslator to “redirect” the handling of arrays to us.

Please note, it is just a prototype and needs more investigation/testing :-)

// to get into EF pipeline
public class DemoArrayTranslator : IExpressionFragmentTranslator
{
    public Expression Translate(Expression expression)
    {
       if (expression?.NodeType == ExpressionType.NewArrayInit)
       {
          var arrayInit = (NewArrayExpression)expression;
          return new DemoArrayInitExpression(arrayInit.Type, arrayInit.Expressions);
       }

       return null;
    }
}

// lets visitors visit the array-elements
public class DemoArrayInitExpression : Expression
{
   private readonly ReadOnlyCollection<Expression> _expressions;

   public override Type Type { get; }
   public override ExpressionType NodeType => ExpressionType.Extension;

   public DemoArrayInitExpression(Type type, 
           ReadOnlyCollection<Expression> expressions)
   {
      Type = type ?? throw new ArgumentNullException(nameof(type));
      _expressions = expressions ?? throw new ArgumentNullException(nameof(expressions));
   }

   protected override Expression Accept(ExpressionVisitor visitor)
   {
      var visitedExpression = visitor.Visit(_expressions);
      return NewArrayInit(Type.GetElementType(), visitedExpression);
   }
}

// adds our DemoArrayTranslator to the others
public class DemoRelationalCompositeExpressionFragmentTranslator 
      : RelationalCompositeExpressionFragmentTranslator
{
    public DemoRelationalCompositeExpressionFragmentTranslator(
             RelationalCompositeExpressionFragmentTranslatorDependencies dependencies)
         : base(dependencies)
      {
         AddTranslators(new[] { new DemoArrayTranslator() });
      }
   }

// Register the translator
services
  .AddDbContext<DemoDbContext>(builder => builder
       .ReplaceService<IExpressionFragmentTranslator,
                       DemoRelationalCompositeExpressionFragmentTranslator>());

For testing I introduced another overload containing Guid[] as parameter. Although, this method doesn't make sense in my use case at all :)

public static long RowNumber(this DbFunctions _, Guid[] orderBy) 

And adjusted the usage of the method

// Translates to ROW_NUMBER() OVER(ORDER BY Id)
.Select(c => new { 
                RowNumber = EF.Functions.RowNumber(new Guid[] { c.Id })
})