Delete by key, by compound key, by columns

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Delete.Basics;

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;

using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Delete.Basics;

/// <summary>
/// How a delete finds its rows when you don't have the id (ArxMatch), in four scenarios: by a declared key, by a compound
/// key on a table with no id, by columns on a table with no id and no key, and strict: exactly one row or nothing.
/// </summary>
public sealed class Basic3_HowToFindTheRows : ISample
{
    public string Name => "delete-basics-3-how-to-find-the-rows";

    public string Title => "Delete by key, by compound key, by columns";

    public async Task RunAsync(SampleContext dbContext)
    {
        var service = new ArxMsSqlDbService(dbContext.ConnectionString, ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id")).Key("account", "account_number")
            .Table("country", ArxIdColumn.Supplied<string>("code"))
            .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
            .Table("activity_log", ArxIdColumn.NoIdColumn)
            .Build());

        await Scenario1_ByADeclaredKeyAsync(service);

        await Scenario2_ByACompoundKeyAsync(service);

        await Scenario3_ByColumns_NoIdNoKeyAsync(service);

        await Scenario4_StrictExactlyOneRowAsync(service);
    }

    /// <summary>1. By a declared key: the account number.</summary>
    private static async Task Scenario1_ByADeclaredKeyAsync(ArxMsSqlDbService service)
    {
        Step("1. By a key");

        var account = new ArxEntity("account");
        account["name"] = "Found by number";
        account["account_number"] = "ACC-D401";
        await service.CreateAsync(account);

        var byNumber = new ArxEntity("account");
        byNumber["account_number"] = "ACC-D401";

        int deleted = await service.DeleteAsync(byNumber, new ArxDeleteOptions { Match = ArxMatch.Key("account_number") });

        Show($"{deleted} deleted");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number = 'ACC-D401';
         * -- Expect: 0.
         * ===========================================================
         */
    }

    /// <summary>2. A price has no id: region and SKU together find it.</summary>
    private static async Task Scenario2_ByACompoundKeyAsync(ArxMsSqlDbService service)
    {
        Step("2. By a compound key");

        var poland = new ArxEntity("country");
        poland["code"] = "PL";
        poland["name"] = "Poland";
        await service.CreateAsync(poland);

        foreach (var sku in new[] { "SKU-D401", "SKU-D402" })
        {
            var price = new ArxEntity("price_list");
            price["region"] = "PL";
            price["sku"] = sku;
            price["unit_price"] = 3.00m;
            price["valid_from"] = new DateOnly(2026, 1, 1);
            await service.CreateAsync(price);
        }

        var one = new ArxEntity("price_list");
        one["region"] = "PL";
        one["sku"] = "SKU-D401";

        int deleted = await service.DeleteAsync(one, new ArxDeleteOptions { Match = ArxMatch.Key("region", "sku") });

        Show($"{deleted} deleted");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku
         * FROM price_list
         * WHERE region = 'PL';
         * -- Expect: 1 row, SKU-D402.
         * ===========================================================
         */
    }

    /// <summary>3. The activity log has no id and no key: delete by any columns. Every matching row goes.</summary>
    private static async Task Scenario3_ByColumns_NoIdNoKeyAsync(ArxMsSqlDbService service)
    {
        Step("3. By columns");

        foreach (var action in new[] { "test login", "test login", "test logout" })
        {
            var entry = new ArxEntity("activity_log");
            entry["action"] = action;
            await service.CreateAsync(entry);
        }

        var logins = new ArxEntity("activity_log");
        logins["action"] = "test login";

        int deleted = await service.DeleteAsync(logins, new ArxDeleteOptions { Match = ArxMatch.Columns("action") });

        Show($"{deleted} log entries deleted");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT action, count(*)
         * FROM activity_log
         * WHERE action LIKE 'test log%'
         * GROUP BY action;
         * -- Expect: 1 row, test logout, 1.
         * ===========================================================
         */
    }

    /// <summary>4. Strict: RequireSingleRow refuses a delete that would remove more than one row, and deletes nothing.</summary>
    private static async Task Scenario4_StrictExactlyOneRowAsync(ArxMsSqlDbService service)
    {
        Step("4. Strict");

        foreach (var action in new[] { "strict delete", "strict delete" })
        {
            var entry = new ArxEntity("activity_log");
            entry["action"] = action;
            await service.CreateAsync(entry);
        }

        var entries = new ArxEntity("activity_log");
        entries["action"] = "strict delete";

        try
        {
            await service.DeleteAsync(entries, new ArxDeleteOptions { Match = ArxMatch.Columns("action"), RequireSingleRow = true });
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.MultipleRecordsMatched)
        {
            Show($"{error.Message}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM activity_log
         * WHERE action = 'strict delete';
         * -- Expect: 2: nothing was deleted.
         * ===========================================================
         */
    }
}

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

namespace ArxSamples.MSSQL;

/// <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 SqlClient 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.MSSQL;

/// <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 a key
      1 deleted
  > 2. By a compound key
      1 deleted
  > 3. By columns
      2 log entries deleted
  > 4. Strict
      ARX2003 MultipleRecordsMatched: Delete on 'activity_log': 1 entities matched 2 rows, but RequireSingleRow needs exactly one each. Nothing was deleted.