How do I map a MySql user defined function with ASP.NET EF Core Pomelo

Question

I'm trying to map a custom MySQL function to a method on my dbcontext as shown in this article. As suggested in the article I've stubbed out a method with the same signature of my user-defined function.

public int CalcOffset(DateTime input_date, int id)
    => throw new InvalidOperationException();

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(typeof(DbContext).GetMethod(nameof(CalcOffset), new[] { typeof(DateTime), typeof(int) }),
            b =>
                {
                    b.HasName("fn_test");
                    b.HasParameter("input_date");
                    b.HasParameter("id");
                });
        }

The MySql function I'm attempting to map to:

CREATE DEFINER=`myuser`@`localhost` FUNCTION `fn_test`(input_date datetime, project_id int) RETURNS int(11)
BEGIN

RETURN 1;
END

finally I call the function via the context

var offset = _context.CalcOffset(DateTime.Now, id);

When I run the code above I'm getting the error message thrown by the function I stubbed out. However per the article the method should be mapped the database function, not the actual method:

The body of the CLR method is not important. The method will not be invoked client-side, unless EF Core can't translate its arguments. If the arguments can be translated, EF Core only cares about the method signature

Versions: ASP.NET Core 3.1 Pomelo Entity Framework Core 3.2 MySql 5.7

Answer 1

UDFs work fine in my tests. Here is a fully working console program:

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }

    public class Context : DbContext
    {
        public virtual DbSet<IceCream> IceCreams { get; set; }

        public int IncrementInteger(int value)
            => throw new InvalidOperationException();

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So66857783";

            optionsBuilder.UseMySql(
                    connectionString,
                    options => options.CharSetBehavior(CharSetBehavior.NeverAppend)
                        .ServerVersion(ServerVersion.AutoDetect(connectionString)))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        configure => configure
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    modelBuilder.HasDbFunction(
                        typeof(Context).GetMethod(nameof(IncrementInteger)),
                        b => b.HasName("udf_increment")
                            .HasParameter("value"));

                    entity.HasData(
                        new IceCream {IceCreamId = 1, Name = "Vanilla"},
                        new IceCream {IceCreamId = 2, Name = "Chocolate"},
                        new IceCream {IceCreamId = 3, Name = "Matcha"}
                    );
                });
        }
    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            SetupDatabase(context);

            var iceCreams = context.IceCreams
                .OrderBy(i => i.IceCreamId)
                .Select(
                i => new
                {
                    IceCreamId = i.IceCreamId,
                    IcrementedValue = context.IncrementInteger(i.IceCreamId)
                })
                .ToList();
            
            Trace.Assert(iceCreams.Count == 3);
            Trace.Assert(iceCreams[0].IceCreamId == 1);
            Trace.Assert(iceCreams[0].IcrementedValue == 2);
        }

        private static void SetupDatabase(DbContext context)
        {
            context.Database.OpenConnection();

            var connection = context.Database.GetDbConnection();
            using var command = connection.CreateCommand();

            command.CommandText = @"CREATE FUNCTION `udf_increment`(value int) RETURNS int
    DETERMINISTIC
BEGIN
    RETURN value + 1;
END";
            command.ExecuteNonQuery();
        }
    }
}

It generates the following SQL query:

SELECT `i`.`IceCreamId`, `udf_increment`(`i`.`IceCreamId`) AS `IcrementedValue`
FROM `IceCreams` AS `i`
ORDER BY `i`.`IceCreamId`

However, just calling the function without an entity context, as you do in your OP, will not work because you would just execute your function body, instead of letting EF Core translating an expression tree to SQL:

// This does not work, because Context.IncrementInteger(int) is being directly called
// here, instead of being part of an expression tree, that is then translated to SQL
// by EF Core.
var answer = context.IncrementInteger(41);
Trace.Assert(answer == 42);

Of course you can always execute some good old SQL yourself, if you need to:

// Executing the UDF using god old SQL.
// Use parameters for input values in the real world.
context.Database.OpenConnection();
using var command = context.Database.GetDbConnection().CreateCommand();
command.CommandText = @"SELECT `udf_increment`(41)";
var answerToEverything = (int)command.ExecuteScalar();

Trace.Assert(answerToEverything == 42);