Update every row a filter matches

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.UpdateMultiple.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 Basic3_ByAFilter().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.UpdateMultiple.Basics;

/// <summary>
/// Every row a condition matches, changed in one call: the entity carries only the values to set, and a filter says
/// which rows. The call returns how many rows it changed.
/// </summary>
/// <remarks>
/// <para>The filter takes the operators of a query, Or groups included, on the table's own columns; its values are parameters.</para>
/// <para>An empty filter is refused, so a missing condition never changes the whole table: pass new ArxFilterWriteOptions { AllRows = true } when every row is meant.</para>
/// <para>Needs ARX.SDK 0.1.0-preview.5 or later.</para>
/// </remarks>
public sealed class Basic3_ByAFilter : ISample
{
    public string Name => "update-multiple-basics-3-by-a-filter";

    public string Title => "Update every row a filter matches";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_EveryRowTheFilterMatchesAsync(dbContext.ConnectionString);

        await Scenario2_TheValuesCarryNoIdAsync(dbContext.ConnectionString);
    }

    private static ArxMySqlDbService Service(string connectionString) => new ArxMySqlDbService(connectionString, ArxSchemaCatalog.Create()
        .Table("account", ArxIdColumn.Generated<long>("id"))
        .Build());

    /// <summary>1. Small accounts put on hold: one UPDATE for every row the filter matches, however many there are.</summary>
    private static async Task Scenario1_EveryRowTheFilterMatchesAsync(string connectionString)
    {
        Step("1. Every row the filter matches");

        var service = Service(connectionString);
        var accounts = new List<ArxEntity>();

        for (var i = 1; i <= 5; i++)
        {
            var account = new ArxEntity("account");
            account["name"] = $"Filter update {i}";
            account["account_number"] = $"ACC-F10{i}";
            account["employee_count"] = i * 5;
            accounts.Add(account);
        }

        await service.CreateMultipleAsync(accounts);

        var change = new ArxEntity("account");
        change["status"] = "ON_HOLD";

        var filter = new ArxFilterExpression();
        filter.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-F10");
        filter.AddCondition("employee_count", ArxConditionOperator.LessThan, 15);

        int changed = await service.UpdateMultipleAsync(change, filter);

        Show($"{changed} accounts put on hold");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, employee_count, status
         * FROM account
         * WHERE account_number LIKE 'ACC-F10%'
         * ORDER BY account_number;
         * -- Expect: 5 rows; ACC-F101 and ACC-F102 (5 and 10 employees) ON_HOLD, the other three ACTIVE.
         * ===========================================================
         */
    }

    /// <summary>2. The entity carries the values only: with an id as well as a filter, ARX can't tell which rows were meant.</summary>
    private static async Task Scenario2_TheValuesCarryNoIdAsync(string connectionString)
    {
        Step("2. The values carry no id");

        var service = Service(connectionString);

        var account = new ArxEntity("account");
        account["name"] = "Filter update with an id";
        account["account_number"] = "ACC-F111";
        var id = await service.CreateAsync(account);

        var change = new ArxEntity("account");
        change.RecordId = id;
        change["status"] = "CLOSED";

        var filter = new ArxFilterExpression();
        filter.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-F111");

        try
        {
            await service.UpdateMultipleAsync(change, filter);
        }
        catch (ArxValidationException error)
        {
            Show($"Refused: {error.Code}. Nothing was changed.");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, status
         * FROM account
         * WHERE account_number = 'ACC-F111';
         * -- Expect: 1 row, status ACTIVE.
         * ===========================================================
         */
    }
}

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. Every row the filter matches
      2 accounts put on hold
  > 2. The values carry no id
      Refused: InvalidArgument. Nothing was changed.