Page through a procedure's result: the procedure pages itself, ARX pages a result set

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Execute.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 RealCase3_PageThroughAProcedure().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.Execute.RealCases;

/// <summary>
/// Paging what a procedure returns, in four scenarios. Each sets <see cref="ArxProcedureExpression.PageInfo"/>, as a
/// query does, and gets back a result set with <c>CurrentPage</c>, <c>HasNextPage</c> and, when asked, the total. The
/// total is opt-in: ask for it once (page 1), as for RetrieveMultiple.
/// <list type="number">
/// <item><b>The procedure pages itself</b> (best for large data: the database produces only the page). ARX fills its page
/// arguments from <c>PageInfo</c> and reads its total from an OUT parameter.</item>
/// <item><b>A function's rows</b>: MySQL functions return one value, never rows, so paging one is refused before
/// anything is sent (ARX8001). Page the table with RetrieveMultiple instead: <c>LIMIT</c>/<c>OFFSET</c> and the total in
/// one round trip.</item>
/// <item><b>ARX pages a table the procedure returns</b> (a result set): MySQL has no cursor to move, so ARX reads past
/// the earlier rows, keeps one page and one row more, and counts the rest. Every row crosses the network.</item>
/// <item>Which to choose.</item>
/// </list>
/// </summary>
public sealed class RealCase3_PageThroughAProcedure : ISample
{
    public string Name => "execute-real-3-page-through-a-procedure";

    public string Title => "Page through a procedure's result: the procedure pages itself, ARX pages a result set";

    private const string PREFIX = "PG-E7";

    public async Task RunAsync(SampleContext dbContext)
    {
        var products = Enumerable.Range(1, 120).Select(i =>
        {
            var product = new ArxEntity("product");
            product["sku"] = $"{PREFIX}{i:D3}";
            product["name"] = $"Paged product {i}";
            product["unit_price"] = 1m + i % 10;
            return product;
        }).ToList();

        await dbContext.Service.CreateMultipleAsync(products);

        await Scenario1_TheProcedurePagesItselfAsync(dbContext);

        await Scenario2_ARXPagesAFunctionAsync(dbContext);

        await Scenario3_ARXPagesAProceduresTableAsync(dbContext);

        Scenario4_WhichToChoose();
    }

    private static string Describe(ArxEntityCollection page)
        => $"page {page.CurrentPage}: {page.Entities.Count} rows ({page.Entities.FirstOrDefault()?["sku"]} to {page.Entities.LastOrDefault()?["sku"]}), next page: {page.HasNextPage}"
           + (page.TotalRecordCount > 0 ? $", {page.TotalRecordCount} in all, {page.TotalPageCount} pages" : string.Empty);

    /// <summary>
    /// 1. <c>sp_products_page(p_prefix, p_page, p_page_size, OUT p_total)</c> pages itself. ARX fills <c>p_page</c> and
    /// <c>p_page_size</c>, and reads the total from <c>p_total</c>.
    /// </summary>
    private static async Task Scenario1_TheProcedurePagesItselfAsync(SampleContext dbContext)
    {
        Step("1. The procedure pages itself");

        foreach (var pageNumber in new[] { 1, 3 })
        {
            var procedure = new ArxProcedureExpression("sp_products_page");
            procedure.AddParameter("p_prefix", PREFIX);
            procedure.PageInfo = new ArxPagingInfo { Count = 50, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
            procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

            Show(Describe((await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0]));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), min(sku), max(sku)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku LIMIT 50 OFFSET 100) AS page_3;
         * -- Expect: 20, PG-E7101, PG-E7120 (page 1 is PG-E7001 to PG-E7050; 120 in all, 3 pages).
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. Paging a function's rows is refused on MySQL (its functions return one value). The same page comes from the
    /// table, ordered and paged by RetrieveMultiple, with the total in the same round trip.
    /// </summary>
    private static async Task Scenario2_ARXPagesAFunctionAsync(SampleContext dbContext)
    {
        Step("2. A function's rows: refused; page the table instead");

        var function = new ArxProcedureExpression("fn_products_like") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter(PREFIX);
        function.PageInfo = new ArxPagingInfo { Count = 25, PageNumber = 2, ReturnTotalRecordCount = true };

        try
        {
            await dbContext.Service.ExecuteAsync(function);
        }
        catch (ArxNotSupportedException error) when (error.Code == ArxErrorCode.NotSupported)
        {
            Show($"a function's page: {error.Code}");
        }

        var query = new ArxQueryExpression("product");
        query.ColumnSet.AddColumns("sku", "unit_price");
        query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
        query.AddOrder("unit_price", ArxOrderType.Descending);
        query.AddOrder("sku", ArxOrderType.Ascending);
        query.PageInfo = new ArxPagingInfo { Count = 25, PageNumber = 2, ReturnTotalRecordCount = true };

        var page = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(Describe(page) + $"; prices {page.Entities[0]["unit_price"]:N2} to {page.Entities[^1]["unit_price"]:N2}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku, unit_price
         * FROM product
         * WHERE sku LIKE 'PG-E7%'
         * ORDER BY unit_price DESC, sku LIMIT 25 OFFSET 25;
         * -- Expect: 25 rows, PG-E7017 (8.00) first and PG-E7015 (6.00) last (120 in all, 5 pages).
         * ===========================================================
         */
    }

    /// <summary>3. <c>sp_products</c> returns every matching product as a result set; ARX pages it.</summary>
    private static async Task Scenario3_ARXPagesAProceduresTableAsync(SampleContext dbContext)
    {
        Step("3. ARX pages a table the procedure returns");

        var procedure = new ArxProcedureExpression("sp_products");
        procedure.AddParameter("p_prefix", PREFIX);
        procedure.PageInfo = new ArxPagingInfo { Count = 40, PageNumber = 3, ReturnTotalRecordCount = true };

        Show(Describe((await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0]));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), min(sku), max(sku)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku LIMIT 40 OFFSET 80) AS page_3;
         * -- Expect: 40, PG-E7081, PG-E7120: the last page (no next page; 120 in all, 3 pages).
         * ===========================================================
         */
    }

    /// <summary>4. The choice, with the numbers of the Execute benchmarks.</summary>
    private static void Scenario4_WhichToChoose()
    {
        Step("4. Which to choose");

        Show("large data, deep pages: let the procedure page itself (1); the database produces only the page.");
        Show("rows a table or view holds: page them with RetrieveMultiple (2); the limit reaches the index.");
        Show("a procedure's result set: ARX pages it (3), but every skipped row is still produced and sent.");
        Show("the total costs a count of every row: ask for it on page 1 only, or not at all for \"load more\".");
    }
}

  > 1. The procedure pages itself
      page 1: 50 rows (PG-E7001 to PG-E7050), next page: True, 120 in all, 3 pages
      page 3: 20 rows (PG-E7101 to PG-E7120), next page: False
  > 2. A function's rows: refused; page the table instead
      a function's page: NotSupported
      page 2: 25 rows (PG-E7017 to PG-E7015), next page: True, 120 in all, 5 pages; prices 8.00 to 6.00
  > 3. ARX pages a table the procedure returns
      page 3: 40 rows (PG-E7081 to PG-E7120), next page: False, 120 in all, 3 pages
  > 4. Which to choose
      large data, deep pages: let the procedure page itself (1); the database produces only the page.
      rows a table or view holds: page them with RetrieveMultiple (2); the limit reaches the index.
      a procedure's result set: ARX pages it (3), but every skipped row is still produced and sent.
      the total costs a count of every row: ask for it on page 1 only, or not at all for "load more".