Find a record by id, key or columns

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Retrieve.Basics;

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

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;

using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Retrieve.Basics;

/// <summary>
/// The three ways to find one record, in four scenarios: by id, by a declared key, by any
/// columns, and what happens when columns match more than one row.
/// </summary>
public sealed class Basic3_HowToFindTheRow : ISample
{
    public string Name => "retrieve-basics-3-how-to-find-the-row";

    public string Title => "Find a record by id, key or columns";

    public async Task RunAsync(SampleContext dbContext)
    {
        var service = new ArxPgDbService(dbContext.ConnectionString, ArxSchemaCatalog.Create()
            .Table("contact", ArxIdColumn.Generated<long>("id")).Key("contact", "email")
            .Build());

        var contacts = new List<ArxEntity>();

        foreach (var (first, email) in new[] { ("Ines", "ines.r401@example.com"), ("Ivo", "ivo.r401@example.com") })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Find-R401";
            contact["email"] = email;
            contacts.Add(contact);
        }

        var ids = await service.CreateMultipleAsync(contacts);

        await Scenario1_ByIdAsync(service, ids[0]);

        await Scenario2_ByADeclaredKeyAsync(service);

        await Scenario3_ByAnyColumnsAsync(service);

        await Scenario4_ColumnsThatMatchSeveralRowsAsync(service);
    }

    /// <summary>1. By id, through an entity: the same as RetrieveAsync("contact", id).</summary>
    private static async Task Scenario1_ByIdAsync(ArxPgDbService service, ArxRecordId id)
    {
        Step("1. By id");

        var match = new ArxEntity("contact") { RecordId = id };

        var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Columns = new ArxColumnSet("first_name") }) ?? throw new InvalidOperationException("The record isn't there.");

        Show($"{read["first_name"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE email = 'ines.r401@example.com';
         * -- Expect: 1 row, Ines.
         * ===========================================================
         */
    }

    /// <summary>2. By the declared key: the email is unique, so it finds one record.</summary>
    private static async Task Scenario2_ByADeclaredKeyAsync(ArxPgDbService service)
    {
        Step("2. By a declared key");

        var match = new ArxEntity("contact");
        match["email"] = "ivo.r401@example.com";

        var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Key("email"), Columns = new ArxColumnSet("first_name") }) ?? throw new InvalidOperationException("The record isn't there.");

        Show($"{read["first_name"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE email = 'ivo.r401@example.com';
         * -- Expect: 1 row, Ivo.
         * ===========================================================
         */
    }

    /// <summary>3. By any columns: the first name and last name together, no declaration needed.</summary>
    private static async Task Scenario3_ByAnyColumnsAsync(ArxPgDbService service)
    {
        Step("3. By any columns");

        var match = new ArxEntity("contact");
        match["first_name"] = "Ines";
        match["last_name"] = "Find-R401";

        var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Columns("first_name", "last_name"), Columns = new ArxColumnSet("email") }) ?? throw new InvalidOperationException("The record isn't there.");

        Show($"{read["email"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT email
         * FROM contact
         * WHERE first_name = 'Ines' AND last_name = 'Find-R401';
         * -- Expect: 1 row, ines.r401@example.com.
         * ===========================================================
         */
    }

    /// <summary>4. A Retrieve answers one record: columns that match two rows are ARX2003. Use RetrieveMultiple for many.</summary>
    private static async Task Scenario4_ColumnsThatMatchSeveralRowsAsync(ArxPgDbService service)
    {
        Step("4. Several rows");

        var match = new ArxEntity("contact");
        match["last_name"] = "Find-R401";

        try
        {
            await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Columns("last_name") });
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.MultipleRecordsMatched)
        {
            Show($"{error.Code}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE last_name = 'Find-R401';
         * -- Expect: 2: that's why one record couldn't be returned.
         * ===========================================================
         */
    }
}

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.PostgreSql;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain Npgsql read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.PostgreSql;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}

  > 1. By id
      Ines
  > 2. By a declared key
      Ivo
  > 3. By any columns
      ines.r401@example.com
  > 4. Several rows
      MultipleRecordsMatched