DB Function Error

[jasonlaw]

Hi @rivantsov ,

I am trying the db functions with MySQL but it keep failing and I have no idea where did I do wrongly.

Here is my DB Function

    // A function can be used in LINQ expressions, it results in SQL expression added to SQL select,
    // to calculate the value on DB server side. We must provide a separate SQL template for each server type.
    // SQL template can use placeholders like {dt} matching the names of the CLR function parameters.
    [SqlExpression(DbServerType.MsSql, "ABS(DATEDIFF(DAY, {dateTime}, {otherDateTime}))")]
    [SqlExpression(DbServerType.MySql, "ABS(DATEDIFF({dateTime}, {otherDateTime}))")]
    //[SqlExpression(DbServerType.Postgres, "({dt} + {years} * INTERVAL '1 year')")]
    //[SqlExpression(DbServerType.Oracle, "ADD_MONTHS({dt}, 12 * {years})")]
    [SqlExpression(DbServerType.SQLite, "ABS(JULIANDAY({otherDateTime}) - JULIANDAY({dateTime}))")]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Style", "IDE0060:Remove unused parameter", Justification = "<Pending>")]
    public static int DbDateDiff(this DateTime dateTime, DateTime otherDateTime)
    {
        CannotCallDirectly();
        return default;
    }

Here is how I call the DB Function

        var today = DateTime.Today;
        var companies = session.EntitySet<ICompany>()
                               .Where(x => x.IsActive)
                               .Where(x => x.SubscriptionOrder == null)
                               .Where(x => x.SubscriptionPlanId != null)
                               .Where(x => x.CurrentPlan != null && x.CurrentPlan.ExpiryDate.DbDateDiff(today) <= 14)
                               .Select(x => x.Name)
                               .ToList();

Here is the error summary:

Linq to SQL translation failed, invalid expression: Function DbDateDiff not supported in queries [5/17/2023 1:54:53 AM] ErrorKind:Internal Vita.Data.Linq.Translation.LinqTranslationException: Linq to SQL translation failed, invalid expression: Function DbDateDiff not supported in queries ---> System.Exception: Function DbDateDiff not supported in queries

The full error log attached here: ErrorLog.txt

Thanks in advance!

[rivantsov]

You need to register the class that defines the function in module constructor or in EntityApp:

  mainModule.RegisterFunctions(typeof(MiscModelExtensions));

Look in MiscTests_Model, around line 114

[jasonlaw]

Thanks for the prompt response. It is working perfectly now, thank you very much!