A referenced record: ARX3002, then delete in order

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Delete.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 RealCase3_ReferencedRecord().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Delete.RealCases;

/// <summary>
/// An account that contacts still point at can't be deleted, in two scenarios: the database refuses it (ARX3002, with
/// the constraint's name); delete the contacts first, by their account, then the account.
/// </summary>
public sealed class RealCase3_ReferencedRecord : ISample
{
    public string Name => "delete-real-3-referenced-record";

    public string Title => "A referenced record: ARX3002, then delete in order";

    public async Task RunAsync(SampleContext dbContext)
    {
        // One customer per scenario: the first stays (its delete is refused), the second goes.
        await Scenario1_TheDatabaseRefusesAsync(dbContext, await CreateCustomerAsync(dbContext, "ACC-D501", "Kept"));

        await Scenario2_DeleteTheContactsFirstAsync(dbContext, await CreateCustomerAsync(dbContext, "ACC-D502", "Closing"));
    }

    private static async Task<ArxRecordId> CreateCustomerAsync(SampleContext dbContext, string number, string contactsLastName)
    {
        var account = new ArxEntity("account");
        account["name"] = "Customer " + number;
        account["account_number"] = number;
        var accountId = await dbContext.Service.CreateAsync(account);

        var contacts = new[] { "Ana", "Bo" }.Select(name =>
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = name;
            contact["last_name"] = contactsLastName;
            contact["account_id"] = new ArxEntityReference("account", accountId);
            return contact;
        }).ToList();
        await dbContext.Service.CreateMultipleAsync(contacts);

        return accountId;
    }

    /// <summary>1. The account still has contacts: the foreign key refuses the delete, and names itself.</summary>
    private static async Task Scenario1_TheDatabaseRefusesAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. Delete the account: refused");

        try
        {
            await dbContext.Service.DeleteAsync("account", accountId);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.ForeignKeyViolation)
        {
            Show($"{error.Code}, constraint {error.ConstraintName}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-D501') AS accounts,
         *        (SELECT count(*) FROM contact WHERE last_name = 'Kept') AS contacts;
         * -- Expect: 1, 2: still there.
         * ===========================================================
         */
    }

    /// <summary>2. Delete the contacts by their account (a column match), then the account, in one batch scope: all or nothing.</summary>
    private static async Task Scenario2_DeleteTheContactsFirstAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. Contacts first, then the account");

        await dbContext.Service.RunBatchAsync(async scope =>
        {
            var ofTheAccount = new ArxEntity("contact");
            ofTheAccount["account_id"] = new ArxEntityReference("account", accountId);

            var contacts = await scope.DeleteAsync(ofTheAccount, new ArxDeleteOptions { Match = ArxMatch.Columns("account_id") });
            var account = await scope.DeleteAsync("account", accountId);

            Show($"{contacts} contacts and {(account ? 1 : 0)} account deleted");
        });

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-D502') AS accounts,
         *        (SELECT count(*) FROM contact WHERE last_name = 'Closing') AS contacts;
         * -- Expect: 0, 0.
         * ===========================================================
         */
    }
}

  > 1. Delete the account: refused
      ForeignKeyViolation, constraint FK__contact__account__4A5FADB1
  > 2. Contacts first, then the account
      2 contacts and 1 account deleted