Flagged enums
Map a C# flags enum to a many-to-many junction table with [FlaggedEnum].
Overview
A flags enum represents a set of values (e.g. 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}→users_id - Enum FK:
{enum_table}_id→roles_id
Reading and writing
When you insert or query a User, the Roles property is treated as a combined flags bitmask. Internally, 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")
);