Rows from a function: refused on MySQL, and what to read instead

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL connection string.");

await new RealCase6_ReadAFunction().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// Functions as row sources, on MySQL, in three scenarios: a function as a query's row source is refused before anything
/// is sent, since MySQL functions return one value, never rows (ARX8001); the same rows read from the table, filtered,
/// ordered and paged; and the same rows from a procedure, called through Execute. On PostgreSQL and SQL Server this
/// sample reads a table-valued function.
/// </summary>
public sealed class RealCase6_ReadAFunction : ISample
{
    public string Name => "retrieve-multiple-real-6-read-a-function";

    public string Title => "Rows from a function: refused on MySQL, and what to read instead";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Function Foods";
        account["account_number"] = "ACC-R671";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var first in new[] { "Fay", "Finn", "Flo", "Fred" })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Func-R671";
            contact["email"] = $"{first.ToLowerInvariant()}[email protected]";
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_AFunctionAsARowSourceIsRefusedAsync(dbContext, accountId);

        await Scenario2_TheSameRowsFromTheTableAsync(dbContext, accountId);

        await Scenario3_TheSameRowsFromAProcedureAsync(dbContext);
    }

    /// <summary>1. A function as the row source: MySQL has none that return rows, so ARX refuses it before anything is sent.</summary>
    private static async Task Scenario1_AFunctionAsARowSourceIsRefusedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. A function as a row source is refused");

        try
        {
            await dbContext.Service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64())));
        }
        catch (ArxNotSupportedException error) when (error.Code == ArxErrorCode.NotSupported)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT routine_name, routine_type, data_type
         * FROM information_schema.routines
         * WHERE routine_schema = DATABASE() AND routine_type = 'FUNCTION'
         * ORDER BY routine_name;
         * -- Expect: fn_contact_count and fn_order_count, each returning one bigint: MySQL's functions return one value.
         * ===========================================================
         */
    }

    /// <summary>2. The rows the function would have returned, read from the table: criteria, order and paging as usual.</summary>
    private static async Task Scenario2_TheSameRowsFromTheTableAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. The same rows from the table: filtered, ordered and paged");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "email");
        query.Criteria.AddCondition("account_id", ArxConditionOperator.Equal, accountId.AsInt64());
        query.Criteria.AddCondition("first_name", ArxConditionOperator.NotEqual, "Flo");
        query.AddOrder("first_name", ArxOrderType.Descending);
        query.PageInfo = new ArxPagingInfo { Count = 2, ReturnTotalRecordCount = true };

        var page = await dbContext.Service.RetrieveMultipleAsync(query);
        Show($"{string.Join(", ", page.Entities.Select(x => x["first_name"]))} (page 1 of {page.TotalPageCount}, {page.TotalRecordCount} in all)");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         * WHERE a.account_number = 'ACC-R671' AND c.first_name <> 'Flo'
         * ORDER BY c.first_name DESC
         * LIMIT 2;
         * -- Expect: 2 rows, Fred, Finn (3 in all).
         * ===========================================================
         */
    }

    /// <summary>3. Rows a routine computes come from a procedure: <c>sp_contacts_named</c>, called through Execute.</summary>
    private static async Task Scenario3_TheSameRowsFromAProcedureAsync(SampleContext dbContext)
    {
        Step("3. The same rows from a procedure, through Execute");

        var procedure = new ArxProcedureExpression("sp_contacts_named");
        procedure.AddParameter("p_last_name", "Func-R671");

        var rows = (await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0];
        Show(string.Join(", ", rows.Entities.Select(x => x["first_name"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * CALL sp_contacts_named('Func-R671');
         * -- Expect: 4 rows, Fay, Finn, Flo, Fred.
         * ===========================================================
         */
    }
}

  > 1. A function as a row source is refused
      caught ArxNotSupportedException: ARX8001 NotSupported: Function 'fn_contacts_of_account': MySQL functions return one value, not rows, so a function can't be a query's row source. Read a table or a view, call a procedure that returns its rows (ExecuteAsync, result sets), or call the function for its value (ExecuteAsync with ArxProcedureType.Function).
  > 2. The same rows from the table: filtered, ordered and paged
      Fred, Finn (page 1 of 2, 3 in all)
  > 3. The same rows from a procedure, through Execute
      Fay, Finn, Flo, Fred