Find rows by id, by key or by columns

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Update.Basics;

// 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 Basic4_HowToFindTheRows().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.MySQL.Services;
using ARX.SDK.Services;

using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Update.Basics;

/// <summary>
/// How an update finds its rows (ArxMatch), in four scenarios: by id (the default), by a declared key, by any columns
/// (which may match many rows), and strict: exactly one row, or nothing changes.
/// </summary>
public sealed class Basic4_HowToFindTheRows : ISample
{
    public string Name => "update-basics-4-how-to-find-the-rows";

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

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

        await Scenario1_ByIdAsync(service);

        await Scenario2_ByADeclaredKeyAsync(service);

        await Scenario3_ByAnyColumnsAsync(service);

        await Scenario4_StrictExactlyOneRowAsync(service);
    }

    /// <summary>1. The default: by the table's id. The other ways below are for when you don't have it.</summary>
    private static async Task Scenario1_ByIdAsync(ArxMySqlDbService service)
    {
        Step("1. By id");

        var account = new ArxEntity("account");
        account["name"] = "Lucerne Publishing";
        account["account_number"] = "ACC-U501";
        var id = await service.CreateAsync(account);

        var change = new ArxEntity("account");
        change.RecordId = id;
        change["employee_count"] = 30;
        await service.UpdateAsync(change);

        Show("ACC-U501 updated by its id");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, employee_count
         * FROM account
         * WHERE account_number = 'ACC-U501';
         * -- Expect: 1 row, employee_count 30.
         * ===========================================================
         */
    }

    /// <summary>2. By a declared key: you know the account number, not the id.</summary>
    private static async Task Scenario2_ByADeclaredKeyAsync(ArxMySqlDbService service)
    {
        Step("2. By a key");

        var account = new ArxEntity("account");
        account["name"] = "Coho Winery";
        account["account_number"] = "ACC-U502";
        await service.CreateAsync(account);

        var change = new ArxEntity("account");
        change["account_number"] = "ACC-U502";
        change["employee_count"] = 18;

        await service.UpdateAsync(change, new ArxUpdateOptions { Match = ArxMatch.Key("account_number") });

        Show("ACC-U502 updated by its account number");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, employee_count
         * FROM account
         * WHERE account_number = 'ACC-U502';
         * -- Expect: 1 row, Coho Winery, employee_count 18.
         * ===========================================================
         */
    }

    /// <summary>3. By any columns: every contact from one import is marked "do not email". Many rows change.</summary>
    private static async Task Scenario3_ByAnyColumnsAsync(ArxMySqlDbService service)
    {
        Step("3. By columns");

        var contacts = Enumerable.Range(1, 4).Select(i =>
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = "Imported";
            contact["last_name"] = $"Contact {i}";
            contact["email"] = $"import-u5-{i}@example.com";
            contact["lead_source"] = "Expo U5";
            return contact;
        }).ToList();
        await service.CreateMultipleAsync(contacts);

        var change = new ArxEntity("contact");
        change["lead_source"] = "Expo U5";
        change["do_not_email"] = true;

        int changed = await service.UpdateAsync(change, new ArxUpdateOptions { Match = ArxMatch.Columns("lead_source") });

        Show($"{changed} contacts from Expo U5 marked do not email");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE lead_source = 'Expo U5' AND do_not_email;
         * -- Expect: 4.
         * ===========================================================
         */
    }

    /// <summary>4. Strict: RequireSingleRow refuses an update that would change more than one row, and changes nothing.</summary>
    private static async Task Scenario4_StrictExactlyOneRowAsync(ArxMySqlDbService service)
    {
        Step("4. Strict");

        var change = new ArxEntity("contact");
        change["lead_source"] = "Expo U5";
        change["job_title"] = "Buyer";

        try
        {
            await service.UpdateAsync(change, new ArxUpdateOptions { Match = ArxMatch.Columns("lead_source"), RequireSingleRow = true });
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.MultipleRecordsMatched)
        {
            Show($"{error.Message}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE lead_source = 'Expo U5' AND job_title = 'Buyer';
         * -- Expect: 0: four contacts matched, so none was changed.
         * ===========================================================
         */
    }
}

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

namespace ArxSamples.MySQL;

/// <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 MySqlConnector 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.MySQL;

/// <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
      ACC-U501 updated by its id
  > 2. By a key
      ACC-U502 updated by its account number
  > 3. By columns
      4 contacts from Expo U5 marked do not email
  > 4. Strict
      ARX2003 MultipleRecordsMatched: Update on 'contact': 1 entities matched 4 rows, but RequireSingleRow needs exactly one each. Nothing was changed.