Question 20298 in Mysql Question posted in
The official MySQL documentation can be found here.

How to map MySql built-in function TIMESTAMPDIFF in EF Core?

I used this, but it put the unit in a quote like ‘MONTH’; how can I force him not to generate it without a quote?

    [DbFunction("TIMESTAMPDIFF", IsBuiltIn = true)]
    public static int DateDiffMinute([NotParameterized] string unit, DateTime start, DateTime end)
    {
        throw new Exception("Should not be called!");
    }
Tags:

2

Answers


  1. 0

    You have to define translation for this function. Also unit parameter should be removed.

    Assuming that your function is defined in static class MySQLFunctions

    public static class MySQLFunctions
{
    public static int DateDiffMinute(DateTime start, DateTime end)
    {
        throw new InvalidOperationException("Should not be called!");
    }
}

    In OnModelCreating define translation of this function:

    modelBuilder.HasDbFunction(() => MySQLFunctions.DateDiffMinute(default, default))
    .IsBuiltIn()
    .HasTranslation(parameters =>
        new SqlFunctionExpression("TIMESTAMPDIFF", parameters.Prepend(new SqlFragmentExpression("MINUTE")),
            true, new[]{false, true, true}, typeof(int), null));
    Login or Signup to reply.
  2. 0

    That’s already mapped in the most popular (and truly open source) EF Core provider, Pomelo.EntityFrameworkCore.MySql. You can use EF.Functions.DateDiffMonth(startDate, endDate) directly.

    The Pomelo provider uses the also open source MySqlConnector provider instead of Oracle’s Connector/.NET. The MySqlConnector and Pomelo.EntityFrameworkCore.MySql packages solve many of the issues found in Oracle’s drivers and add many features that don’t exist in the Oracle drivers and providers. They’re updated more frequently than Oracle’s own packages and by now are both more popular than Oracle’s packages.

    If you check NuGet statistics you’ll see :

    • MySqlConnector 61M downloads
    • MySQL.Data (Oracle) 57M

    The EF Core provider situation is more dramatic:

    • Pomelo.EntityFrameworkCore.MySql 37M
    • MySql.EntityFrameworkCore (Oracle) 2M

    Avoiding Conflicts

    If different providers are used in the same project, the compiler may not know which method to use and generate CS0121 The call is ambiguous between the following methods or properties:. This can be resolved in several ways and the IDE probably already proposes one of them as a fix :

    • The method can be called normally
    • A using static import can be used to "separate" the names

    In the following example a static import is used to allow calling the MySQL function directly, as if it was a normal function:

    using static Microsoft.EntityFrameworkCore.MySqlDbFunctionsExtensions;

...

var totalDuration=dbContext.Events.Sum(e=>DateDiffMinute(e.Start,e.End));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search