Create many records in one call and get their ids

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.CreateMultiple.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 Basic1_CreateManyRecords().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.CreateMultiple.Basics;

/// <summary>
/// Several records in one call, in seven scenarios. Three ways to declare the id column (in the service's schema, on
/// each entity, in a schema for this one call), then two tables with no id column, then two things that only happen
/// with many records: several tables in one list, and all or nothing when one record is refused.
/// </summary>
public sealed class Basic1_CreateManyRecords : ISample
{
    public string Name => "create-multiple-basics-1-create-many-records";

    public string Title => "Create many records in one call and get their ids";

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

        await Scenario2_IdColumnOnEachEntityAsync(dbContext.ConnectionString);

        await Scenario3_ASchemaForThisCallAsync(dbContext.ConnectionString);

        await Scenario4_NoIdColumn_ACompoundKeyAsync(dbContext.ConnectionString);

        await Scenario5_NoIdColumn_NoKeyAsync(dbContext.ConnectionString);

        await Scenario6_SeveralTablesInOneCallAsync(dbContext.ConnectionString);

        await Scenario7_AllOrNothingAsync(dbContext.ConnectionString);
    }

    /// <summary>1. The usual way: the id column declared in the schema, the ids read from the list CreateMultipleAsync returns.</summary>
    private static async Task Scenario1_IdColumnInTheSchemaAsync(string connectionString)
    {
        Step("1. Id column in the schema");

        var schema = ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id"))
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var first = new ArxEntity("account");
        first["name"] = "Contoso Ltd.";
        first["account_number"] = "ACC-0002";

        var second = new ArxEntity("account");
        second["name"] = "Fabrikam Inc.";
        second["account_number"] = "ACC-0003";

        var third = new ArxEntity("account");
        third["name"] = "Litware Inc.";
        third["account_number"] = "ACC-0004";

        var accounts = new List<ArxEntity> { first, second, third };

        // CreateMultipleAsync returns one id per entity, in the list's order: ids[0] is first's id, and so on.
        IReadOnlyList<ArxRecordId> ids = await service.CreateMultipleAsync(accounts);

        Show($"created accounts {string.Join(", ", ids)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number
         * FROM account
         * WHERE account_number IN ('ACC-0002', 'ACC-0003', 'ACC-0004')
         * ORDER BY id;
         * -- Expect: 3 rows, Contoso Ltd., Fabrikam Inc., Litware Inc., ids in list order.
         * ===========================================================
         */
    }

    /// <summary>2. The other way: the id column declared on each entity, the ids read from the entities.</summary>
    private static async Task Scenario2_IdColumnOnEachEntityAsync(string connectionString)
    {
        Step("2. Id column on each entity");

        // No schema catalog: each entity says what its id column is.
        var service = new ArxMsSqlDbService(connectionString);

        // Declared once, outside the loop: every entity shares the same object.
        var accountId = ArxIdColumn.Generated<long>("id");

        var accounts = new List<ArxEntity>();

        foreach (var (name, number) in new[] { ("Alpine Ski House", "ACC-0007"), ("Blue Yonder Airlines", "ACC-0008"), ("Coho Winery", "ACC-0009") })
        {
            var account = new ArxEntity("account");
            account.IdColumn = accountId;
            account["name"] = name;
            account["account_number"] = number;
            accounts.Add(account);
        }

        await service.CreateMultipleAsync(accounts);

        // Each id is also set on its own entity.
        foreach (var account in accounts)
        {
            Show($"{account["name"]} got id {account.RecordId}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number
         * FROM account
         * WHERE account_number IN ('ACC-0007', 'ACC-0008', 'ACC-0009')
         * ORDER BY id;
         * -- Expect: 3 rows, Alpine Ski House, Blue Yonder Airlines, Coho Winery.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. A third way: a schema for this one call. The entities declare nothing; the schema passed to CreateMultipleAsync
    /// declares the table for this call only, in place of the service's.
    /// </summary>
    private static async Task Scenario3_ASchemaForThisCallAsync(string connectionString)
    {
        Step("3. A schema for this call");

        // No schema catalog on the service: the call brings its own.
        var service = new ArxMsSqlDbService(connectionString);

        var schema = ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id"))
            .Build();

        var accounts = new List<ArxEntity>();

        foreach (var (name, number) in new[] { ("Wide World Importers", "ACC-0011"), ("Woodgrove Bank", "ACC-0012") })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            accounts.Add(account);
        }

        // The schema declares the table, so the entities mustn't: one with its own IdColumn or Keys is refused (ARX1001).
        var ids = await service.CreateMultipleAsync(accounts, schema);

        Show($"created accounts {string.Join(", ", ids)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number
         * FROM account
         * WHERE account_number IN ('ACC-0011', 'ACC-0012')
         * ORDER BY id;
         * -- Expect: 2 rows, Wide World Importers, Woodgrove Bank.
         * ===========================================================
         */
    }

    /// <summary>4. A table with no id column, where a compound key (region + sku) identifies each record.</summary>
    private static async Task Scenario4_NoIdColumn_ACompoundKeyAsync(string connectionString)
    {
        Step("4. Compound key");

        var schema = ArxSchemaCatalog.Create()
            .Table("country", ArxIdColumn.Supplied<string>("code"))
            .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        // A price's region must be a country that exists, so add the country first.
        var netherlands = new ArxEntity("country");
        netherlands["code"] = "NL";
        netherlands["name"] = "Netherlands";
        await service.CreateAsync(netherlands);

        var espresso = new ArxEntity("price_list");
        espresso["region"] = "NL";
        espresso["sku"] = "SKU-000002";
        espresso["unit_price"] = 1.20m;
        espresso["valid_from"] = new DateOnly(2026, 1, 1);

        var cappuccino = new ArxEntity("price_list");
        cappuccino["region"] = "NL";
        cappuccino["sku"] = "SKU-000003";
        cappuccino["unit_price"] = 1.80m;
        cappuccino["valid_from"] = new DateOnly(2026, 1, 1);

        // There is no id column, so each id in the list is ArxRecordId.None.
        var ids = await service.CreateMultipleAsync(new List<ArxEntity> { espresso, cappuccino });

        Show($"created {ids.Count} prices; their ids: {string.Join(", ", ids)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT region, sku, unit_price
         * FROM price_list
         * WHERE region = 'NL'
         * ORDER BY sku;
         * -- Expect: 2 rows, SKU-000002 (1.20) and SKU-000003 (1.80).
         * ===========================================================
         */
    }

    /// <summary>5. A table with no id column and no key: the same row can go in twice.</summary>
    private static async Task Scenario5_NoIdColumn_NoKeyAsync(string connectionString)
    {
        Step("5. No key");

        var schema = ArxSchemaCatalog.Create()
            .Table("activity_log", ArxIdColumn.NoIdColumn)
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var first = new ArxEntity("activity_log");
        first["action"] = "report exported";

        var again = new ArxEntity("activity_log");
        again["action"] = "report exported";

        var ids = await service.CreateMultipleAsync(new List<ArxEntity> { first, again });

        Show($"logged \"report exported\" {ids.Count} times");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT action, count(*)
         * FROM activity_log
         * WHERE action = 'report exported'
         * GROUP BY action;
         * -- Expect: count 2 (the same row went in twice).
         * ===========================================================
         */
    }

    /// <summary>6. Several tables in one call: the ids come back in list order, each of its own table's type.</summary>
    private static async Task Scenario6_SeveralTablesInOneCallAsync(string connectionString)
    {
        Step("6. Several tables");

        var schema = ArxSchemaCatalog.Create()
            .Table("country", ArxIdColumn.Supplied<string>("code"))
            .Table("device", ArxIdColumn.Generated<Guid>("id"))
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var france = new ArxEntity("country");
        france["code"] = "FR";
        france["name"] = "France";

        var kiosk = new ArxEntity("device");
        kiosk["serial_number"] = "KIOSK-0001";

        var germany = new ArxEntity("country");
        germany["code"] = "DE";
        germany["name"] = "Germany";

        var ids = await service.CreateMultipleAsync(new List<ArxEntity> { france, kiosk, germany });

        Show($"country {ids[0].AsString()}, device {ids[1].AsGuid()}, country {ids[2].AsString()}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT 'country' AS source, code AS id, name FROM country WHERE code IN ('FR', 'DE')
         * UNION ALL
         * SELECT 'device', CAST(id AS nvarchar(max)), serial_number FROM device WHERE serial_number = 'KIOSK-0001';
         * -- Expect: 3 rows: France, Germany and the kiosk device with its uuid.
         * ===========================================================
         */
    }

    /// <summary>7. All or nothing: when the database refuses one record, none of the list is stored and no entity gets an id.</summary>
    private static async Task Scenario7_AllOrNothingAsync(string connectionString)
    {
        Step("7. All or nothing");

        var schema = ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id"))
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var fine = new ArxEntity("account");
        fine["name"] = "Margie's Travel";
        fine["account_number"] = "ACC-0010";

        // Account numbers are unique, and ACC-0002 was created in scenario 1.
        var taken = new ArxEntity("account");
        taken["name"] = "Contoso (again)";
        taken["account_number"] = "ACC-0002";

        try
        {
            await service.CreateMultipleAsync(new List<ArxEntity> { fine, taken });
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
        {
            Show($"refused ({error.Code}); Margie's Travel wasn't stored either, and has no id ({(fine.RecordId == null ? "RecordId is null" : fine.RecordId)})");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number = 'ACC-0010';
         * -- Expect: 0: the list was refused, so Margie's Travel wasn't stored either.
         * ===========================================================
         */
    }
}

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. Id column in the schema
      created accounts 1, 2, 3
  > 2. Id column on each entity
      Alpine Ski House got id 4
      Blue Yonder Airlines got id 5
      Coho Winery got id 6
  > 3. A schema for this call
      created accounts 7, 8
  > 4. Compound key
      created 2 prices; their ids: (none), (none)
  > 5. No key
      logged "report exported" 2 times
  > 6. Several tables
      country FR, device 3afb7bf0-9fce-41e4-9c8f-bb673baa6666, country DE
  > 7. All or nothing
      refused (UniqueViolation); Margie's Travel wasn't stored either, and has no id (RecordId is null)