Natural ids, uuid ids and a table with a compound key

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.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 RealCase7_NaturalIdsAndCompoundKeys().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.Create.RealCases;

/// <summary>
/// Ids that aren't a generated number, and a table with no id column, in five scenarios: countries by their
/// ISO code, devices with uuid ids, a regional price list identified by region + SKU, and the two mistakes SQL Server
/// catches: the same region and SKU again, and one part of the key missing. All declared in SampleCatalog.cs.
/// </summary>
/// <remarks>
/// A supplied id is an attribute like any other: <c>country["code"] = "ES"</c>. Create returns the id as an
/// <see cref="ArxRecordId"/> (also on <c>entity.RecordId</c>). A table declared NoIdColumn returns
/// <see cref="ArxRecordId.None"/>; its declared key (region, sku) is what Update and Delete will find a price by.
/// </remarks>
public sealed class RealCase7_NaturalIdsAndCompoundKeys : ISample
{
    public string Name => "create-real-7-natural-ids-and-compound-keys";

    public string Title => "Natural ids, uuid ids and a table with a compound key";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_CountriesByTheirCodeAsync(dbContext);

        await Scenario2_DevicesWithUuidIdsAsync(dbContext);

        await Scenario3_APriceListWithNoIdColumnAsync(dbContext);

        await Scenario4_TheSameRegionAndSkuAgainAsync(dbContext);

        await Scenario5_OnePartOfTheKeyMissingAsync(dbContext);
    }

    /// <summary>1. Countries by their ISO code: a text id the caller supplies.</summary>
    private static async Task Scenario1_CountriesByTheirCodeAsync(SampleContext dbContext)
    {
        Step("1. Countries by their ISO code (a supplied text id)");

        var spain = new ArxEntity("country");
        spain["code"] = "ES";
        spain["name"] = "Spain";

        var brazil = new ArxEntity("country");
        brazil["code"] = "BR";
        brazil["name"] = "Brazil";

        var philippines = new ArxEntity("country");
        philippines["code"] = "PH";
        philippines["name"] = "Philippines";

        var countryIds = await dbContext.Service.CreateMultipleAsync(new List<ArxEntity> { spain, brazil, philippines });
        Show($"ids: {string.Join(", ", countryIds.Select(x => x.AsString()))}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT code, name
         * FROM country
         * WHERE code IN ('ES', 'BR', 'PH')
         * ORDER BY code;
         * -- Expect: 3 rows, Brazil, Spain, Philippines.
         * ===========================================================
         */
    }

    /// <summary>2. Devices with uuid ids: generated by the database, or generated by your code.</summary>
    private static async Task Scenario2_DevicesWithUuidIdsAsync(SampleContext dbContext)
    {
        Step("2. Devices with uuid ids: generated by the database, or generated by your code");

        var tablet = new ArxEntity("device");
        tablet["serial_number"] = "TAB-0001";

        var phone = new ArxEntity("device");
        phone["id"] = Guid.NewGuid();   // a client-generated id is written as given
        phone["serial_number"] = "PHN-0001";

        var deviceIds = await dbContext.Service.CreateMultipleAsync(new List<ArxEntity> { tablet, phone });
        Show($"tablet {deviceIds[0].AsGuid()} (from the database), phone {deviceIds[1].AsGuid()} (from the code)");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, serial_number
         * FROM device
         * WHERE serial_number IN ('TAB-0001', 'PHN-0001');
         * -- Expect: 2 rows, with the uuids the sample printed.
         * ===========================================================
         */
    }

    /// <summary>3. A regional price list with no id column: each price is identified by its region and SKU together.</summary>
    private static async Task Scenario3_APriceListWithNoIdColumnAsync(SampleContext dbContext)
    {
        Step("3. A regional price list: 3 regions x 1,000 SKUs, no id column, identified by (region, sku)");

        var prices = new List<ArxEntity>();

        foreach (var region in new[] { "ES", "BR", "PH" })
        {
            for (var i = 1; i <= 1_000; i++)
            {
                var price = new ArxEntity("price_list");
                price["region"] = new ArxEntityReference("country", region);   // a lookup to the country, by its code
                price["sku"] = $"SKU-{i:D6}";
                price["unit_price"] = region == "BR" ? 9.90m + i % 50 : 12.50m + i % 40;
                price["valid_from"] = new DateOnly(2026, 1, 1);
                prices.Add(price);
            }
        }

        var priceIds = await dbContext.Service.CreateMultipleAsync(prices);
        Show($"stored {await dbContext.Direct.CountAsync("price_list"):N0} prices; {priceIds.Count(x => !x.HasValue):N0} of {priceIds.Count:N0} ids are None: the table has no id column");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT region, count(*) AS prices
         * FROM price_list
         * WHERE region IN ('ES', 'BR', 'PH')
         * GROUP BY region
         * ORDER BY region;
         * -- Expect: 3 rows, 1000 prices each.
         * ===========================================================
         */
    }

    /// <summary>4. The same region and SKU again: the table's primary key refuses it.</summary>
    private static async Task Scenario4_TheSameRegionAndSkuAgainAsync(SampleContext dbContext)
    {
        Step("4. The same region and SKU again: the table's primary key refuses it");

        var duplicate = new ArxEntity("price_list");
        duplicate["region"] = new ArxEntityReference("country", "ES");
        duplicate["sku"] = "SKU-000001";
        duplicate["unit_price"] = 1.00m;
        duplicate["valid_from"] = new DateOnly(2026, 6, 1);

        try
        {
            await dbContext.Service.CreateAsync(duplicate);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT region, sku, unit_price, valid_from
         * FROM price_list
         * WHERE region = 'ES' AND sku = 'SKU-000001';
         * -- Expect: 1 row, the original: 13.50 from 2026-01-01 (the duplicate was refused).
         * ===========================================================
         */
    }

    /// <summary>5. One part of the key missing: the NOT NULL column refuses it (Create doesn't use keys; the database enforces them).</summary>
    private static async Task Scenario5_OnePartOfTheKeyMissingAsync(SampleContext dbContext)
    {
        Step("5. One part of the key missing: the NOT NULL column refuses it");

        var noRegion = new ArxEntity("price_list");
        noRegion["sku"] = "SKU-999999";
        noRegion["unit_price"] = 5.00m;
        noRegion["valid_from"] = new DateOnly(2026, 6, 1);

        try
        {
            await dbContext.Service.CreateAsync(noRegion);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotNullViolation)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM price_list
         * WHERE sku = 'SKU-999999';
         * -- Expect: 0: refused by the NOT NULL region column.
         * ===========================================================
         */
    }
}

  > 1. Countries by their ISO code (a supplied text id)
      ids: ES, BR, PH
  > 2. Devices with uuid ids: generated by the database, or generated by your code
      tablet 7f0ef662-b100-43af-a4e7-94351d89d420 (from the database), phone b3c4bc50-9a9e-4f2f-9e97-9c217a706171 (from the code)
  > 3. A regional price list: 3 regions x 1,000 SKUs, no id column, identified by (region, sku)
      stored 3,000 prices; 3,000 of 3,000 ids are None: the table has no id column
  > 4. The same region and SKU again: the table's primary key refuses it
      caught ArxDatabaseException: ARX3001 UniqueViolation: Create on 'price_list' violates unique constraint 'PK__price_li__072E5DBB910C34DC'.
  > 5. One part of the key missing: the NOT NULL column refuses it
      caught ArxDatabaseException: ARX3003 NotNullViolation: Create on 'price_list' has no value for required attribute 'region'.