Sales summary report error, DateAdd function not available in MySql

[coolio986]

nopCommerce version: 4.70

Steps to reproduce the problem:

1. Clone NopCommerce github for 4.70 (develop as of writing this).
2. Build NopCommece in VS2022
3. Install / Setup blank MySql DB.
4. Run NopCommerce, insert DB details for initial run.
5. Log into admin page, navigate to Reports -> Sales Summary. See ajax error.

MySqlConnector.MySqlException (0x80004005): FUNCTION MyNopCommerceDatabase.DateAdd does not exist

Thrown at OrderReportService, SalesSummaryReportAsync await ssReport.ToPagedListAsync(pageIndex, pageSize);

Sql Expression from ssReport:

--  MySqlConnector MySql
DECLARE @utcOffsetInMinutes Double
SET     @utcOffsetInMinutes = -360

SELECT
    `orderItem_1`.`Key_1`,
    Count(*),
    (
        SELECT
            Sum(`o`.`OrderTotal`)
        FROM
            `Order` `o`
        WHERE
            (`orderItem_1`.`Key_1` = Cast(DateAdd(minute, @utcOffsetInMinutes, `o`.`CreatedOnUtc`) as Date) OR `orderItem_1`.`Key_1` IS NULL AND Cast(DateAdd(minute, @utcOffsetInMinutes, `o`.`CreatedOnUtc`) as Date) IS NULL) AND

Of course the expression is much longer, but this illustrates the issue.

In MySql DateAdd is not a function: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

At this time, I am not sure if this is a LinqToDB issue or some other type of translation issue. I will dive more into the issue and report any results.

[coolio986]

Additional information. Version 4.60 uses Date_Add function correctly for MySql

[coolio986]

I have identified the breaking change as commit https://github.com/nopSolutions/nopCommerce/commit/9c176a6c7ac0537d8101e41fd0f45cef1111813a

[coolio986]

It appears the mapping schema is missing during the data connection creation. I added

MySqlTools.GetDataProvider(ProviderName.MySqlConnector).MappingSchema

to

NopMappingSchema.cs at GetMappingSchema(string configurationName)

public static MappingSchema GetMappingSchema(string configurationName)
{

    if (Singleton<MappingSchema>.Instance is null)
    {
        Singleton<MappingSchema>.Instance ??= new MappingSchema(configurationName, MySqlTools.GetDataProvider(ProviderName.MySqlConnector).MappingSchema);
        Singleton<MappingSchema>.Instance.AddMetadataReader(new FluentMigratorMetadataReader());
    }

    return Singleton<MappingSchema>.Instance;
}

This is not a very elegant fix, but will suffice until I create a PR

[coolio986]

PR created with a more elegant fix https://github.com/nopSolutions/nopCommerce/pull/7134

[exileDev]

Closed #7132