Flagged enums

Map a C# flags enum to a many-to-many junction table with [FlaggedEnum] in Socigy.OpenSource.DB v0.3.2.

updated 5 Jun 20262 min readv0.3.2View as Markdown

Overview

A flags enum represents a set of values, for example a user's roles. Instead of storing a bitmask integer, [FlaggedEnum] instructs the generator to manage a junction table: one row per enum flag that is set. This lets you query all users with a specific role efficiently.

Declare the enum table

The enum carries [Table] to make it a reference table:

[Table("roles")]
public enum Role
{
    Reader    = 1,
    Writer    = 2,
    Moderator = 4,
    Admin     = 8,
}

The migration tool seeds this table with all declared enum values. Values must be powers of two; they are stored as the id column.

Declare the owner model

Add a [FlaggedEnum] property on the model that holds the combined flags value. The property type must be the flags enum:

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    [FlaggedEnum]
    public Role Roles { get; set; }
}

Generated junction table

The source generator creates a junction table named {main_table}_{enum_table} with two foreign key columns:

CREATE TABLE "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id")  REFERENCES "users"("id")  ON DELETE CASCADE,
    FOREIGN KEY ("roles_id")  REFERENCES "roles"("id")
);

Column naming convention:

Main table FK: {main_table}_{pk_column}, giving users_id.
Enum FK: {enum_table}_id, giving roles_id.

You can override individual mappings by passing alternating (localPropertyName, junctionColumnName) pairs: [FlaggedEnum(nameof(Id), "user_id")]. Override the junction table name with the TableName property.

Reading and writing

When you insert or query a User, the Roles property is treated as a combined flags bitmask. The generated code decomposes the bitmask into individual flag values and syncs the junction table rows.

var user = new User
{
    Username = "alice",
    Roles    = Role.Reader | Role.Writer,
};
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();

When a user is read back, the junction table rows are aggregated into the Roles bitmask:

await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Roles.HasFlag(Role.Writer)); // True
}

DDL setup

You must create the enum table and seed it before using the junction table. The CLI tool does this automatically; see Schema generation. For manual setup:

CREATE TABLE "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL);

CREATE TABLE "users_roles" (
    "users_id" UUID    NOT NULL,
    "roles_id" INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);

NOTE

When the junction table needs extra columns (an AssignedAt timestamp, for instance), declare an explicit [FlagTable] class and point to it with [FlaggedEnumTable(typeof(...))] instead of [FlaggedEnum]. See the Tables article.

WARNING

Enum member values must be exact powers of two (1, 2, 4, 8, and so on). Arbitrary integers break the bitmask decomposition.

← previousEncrypted columns

next →Sequences