Read a function that returns a table

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server 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.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// A table-valued function, read like a table, in three scenarios: its argument by position, sent as a typed
/// parameter; filtered, ordered and paged on top; and a named argument, which SQL Server's functions don't take
/// (ARX8001, before anything is sent). SQL Server functions can't change data, so a function read is only a read.
/// </summary>
public sealed class RealCase6_ReadAFunction : ISample
{
    public string Name => "retrieve-multiple-real-6-read-a-function";

    public string Title => "Read a function that returns a table";

    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_AnArgumentByPositionAsync(dbContext, accountId);

        await Scenario2_FilteredOrderedPagedAsync(dbContext, accountId);

        await Scenario3_ANamedArgumentIsRefusedAsync(dbContext, accountId);
    }

    /// <summary>1. fn_contacts_of_account(@p0): the argument, by position, is a typed parameter.</summary>
    private static async Task Scenario1_AnArgumentByPositionAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. An argument by position");

        var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64()));
        query.AddOrder("first_name", ArxOrderType.Ascending);

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", rows.Entities.Select(x => x["first_name"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT f.first_name
         * FROM account a CROSS APPLY dbo.fn_contacts_of_account(a.id) f
         * WHERE a.account_number = 'ACC-R671'
         * ORDER BY f.first_name;
         * -- Expect: 4 rows, Fay, Finn, Flo, Fred.
         * ===========================================================
         */
    }

    /// <summary>2. Criteria, order and paging on top of the function, as on a table.</summary>
    private static async Task Scenario2_FilteredOrderedPagedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. Filtered, ordered and paged");

        var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64()));
        query.Criteria.AddCondition("first_name", ArxConditionOperator.BeginsWith, "F");
        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 f.first_name
         * FROM account a CROSS APPLY dbo.fn_contacts_of_account(a.id) f
         * WHERE a.account_number = 'ACC-R671' AND f.first_name <> 'Flo'
         * ORDER BY f.first_name DESC
         * OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
         * -- Expect: 2 rows, Fred, Finn (3 in all).
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. The same function with its argument by name: SQL Server's functions take their arguments by position only, so
    /// ARX refuses it before anything is sent instead of guessing the order. Pass the arguments in the function's order.
    /// </summary>
    private static async Task Scenario3_ANamedArgumentIsRefusedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. A named argument is refused");

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, type_desc
         * FROM sys.objects
         * WHERE name = 'fn_contacts_of_account';
         * -- Expect: 1 row, SQL_INLINE_TABLE_VALUED_FUNCTION: the function is there; only the named call was refused.
         * ===========================================================
         */
    }
}

  > 1. An argument by position
      Fay, Finn, Flo, Fred
  > 2. Filtered, ordered and paged
      Fred, Finn (page 1 of 2, 3 in all)
  > 3. A named argument is refused
      caught ArxNotSupportedException: ARX8001 NotSupported: Function 'fn_contacts_of_account': SQL Server functions take their arguments by position only. Pass them in the function's order, without names.