Data operations

Insert rows

Column and value arrays must have the same length. The provider binds values using its driver-specific parameter mappings. For multiple rows issue multiple Insert.IntoTable(...).Row(...) expressions. Each Row completes one insert; its returned builder offers only IfNotExists, so a second Row cannot silently replace the first. Insert, update and delete each expose only their supported steps.

Database.Insert("Users", new[] { "Id", "Name" }, new object[] { 1, "Ada" });

migration.Insert.IntoTable("Users")
    .Row(new[] { "Id", "Name" }, new object[] { 1, "Ada" });

Update and delete with predicates

Classic update/delete without a predicate affects every row. Fluent update/delete requires Where(...) or an explicit AllRows() to complete the operation. Empty predicate arrays are rejected; an unfinished chain fails during Build, Apply or Preview before any queued operation executes. Fluent WhereSql is available for updates only; its text is trusted SQL, not an escaped user input.

Database.Update("Users", new[] { "Name" }, new object[] { "Ada Lovelace" },
    new[] { "Id" }, new object[] { 1 });

migration.Update.Table("Users")
    .Set(new[] { "Name" }, new object[] { "Ada Lovelace" })
    .Where(new[] { "Id" }, new object[] { 1 });

Database.Delete("Users", new[] { "Id" }, new object[] { 1 });

migration.Delete.FromTable("Users").Where(new[] { "Id" }, new object[] { 1 });

Delete duplicate rows

DeleteDuplicateRows keeps one arbitrary row per composite key using database equality and collation. It supports SQLite ordinary rowid tables, PostgreSQL, Oracle ROWID tables and SQL Server. NULL keys compare equal by default; pass DuplicateNullHandling.ExcludeNullKeys to leave rows with any NULL key untouched. The direct API returns the database-reported affected-row count. Non-key values do not influence the survivor. Keys must be non-empty, distinct existing columns.

The operation executes one DELETE in the existing transaction without changing the schema. Normal DELETE triggers and foreign-key rules apply. It does not prevent concurrent or future duplicates: coordinate writers and add an appropriate unique constraint separately. Deleted data cannot be automatically reversed. SQL preview is unsupported because safe physical row identity selection requires live metadata. Unsupported providers and SQLite tables without an accessible rowid are rejected.

int removed = Database.DeleteDuplicateRows("Assignments",
    new[] { "RoleId", "GroupId" }, DuplicateRowRetention.Any);

migration.Delete.DuplicateRows().FromTable("Assignments")
    .ByColumns("RoleId", "GroupId").KeepAny();

Conditional seed data

Use an explicit identifying predicate when a seed should exist only once. This is distinct from a migration version: a named profile can run repeatedly without a history entry. Coordinate competing writers; a check-then-insert helper is not a substitute for a database unique key.

Database.InsertIfNotExists("Users", new[] { "Id", "Name" },
    new object[] { 1, "Ada" }, new[] { "Id" }, new object[] { 1 });

migration.Insert.IntoTable("Users")
    .Row(new[] { "Id", "Name" }, new object[] { 1, "Ada" })
    .IfNotExists(new[] { "Id" }, new object[] { 1 });

Copying and reversal

Use the provider CopyDataFromTableToTable helper or fluent Execute.CopyDataFromTable(...).ToTable(...).WithColumns(...) for named-column copies. Both tables must already exist and target columns must accept the source values. Execute.UpdateTable(target).FromTable(source).Set(copyPairs).Match(keyPairs) maps source/target pairs. CopyDataFromTable also supports OrderBy after WithColumns. These operations retain provider limits and are outside the SQL-preview subset. A reverse data migration needs authored recovery logic; auto-reversal cannot recreate deleted or overwritten values.

Database.CopyDataFromTableToTable("Users",
    new System.Collections.Generic.List<string> { "Id", "Name" }, "ArchivedUsers",
    new System.Collections.Generic.List<string> { "UserId", "DisplayName" });

migration.Execute.CopyDataFromTable("Users").ToTable("ArchivedUsers")
    .WithColumns(new[] { "Id", "Name" }, new[] { "UserId", "DisplayName" });