用于自定义 MySQL 函数、文字与列的实体框架核心

Question

我正在尝试在 EF Core 3.1、.NET Core 3.1 中映射 MySQL 函数。更具体地说，我正在尝试映射 params object[] 类型的参数。

下面的示例代码使用 CONCAT() 进行说明。

经过一番摆弄 HasTranslation() 和自定义 RelationalTypeMapping 后，它可以使用文字。这意味着它会生成正确的 SQL 查询并将成功执行该查询。

一旦我传递一个列，它就无法生成正确的 SQL 查询，并且在执行时会抛出 NotSupportedException 。

在此阶段，我试图解决两个问题：

1. 传递任何列都永远不会影响我的函数配置，进而影响我的翻译。

2. 传递任何列都会导致其他参数出现各种（且奇怪的）行为。在解决#1 后，这个问题可能很容易解决，但它仍然很有趣。 （即 "asdf" 与 (object)"asdf"）

用于调试的 ToSql() 扩展方法来自 https://stackoverflow.com/a/67901042/1048799

输出在 代码。

包

<PackageReference Include="Microsoft.Extensions.Hosting" Version="6.0.1" />
<PackageReference Include="Microsoft.Extensions.Logging" Version="6.0.0" />
<PackageReference Include="MySql.EntityFrameworkCore" Version="3.1.17" />

EfTestService.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Linq;
using System.Threading.Tasks;

namespace EfTest
{
    public interface IEfTestService
    {
        Task RunAsync();
    }

    public class EfTestService : IEfTestService
    {
        private readonly ILogger<IEfTestService> logger;
        private readonly EfTestDbContext dbContext;

        public EfTestService(ILogger<IEfTestService> logger, EfTestDbContext dbContext)
        {
            this.logger = logger;
            this.dbContext = dbContext;
        }

        public async Task RunAsync()
        {
            var concat0 = dbContext.TestRecords.Select(r => dbContext.Concat(0, 1, "2", 3.0d, 4.0m, "five"));
            var concat0Sql = concat0.ToSql();
            //var concat0Result = await concat0.FirstAsync();
            Console.WriteLine(concat0Sql);

            /*
             * works as expected
             * 
                SELECT `CONCAT`(0, 1, '2', 3, 4.0, 'five')
                FROM `TestRecords` AS `t`
            */

            var concat1 = dbContext.TestRecords.Select(r => dbContext.Concat(r.TestRecordId));
            var concat1Sql = concat1.ToSql();
            //var concat1Result = await concat1.FirstAsync();
            Console.WriteLine(concat1Sql);

            /* 
             * not CONCAT
             * 
                SELECT `t`.`TestRecordId`
                FROM `TestRecords` AS `t`
            */

            var concat2 = dbContext.TestRecords.Select(r => dbContext.Concat(r.TestRecordId, 0.1)); 
            var concat2Sql = concat2.ToSql();
            //var concat2Result = await concat2.FirstAsync();
            Console.WriteLine(concat2Sql);

            /* 
             * not CONCAT, 0.1 is included
             * 
                SELECT `t`.`TestRecordId`, 0.1
                FROM `TestRecords` AS `t`
            */

            var concat3 = dbContext.TestRecords.Select(r => dbContext.Concat(r.TestRecordId, "asdf")); 
            var concat3Sql = concat3.ToSql();
            //var concat3Result = await concat3.FirstAsync();
            Console.WriteLine(concat3Sql);

            /* 
             * not CONCAT, asdf is NOT included
             * 
                SELECT `t`.`TestRecordId`
                FROM `TestRecords` AS `t`
            */

            var concat4 = dbContext.TestRecords.Select(r => dbContext.Concat(r.TestRecordId, (object)"asdf")); 
            var concat4Sql = concat4.ToSql();
            //var concat4Result = await concat4.FirstAsync();
            Console.WriteLine(concat4Sql);

            /* 
             * not CONCAT, asdf is included
             * 
                SELECT `t`.`TestRecordId`, 'asdf'
                FROM `TestRecords` AS `t`
            */


        }
    }
}

EfTestDbContext.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Linq;
using System.Linq.Expressions;

namespace EfTest
{
    public class EfTestDbContext : DbContext
    {
        public EfTestDbContext(DbContextOptions options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder
                .HasDbFunction(() => Concat(default))
                .HasTranslation(expressions =>
                { /* breakpoint, hit when passing only literals */
                    if (expressions.First() is SqlConstantExpression expression)
                    {
                        if (expression.Value is object[] @params)
                        {
                            var args = @params.Select(p => new SqlConstantExpression(Expression.Constant(p), ObjectTypeMapping.Instance));

                            return SqlFunctionExpression.Create("`CONCAT`", args, typeof(string), null);
                        }
                    }

                    throw new InvalidOperationException();
                })
                .HasParameter("vals").Metadata.TypeMapping = RelationalTypeMapping.NullMapping;
        }

        [DbFunction("CONCAT")]
        public string Concat(params object[] vals)
            => throw new NotSupportedException(); /* thown at execution when passing a column */

        public DbSet<TestRecord> TestRecords { get; set; }
    }

    public class TestRecord
    {
        public int TestRecordId { get; set; }
    }
}

ObjectTypeMapping.cs

using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Text.RegularExpressions;

namespace EfTest
{
    public class ObjectTypeMapping : RelationalTypeMapping
    {
        public static readonly ObjectTypeMapping Instance = new ObjectTypeMapping();

        public ObjectTypeMapping() : base("object", typeof(object), System.Data.DbType.Object, true) { }

        protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters)
            => throw new NotImplementedException();

        public override string GenerateSqlLiteral(object value)
        {
            if (value is string strValue)
            {
                strValue = Regex.Replace(strValue, @"([\\])", @"\$1");

                if (!strValue.Contains("'"))
                {
                    return $"'{strValue}'";
                }
                else if (!strValue.Contains('"'))
                {
                    return $"\"{strValue}\"";
                }
                else
                {
                    strValue = Regex.Replace(strValue, "(['\"])", @"\$1");

                    return $"'{strValue}'";
                }
            }

            return base.GenerateSqlLiteral(value);
        }
    }
}

Program.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System.Threading.Tasks;

namespace EfTest
{
    class Program
    {
        static async Task Main(string[] args)
        {
            IHost host = null;

            try
            {
                host = CreateHostBuilder(null).Build();

                var efTestService = ActivatorUtilities.GetServiceOrCreateInstance<IEfTestService>(host.Services);
                
                await host.StartAsync();
                await efTestService.RunAsync();
                await host.WaitForShutdownAsync();
            }
            finally
            {
                host?.Dispose();
            }
        }

        public static IHostBuilder CreateHostBuilder(string[] args)
        {
            var builder = Host.CreateDefaultBuilder()
                .ConfigureServices((context, services) =>
                {
                    services.AddDbContext<EfTestDbContext>(
                        options => options.UseMySQL(
                            context.Configuration.GetConnectionString("DefaultConnection")
                        )
                    );

                    services.AddSingleton<IEfTestService, EfTestService>();
                });

            return builder;
        }
    }
}

Answer 1

传递任何列都不会影响我的函数配置，进而影响我的翻译。

不可能将 params object[] 直接映射到任何 EF 类。

每个参数都必须明确定义类型和计数。

在映射之前展开输入的变化并转换为适当的 EF 结构/类。

是的，大量的数据和映射类。