SqlExpression for function with atypical format

[ronnyek]

I'm trying to map date addition for this new database provider. This database has a DATE_ADD function that takes in a column or string, and then unit and amount like so:

DATE_ADD('2014-01-01', INTERVAL 1 DAY)

My understanding is that functions that are mapped, are assumed to be expecting typical function(param1, param2) type format.

To try and make this work, I've tried to make my expression take a SqlExpression for the source, and then have the second parameter be sqlExpressionFactory.Constant($"INTERVAL {amount} {unit}"). This doesn't work because the amount being passed in is a SqlExpression.

Is there a way to customize how a functions parameters are passed to the db function, or a way to effectively evaluate the amount expression so I can then pass that value in correctly?

Right now I'm getting this, when I attempt to just string interpolate the argument.

DATE_ADD("o"."OrderDate", 'INTERVAL [Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlUnaryExpression] YEAR')

[maumar]

MySql has to deal with a similar problem, you can try copying their solution:
https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/main/src/EFCore.MySql/Query/Expressions/Internal/MySqlComplexFunctionArgumentExpression.cs

With that approach, your SqlFunctionExpression would take first argument representing the date and the second fragment would be the composite argument, with Delimiter being " ", first argument SqlFragment representing "INTERVAL", sql expression representing amount and another SqlFragment representing unit.

[roji]

@ronnyek can you specify which database you're targeting?

[ronnyek]

MySql has to deal with a similar problem, you can try copying their solution: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/main/src/EFCore.MySql/Query/Expressions/Internal/MySqlComplexFunctionArgumentExpression.cs

With that approach, your SqlFunctionExpression would take first argument representing the date and the second fragment would be the composite argument, with Delimiter being " ", first argument SqlFragment representing "INTERVAL", sql expression representing amount and another SqlFragment representing unit.

I've spent a fair bit of time investigating this. It seems as is I've got it generating the sql but it looks like the SqlFragment expressions in the ComplexFunctionArgumentExpression are just not being included.

WHERE DATE_TO_STR(DATE_ADD("o"."OrderDate",  -1 ), 'yyyy') = 1997

Notice the spaces around -1, it seems like SqlFragments aren't being included as a part of the query. (the date_to_str comapring equality with 1997 is wrong, but I know how to fix that part)

I tried replacing the SqlFragment in the arguments to ComplexFunctionArgument with Constant, and it generates perfect, except wraps Interval and date unit in quotes (understandable since its thinking those are string values)

Currently this lib is targetting EF 6.0.29, and I'm looking at the pomelo driver branched 6.0 maint, which seems to reference 6.0.29. I'm unsure if this is a limitation of efcore at this version, or if there is something else going on.

As a side question (maybe reduce the amount of questions I ask here), are there any architecture documents with EFCORE? In getting this far, I was unsure when errors meant I needed a Visitor, or a Translator or whatever.

[ronnyek]

I just noticed that there was a overridden method in MysqlQuerySqlGenerator that looks like this

 protected override void CheckComposableSql(string sql)
 {
     // MySQL supports CTE (WITH) expressions within subqueries, as well as others,
     // so we allow any raw SQL to be composed over.
 }

Doing a similar override (unless I changed something else) actually execute, and return expected results. Would providing that override make the difference between SqlFragments being rendered in the sql commands or not?