I'm attempting to map a MySQL function in EF Core 3.1, .NET Core 3.1. More specifically, I'm trying to map a parameter of type params object[].
The example code below uses CONCAT() for illustration purposes.
After some fiddling around with HasTranslation() and a custom RelationalTypeMapping it works with literals. Meaning it produces the correct SQL query and will execute that query successfully.
As soon as I pass a column it fails to produce the correct SQL query and will throw the NotSupportedException upon execution.
There are two issues I'm trying to resolve at this stage:
Passing any column will never hit my function configuration, and by extension, my translation.
Passing any column results in various (and weird) behaviors with other parameters. This could likely be easily resolved after solving #1, but it's interesting, nonetheless. (i.e.
"asdf"vs(object)"asdf")
The ToSql() extension method used for debugging is from https://stackoverflow.com/a/67901042/1048799
The output is commented in the code.
Packages
<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;
}
}
}
