New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
TimeSpan.TotalHours could not be translated #1910
Comments
We currently still map the CLR type There is no good type in MySQL to represent a Currently, we only translate So you could use If you want to add a custom implementation/mapping of |
@lauxjpn Interesting you are planning on changing the /cc @roji |
@ajcvickers Pomelo has existing facilities in place to let users customize some type mappings (e.g. MySqlDefaultDataTypeMappings), so users can do something like We would then add When it comes to scaffolding, we have existing pseudo connection string parameters that only work at design time to customize the scaffolding process (we implemented theses before there was official support to pass parameters to providers at design time). So we could add another one for letting users switch between scaffolding For migrations, users will suddenly be confronted with operations that change the column type out of the blue, so we should probably output a warning about it and how to keep the old mapping. We should/could also emit a data update operation to migrate the existing data over from I think that's about the gist of it. What do you think? |
That would be great for the mean time. I'd also love to contribute an implementation for this function or the translation of TimeSpan.Total* to the Pomelo code itself so others can also use this.
I had this exact problem in the first place when migrating from .NET 6 to .NET 8. The scope was a legacy application with didn't use EF Core migrations. When upgrading to .NET 8 I switched to using migrations and the first step I took was to re-scaffold the entire database, so i have the correct current state of it when starting with EF migrations. But then I had to migrate all my repositories to use TimeOnly and DateOnly and convert them back to TimeSpan / DateTime in the business layer. If there had been a switch to change the scaffolding process, it would had saved me many hours refactoring my app. |
Feel free to push a PR for it.
That doesn't currently make too much sense because of:
I outlined the process of properly implementing this above in #1910 (comment), that involves quite a bit and which we are likely to implement for the
You should be able to scaffold the database (which would then generate This should work fine for most scaffolding scenarios. However, if you want to control or override the scaffolding process in any way possible, you can do so as well. I posted some sample code in #1584 (comment) to demonstrate this. (The code is a bit older. Nowadays, the
Here is a sample console app, that demonstrates how to translate a custom Program.csusing System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Query.Internal;
namespace IssueConsoleTemplate;
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public TimeSpan MaxAllowedDurationWithoutRefrigeration { get; set; }
public DateTime? TakenOutOfFreezer { get; set; }
}
public static class CustomMySqlDbFunctionsExtensions
{
public static int TimeToSec(this DbFunctions _, TimeSpan time)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(TimeToSec)));
}
public class CustomMethodCallTranslator : IMethodCallTranslator
{
private static readonly MethodInfo TimeToSecMethodInfo = typeof(CustomMySqlDbFunctionsExtensions)
.GetRuntimeMethod(
nameof(CustomMySqlDbFunctionsExtensions.TimeToSec),
[typeof(DbFunctions), typeof(TimeSpan)]);
private readonly MySqlSqlExpressionFactory _sqlExpressionFactory;
private readonly IRelationalTypeMappingSource _typeMappingSource;
public CustomMethodCallTranslator(
MySqlSqlExpressionFactory sqlExpressionFactory,
IRelationalTypeMappingSource typeMappingSource)
{
_sqlExpressionFactory = sqlExpressionFactory;
_typeMappingSource = typeMappingSource;
}
public SqlExpression Translate(
SqlExpression instance,
MethodInfo method,
IReadOnlyList<SqlExpression> arguments,
IDiagnosticsLogger<DbLoggerCategory.Query> logger)
{
return method == TimeToSecMethodInfo
? _sqlExpressionFactory.NullableFunction(
"TIME_TO_SEC",
new[] { arguments[1] },
typeof(int),
_typeMappingSource.FindMapping(typeof(int)))
: null;
}
}
public class CustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
public CustomMethodCallTranslatorPlugin(
IRelationalTypeMappingSource typeMappingSource,
ISqlExpressionFactory sqlExpressionFactory)
{
var mySqlSqlExpressionFactory = (MySqlSqlExpressionFactory)sqlExpressionFactory;
Translators = new IMethodCallTranslator[]
{
new CustomMethodCallTranslator(mySqlSqlExpressionFactory, typeMappingSource),
};
}
public IEnumerable<IMethodCallTranslator> Translators { get; }
}
public class CustomEvaluatableExpressionFilterPlugin : IEvaluatableExpressionFilterPlugin
{
public bool IsEvaluatableExpression(Expression expression)
=> expression is not MethodCallExpression methodCallExpression ||
methodCallExpression.Method.DeclaringType != typeof(CustomMySqlDbFunctionsExtensions);
}
public class CustomDbContextOptionsExtension : IDbContextOptionsExtension
{
private ExtensionInfo _info;
public virtual DbContextOptionsExtensionInfo Info
=> _info ??= new ExtensionInfo(this);
public void ApplyServices(IServiceCollection services)
{
new EntityFrameworkRelationalServicesBuilder(services) // <-- inject services
.TryAdd<IMethodCallTranslatorPlugin, CustomMethodCallTranslatorPlugin>()
.TryAdd<IEvaluatableExpressionFilterPlugin, CustomEvaluatableExpressionFilterPlugin>();
}
public void Validate(IDbContextOptions options)
{
}
private class ExtensionInfo : DbContextOptionsExtensionInfo
{
public ExtensionInfo(IDbContextOptionsExtension extension)
: base(extension)
{
}
public override int GetServiceProviderHashCode()
=> 0;
public override bool ShouldUseSameServiceProvider(DbContextOptionsExtensionInfo other)
=> other is ExtensionInfo otherInfo;
public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
{
}
public override bool IsDatabaseProvider
=> false;
public override string LogFragment
=> string.Empty;
}
}
public static class CustomDbContextOptionsBuilderExtensions
{
public static DbContextOptionsBuilder ApplyCustomServices(this DbContextOptionsBuilder optionsBuilder)
{
((IDbContextOptionsBuilderInfrastructure)optionsBuilder)
.AddOrUpdateExtension(
optionsBuilder.Options.FindExtension<CustomDbContextOptionsExtension>() ??
new CustomDbContextOptionsExtension());
return optionsBuilder;
}
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;Database=Issue1910";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder
.UseMySql(connectionString, serverVersion)
.ApplyCustomServices() // <-- call custom extension method to inject services
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.HasData(
new IceCream
{
IceCreamId = 1,
Name = "Vanilla",
MaxAllowedDurationWithoutRefrigeration = TimeSpan.FromMinutes(10),
TakenOutOfFreezer = new DateTime(2024, 1, 1, 12, 0, 0),
},
new IceCream
{
IceCreamId = 2,
Name = "Chocolate",
MaxAllowedDurationWithoutRefrigeration = TimeSpan.FromMinutes(15),
TakenOutOfFreezer = new DateTime(2024, 1, 1, 12, 0, 0),
},
new IceCream
{
IceCreamId = 3,
Name = "Matcha",
MaxAllowedDurationWithoutRefrigeration = TimeSpan.FromMinutes(10),
TakenOutOfFreezer = null,
});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var now = new DateTime(2024, 1, 1, 12, 13, 0);
var timeOfDayInSeconds = (now.TimeOfDay.Hours * 60 + now.TimeOfDay.Minutes) * 60 + now.TimeOfDay.Seconds;
var nonRefreezableIceCreams = context.IceCreams
.Where(c => c.TakenOutOfFreezer != null)
.Select(i =>
new
{
i.IceCreamId,
i.Name,
i.MaxAllowedDurationWithoutRefrigeration,
OutOfFreezerInSeconds1 = EF.Functions.DateDiffSecond(i.TakenOutOfFreezer, now),
OutOfFreezerInSeconds2 = timeOfDayInSeconds - ((i.TakenOutOfFreezer.Value.TimeOfDay.Hours * 60 + i.TakenOutOfFreezer.Value.TimeOfDay.Minutes) * 60 + i.TakenOutOfFreezer.Value.TimeOfDay.Seconds),
OutOfFreezerInSeconds3 = EF.Functions.TimeToSec(now.TimeOfDay) - EF.Functions.TimeToSec(i.TakenOutOfFreezer.Value.TimeOfDay),
})
.Where(t => t.OutOfFreezerInSeconds1 == t.OutOfFreezerInSeconds2 &&
t.OutOfFreezerInSeconds1 == t.OutOfFreezerInSeconds3 &&
t.OutOfFreezerInSeconds1 > (t.MaxAllowedDurationWithoutRefrigeration.Hours * 60 + t.MaxAllowedDurationWithoutRefrigeration.Minutes) * 60 + t.MaxAllowedDurationWithoutRefrigeration.Seconds)
.ToList();
Trace.Assert(nonRefreezableIceCreams.Count == 1);
Trace.Assert(nonRefreezableIceCreams[0].Name == "Vanilla");
}
} Output (SQL)warn: 25.04.2024 19:15:02.784 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure)
Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 25.04.2024 19:15:03.156 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP DATABASE `Issue1910`;
info: 25.04.2024 19:15:03.386 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE `Issue1910`;
info: 25.04.2024 19:15:03.584 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER DATABASE CHARACTER SET utf8mb4;
info: 25.04.2024 19:15:03.614 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `IceCreams` (
`IceCreamId` int NOT NULL AUTO_INCREMENT,
`Name` longtext CHARACTER SET utf8mb4 NULL,
`MaxAllowedDurationWithoutRefrigeration` time(6) NOT NULL,
`TakenOutOfFreezer` datetime(6) NULL,
CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
) CHARACTER SET=utf8mb4;
info: 25.04.2024 19:15:03.622 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`IceCreamId`, `MaxAllowedDurationWithoutRefrigeration`, `Name`, `TakenOutOfFreezer`)
VALUES (1, TIME '00:10:00', 'Vanilla', TIMESTAMP '2024-01-01 12:00:00'),
(2, TIME '00:15:00', 'Chocolate', TIMESTAMP '2024-01-01 12:00:00'),
(3, TIME '00:10:00', 'Matcha', NULL);
info: 25.04.2024 19:15:03.955 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (26ms) [Parameters=[@__now_1='2024-01-01T12:13:00.0000000' (Nullable = true) (DbType = DateTime), @__timeOfDayInSeconds_2='43980', @__now_TimeOfDay_3='12:13:00'], CommandType='Text', CommandTimeout='30']
SELECT `i`.`IceCreamId`, `i`.`Name`, `i`.`MaxAllowedDurationWithoutRefrigeration`, TIMESTAMPDIFF(SECOND, `i`.`TakenOutOfFreezer`, @__now_1) AS `OutOfFreezerInSeconds1`, @__timeOfDayInSeconds_2 - ((((EXTRACT(hour FROM CAST(`
i`.`TakenOutOfFreezer` AS time(6))) * 60) + EXTRACT(minute FROM CAST(`i`.`TakenOutOfFreezer` AS time(6)))) * 60) + EXTRACT(second FROM CAST(`i`.`TakenOutOfFreezer` AS time(6)))) AS `OutOfFreezerInSeconds2`, TIME_TO_SEC(@__now_Tim
eOfDay_3) - TIME_TO_SEC(CAST(`i`.`TakenOutOfFreezer` AS time(6))) AS `OutOfFreezerInSeconds3`
FROM `IceCreams` AS `i`
WHERE `i`.`TakenOutOfFreezer` IS NOT NULL AND (((TIMESTAMPDIFF(SECOND, `i`.`TakenOutOfFreezer`, @__now_1) = (@__timeOfDayInSeconds_2 - ((((EXTRACT(hour FROM CAST(`i`.`TakenOutOfFreezer` AS time(6))) * 60) + EXTRACT(minute F
ROM CAST(`i`.`TakenOutOfFreezer` AS time(6)))) * 60) + EXTRACT(second FROM CAST(`i`.`TakenOutOfFreezer` AS time(6)))))) AND (TIMESTAMPDIFF(SECOND, `i`.`TakenOutOfFreezer`, @__now_1) = (TIME_TO_SEC(@__now_TimeOfDay_3) - TIME_TO_SE
C(CAST(`i`.`TakenOutOfFreezer` AS time(6)))))) AND (TIMESTAMPDIFF(SECOND, `i`.`TakenOutOfFreezer`, @__now_1) > ((((EXTRACT(hour FROM `i`.`MaxAllowedDurationWithoutRefrigeration`) * 60) + EXTRACT(minute FROM `i`.`MaxAllowedDuratio
nWithoutRefrigeration`)) * 60) + EXTRACT(second FROM `i`.`MaxAllowedDurationWithoutRefrigeration`)))) |
Sounds like a plan! Let's see how it goes. |
@lauxjpn Thank you so much for your work. I finally found time to wrap my head around your code. It works flawless in my setup. Your code sparked the idea to look deeper into how Pomelo handles the SQL translation and I loved what I saw and couldn't stop adding a translation for the TimeSpan.Total* properties. (#1917) I added another virtual mapping for TimeToHour to avoid additional arithmetics in the queries: public class CustomTimeToHourMethodCallTranslator : IMethodCallTranslator
{
private static readonly MethodInfo TimeToHourMethodInfo = typeof(CustomMySqlDbFunctionsExtensions)
.GetRuntimeMethod(
nameof(CustomMySqlDbFunctionsExtensions.TimeToHour),
[typeof(DbFunctions), typeof(TimeSpan)]);
private readonly MySqlSqlExpressionFactory sqlExpressionFactory;
private readonly IRelationalTypeMappingSource typeMappingSource;
public CustomTimeToHourMethodCallTranslator(
MySqlSqlExpressionFactory sqlExpressionFactory,
IRelationalTypeMappingSource typeMappingSource)
{
this.sqlExpressionFactory = sqlExpressionFactory;
this.typeMappingSource = typeMappingSource;
}
public SqlExpression Translate(
SqlExpression instance,
MethodInfo method,
IReadOnlyList<SqlExpression> arguments,
IDiagnosticsLogger<DbLoggerCategory.Query> logger)
{
if (method == TimeToHourMethodInfo)
{
var timeToSecExpression = this.sqlExpressionFactory.NullableFunction(
name: "TIME_TO_SEC",
arguments: new[] { arguments[1] },
returnType: typeof(int),
typeMapping: this.typeMappingSource.FindMapping(typeof(int))
);
var divideBy3600Expression = this.sqlExpressionFactory.Divide(timeToSecExpression, this.sqlExpressionFactory.Constant(3600d, this.typeMappingSource.FindMapping(typeof(double))));
return divideBy3600Expression;
}
else
{
return null;
}
}
}
public static class CustomMySqlDbFunctionsExtensions
{
public static int TimeToSec(this DbFunctions _, TimeSpan time)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(TimeToSec)));
public static int TimeToHour(this DbFunctions _, TimeSpan time)
=> throw new InvalidOperationException(CoreStrings.FunctionOnClient(nameof(TimeToHour)));
}
public class CustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
public IEnumerable<IMethodCallTranslator> Translators { get; }
public CustomMethodCallTranslatorPlugin(
IRelationalTypeMappingSource typeMappingSource,
ISqlExpressionFactory sqlExpressionFactory)
{
var mySqlSqlExpressionFactory = (MySqlSqlExpressionFactory)sqlExpressionFactory;
this.Translators = new IMethodCallTranslator[]
{
new CustomTimeToSecMethodCallTranslator(mySqlSqlExpressionFactory, typeMappingSource),
new CustomTimeToHourMethodCallTranslator(mySqlSqlExpressionFactory, typeMappingSource),
};
}
} |
Steps to reproduce
context.Test.Sum(x => x.Time.TotalHours)
neither works:
context.Test.Sum(x => EF.Functions.DateDiffMinute(x.Time.TotalHours, TimeSpan.Zero) / 60d)
The issue
Further technical details
Operating system: Windows / Linux
Pomelo.EntityFrameworkCore.MySql version: 8.0.0
Microsoft.AspNetCore.App version: 8.0.200
So, it's dear to me that
TotalHours
can't be mapped.Is there an other way to sum up all time valued-rows?
Can I add the function
TIME_TO_SEC
to the EF Functions to call it an then divide by 3600 to get the hours?Any help will be appreciated.
Thank you in advance.
The text was updated successfully, but these errors were encountered: