Database & Data Model

school

Entity Framework Basics

Phoenix uses Entity Framework Core to communicate with the database. This allows the data model to be defined entirely in code, and natural C# LINQ queries to be written against it. EF Core enables expressive, type-safe code for all database operations.

Terminology

label

Entity — A type that describes a database table.

label

Database Context — The class that handles access to the database. Think of it as “an instance of the database.”

label

LINQ — Language Integrated Query — C#'s built-in library for querying collections. EF Core uses LINQ queries to read data from the database.

label

Navigation Property — Special properties on Entity types that represent a foreign key relationship as data.

label

Migration — Descriptions of updates to the data model. Each time the data model changes, a migration is generated that tells EF Core how to update the database tables to match the code.

What does a query look like?

Every query starts with a Database Context instance. Queries are written against DbSet properties on the context—each DbSet represents a table. Phoenix uses the Set<T>() syntax primarily.

using var dbContext = GetSomeDatabaseContext();

// From the set of all products...
var myEntity = dbContext.Set<Product>()
    // Where the Name equals "Sample Product"...
    .Where(x => x.Name == "Sample Product")
    // Return the only matching record from the table
    .Single();

What is IQueryable<T>?

IQueryable<T> represents an enumerable result set, but defers execution of the query until it is enumerated. Think of it as a Query Builder—any LINQ calls you make are not executed until you trigger enumeration. EF Core then translates the full LINQ query into raw SQL that gets sent to the database.

// Unenumerated query - no database call yet
var query = db.Set<Product>().Where(x => x.IsActive);

Enumeration Methods

Enumeration “runs the query.” Any LINQ method that changes an IQueryable to something else will enumerate:

Method	Description
ToList	Returns all results wrapped in a `List<T>`
First	Returns the first result; throws if no results
FirstOrDefault	Returns the first result, or `null` if none
Single	Returns exactly one result; throws if zero or more than one
SingleOrDefault	Returns one result, or `null`; throws if more than one
ToDictionary	Returns a `Dictionary<TKey, TValue>` with a key and optional value selector
Any	Returns `true` if any results exist, otherwise `false`
Count	Returns the number of matching elements

edit_note

Writing Queries

Accessing the Database Context

If you have a Pipeline Context, access the database like so:

await using var db = context.GetDisposableDatabaseContext();

The context must be disposed (hence the using). The await before using is optional but recommended in async methods, as it enables asynchronous disposal.

If you do not have access to a Pipeline Context, you can also retrieve a database instance from dependency injection. The type to inject is ClarityEntities.

Set<T>() Syntax

Phoenix uses the Set<T>() method primarily, which returns a DbSet accessing the given Entity type. From here, simply chain LINQ expressions:

await using var db = context.GetDisposableDatabaseContext();

var products = await db.Set<Product>()
    .Where(x => x.IsActive)
    .Select(x => new { x.Id, x.Name })
    .ToListAsync();

account_tree

Navigation Properties

Related Objects (Foreign Key → Single)

A navigation property represents “the record that a foreign key is pointing to.” In Phoenix, these are called Related Objects.

public class Product
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public int CategoryId { get; set; }

    public Category? Category { get; set; } // Navigation Property
}

With this, querying the related category is straightforward:

var categoryName = db.Set<Product>()
    .Where(x => x.Id == 123)
    .Select(x => x.Category!.Name)
    .Single();

Associated Objects (Collection)

Navigation Properties also work in reverse. A Category could have many Products. In Phoenix, these are called Associated Objects.

public class Category
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public ICollection<Product> Products { get; set; }
        = new HashSet<Product>();
}

Querying all products in a category:

var productNames = db.Set<Category>()
    .Where(x => x.Id == 123)
    .SelectMany(x => x.Products.Select(y => y.Name))
    .ToList();

// Or, more cleanly from the other direction:
var productNames = db.Set<Product>()
    .Where(x => x.CategoryId == 123)
    .Select(x => x.Name)
    .ToList();

sync_alt

Updating & Deleting

Updating a Record

Standard approach: query the full record, assign new values, and save changes.

// Get the record we wish to update
var product = db.Set<Product>()
    .Where(x => x.Id == 123)
    .Single();

// Update the value we wish to change
product.CategoryId = newCategoryId;

// Save this change to the database
db.SaveChanges();

Bulk API alternative:

db.Set<Product>()
    .Where(x => x.Id == 123)
    .ExecuteUpdate(s => s.SetProperty(p => p.CategoryId, _ => newCategoryId));

Deleting a Record

Standard approach:

var product = db.Set<Product>()
    .Where(x => x.Id == 123)
    .Single();

db.Set<Product>().Remove(product);
db.SaveChanges();

Bulk API alternative:

db.Set<Product>()
    .Where(x => x.Id == 123)
    .ExecuteDelete();

table_chart

BaseEntity & SimpleKeyTable

BaseEntity Columns

The vast majority of Entity classes derive from BaseEntity, which provides these columns automatically:

Column	Type	Description
Id	int	Unique identifier. Value is set by the database when the record is first saved.
Key	string?	Alternative string identifier for more familiar identification.
IsActive	bool	Whether the record is currently considered active.
IsDeleted	bool	Whether the record is considered soft deleted.
CreatedDate	DateTime	Timestamp when the record was first saved to the database.
UpdatedDate	DateTime	Timestamp of the most recent change. Equals CreatedDate if never updated.
Tags	List<EntityTag>	Key/value pairs for storing custom data without altering the entity type.

[SQLTable] Attribute

Any class with the [SQLTable] attribute is treated as an Entity type. Entity classes should be stored in the most relevant plugin's DataModel folder.

[SQLTable]
public class Product : BaseEntity
{
    [Required]
    [MaxLength(128)]
    public string? Name { get; set; }

    public string? SEOURL { get; set; }

    public decimal Price { get; set; }

    public int Stock { get; set; }
}

SimpleKeyTable

For types, states, statuses, or similar records, use SimpleKeyTable instead of C# enums. This supports flexibility without altering code to add or change values.

[SQLTable]
public class ProductType : SimpleKeyTable;

Referenced as a foreign key on other tables:

[SQLTable]
public class Product : BaseEntity
{
    // ...

    [Required]
    public string? TypeId { get; set; }

    public ProductType? Type { get; set; }
}

Property Configuration Attributes

Decorate properties with attributes to guide how EF Core generates the resulting database table:

[Required]

Marks a property as non-null in the database. A value must be provided before the record can be saved. Preferred over the required specifier because it preserves trivial constructibility.

[MaxLength(x)]

Specifies the maximum length of a string property.

[DefaultValue(x)]

Defines the fallback value for a property if none is specified.

[NotMapped]

Indicates that a property should NOT be included in the data model. No column will be created and EF queries against it will fail.

swap_horiz

Models & Mapping

DTOs / Models

When returning data over the wire, it's often preferable to return a streamlined representation of an entity, containing flattened properties joined from various tables. Phoenix calls these Models. The process of converting an Entity to a Model is called mapping.

Model classes must be trivially constructible (parameter-less constructor, no required properties).

public class ProductModel
{
    public int Id { get; set; }

    public string? Name { get; set; }
}

Map<T>() Utility

Use .Map<T>() in your database query. It returns IQueryable, so the mapping logic is built directly into the SQL query for optimal performance:

var productModel = db.Set<Product>()
    .Where(x => x.Id == 123)
    .Map<ProductModel>()
    .Single();

The resulting SQL only selects the needed columns:

-- Without Map<T> or Select
SELECT * FROM [Products].[Product] WHERE [Id] = 123

-- With Map<T>
SELECT [Id], [Name] FROM [Products].[Product] WHERE [Id] = 123

Mapping Semantics

The mapper follows well-defined rules:

1

Exact name match — Property names that match exactly between entity and model (e.g., Id, Name).

2

Prefix flattening — If no exact match, the mapper checks for a matching prefix on a navigation property, then looks for the suffix within that object. This process is recursive to arbitrary depth.

3

Recursive depth — Flatten properties from several objects deep (e.g., RegionCountryName).

public class ProductModel
{
    // Exact match
    public int Id { get; set; }

    // Flattened: Type.Name
    public string? TypeName { get; set; }
}

public class ContactModel
{
    // Deep flattening: Region.Country.Name
    public string? RegionCountryName { get; set; }
}

[MapFrom] Attribute

Use [MapFrom] to set the flattening path separately from the property name:

public class ContactModel
{
    [MapFrom("RegionCountryName")]
    public string? CountryName { get; set; }
}

[MapFrom] also supports a comparison value (returns bool):

public class ContactModel
{
    [MapFrom("RegionName", "California")]
    public bool IsInCalifornia { get; set; }
}

[NotMapped] excludes properties from the mapper:

public class CategoryModel
{
    [NotMapped]
    public IEnumerable<CategoryModel>? Children { get; set; }
}

Nested Model Mapping

Mapping works with nested models. Unlike flattened properties, there is a maximum depth of 8 nested entries to avoid infinite loops for parent/child relationships.

public class ContactModel
{
    public RegionModel? Region { get; set; }
}

public class RegionModel
{
    public CountryModel? Country { get; set; }
}

public class CountryModel
{
    public string? Name { get; set; }
}

Collection Mapping

Mapping applies to Associated Objects too. Collection mapping supports Select, Count, All, and Any operations. If none are specified, Select is the default.

public class ProductModel
{
    // Maps: Categories.Select(c => c.Primary.Name)
    [MapFrom("CategoriesSelectPrimaryName")]
    public List<string> CategoryNames { get; set; }

    // Same as above, "Select" is implied
    [MapFrom("CategoriesPrimaryName")]
    public List<string> CategoryNames2 { get; set; }

    // Expands to: Categories.Count()
    public int CategoriesCount { get; set; }

    // Expands to: Categories.Any()
    public bool CategoriesAny { get; set; }

    // Expands to: RelatedProducts.Any(x => x.TypeId == "VARIANT-OF-MASTER")
    [MapFrom("RelatedProductsAnyTypeId", "VARIANT-OF-MASTER")]
    public bool HasVariants { get; set; }
}

warning IModel<T> Interface

Be careful when using this interface. It automatically exposes read-only endpoints for any record in the table. Ensure you are not returning any secure information before using it.

public class RegionModel : IModel<Region>
{
    public int Id { get; set; }

    public string? AlphaCode { get; set; }

    public string? Name { get; set; }
}

Appropriate for freely public data such as geography (state/country dropdowns), types, statuses. Never use on UserModel, CustomerModel, ContactModel, or any data requiring authorization.

Advanced: Property Maps

For precise control over mapping, define a Property Map in the model's static constructor:

public class ProductModel
{
    static ProductModel()
    {
        Mapper<Product, ProductModel>.SetPropertyMap(
            // When mapping this property...
            x => x.PrimaryImageFileName,
            // Use this expression to assign its value
            x => x.Images
                .Where(x => x.IsPrimary)
                .FirstOrDefault()!.FileName);
    }

    public string? PrimaryImageFileName { get; set; }
}

delete_sweep

Soft Deletion

Phoenix tables contain two columns that indicate record “freshness”:

IsActive

Indicates if a record is current or historical data. Can also be used for drafts, stubs, or partially “realized” records.

IsDeleted

Indicates a record should be considered deleted. The record still exists in the database but the application ignores it. Soft deleted records can be restored by toggling IsDeleted back to false.

State	IsActive	IsDeleted	Meaning
Normal	true	false	Active, in use
Historical	false	false	Inactive but preserved (e.g., removed payment method tied to existing records)
Soft Deleted	false	true	Logically deleted, restorable

When querying, always filter for active and non-deleted records:

var products = db.Set<Product>()
    .WhereActiveAndNotDeleted()
    // ... continued query

visibility_off

Shadow Properties

Client-Specific Columns

Shadow Properties allow client-specific columns to exist in the database without altering shared Entity classes. This prevents inflating the shared data model with project-specific properties. Configure them in the plugin's OnModelCreating hook:

public class MyPlugin : Plugin
{
    public override void OnModelCreating(ModelBuilder builder)
    {
        // Creates a Shadow Property called NdcNumber on the Product table
        builder.Entity<Product>().Property<string?>("NdcNumber");
    }
}

Querying Shadow Properties

Since the property does not exist on the Entity class, use EF.Property<T>() to query against it:

// Select a shadow property value
var ndcNumber = await db.Set<Product>()
    .Where(x => x.Id == 1)
    .Select(x => EF.Property<string?>(x, "NdcNumber"))
    .SingleAsync();

// Filter by a shadow property
var product = await db.Set<Product>()
    .Where(x => EF.Property<string?>(x, "NdcNumber") == "abcd1234")
    .SingleAsync();

Tags as an Alternative

For simpler custom data, Tags (JSON key/value pairs on BaseEntity) can store extra values without creating shadow properties. Use shadow properties when you need actual database columns for indexing, querying performance, or data integrity constraints.

star

Best Practices

check_circle Select only what you need

Use Select or Map<T> to grab only the properties you need. Grabbing full records has a noticeable performance impact on large queries.

var products = await db.Set<Product>()
    .WhereActiveAndNotDeleted()
    .Select(x => new { x.Id, x.Name })
    .ToListAsync();

check_circle Prefer Async enumeration

Always enumerate queries with the Async version of the enumerator (e.g., ToListAsync, SingleAsync). Pass your CancellationToken along to any caller that can accept it.

check_circle Use Include for nested types (avoid N+1 queries)

Lazy loading is disabled in Phoenix. Never query nested properties in a loop—this incurs a round trip per iteration. Use Include to eagerly load:

Bad — N+1 queries

var products = await db.Set<Product>()
    .ToListAsync();

foreach (var product in products)
{
    // BAD: round trip per iteration
    var cats = await db.Set<CategoryProduct>()
        .Where(x => x.ProductId == product.Id)
        .ToListAsync();
}

Good — Eager loading

var products = await db.Set<Product>()
    .Include(x => x.Categories)
    .WhereActiveAndNotDeleted()
    .ToListAsync();

foreach (var product in products)
{
    // Already loaded
}

check_circle Use AsNoTracking for read-only queries

Prevents EF from tracking changes to returned entities. Good practice even for queries returning cut-down selections, as a safeguard against future refactors:

var readOnlyProducts = await db.Set<Product>()
    .AsNoTracking()
    .WhereActiveAndNotDeleted()
    .ToListAsync();

check_circle Use EF.Functions for database functions

For database functions without a direct C# equivalent (e.g., SQL LIKE), use EF.Functions:

// Translates to: WHERE [Name] LIKE '%sample%'
var sample = await db.Set<Product>()
    .Where(x => EF.Functions.Like(x.Name, "%sample%"))
    .ToListAsync();

upgrade

Migrations

Whenever the data model is modified (adding, removing, or altering any Entity class), a migration must be created. The process is straightforward:

1

Ensure your updates to the data model are complete and the solution builds.

2

cd into the Client folder.

3

Create the migration or apply existing ones.

Create a Migration

dotnet ef migrations add "DescriptiveName"

Name should be short, descriptive, PascalCase (e.g., AddPatientTables, DropStaleTables).

Apply Existing Migrations

dotnet ef database update

Applies all pending migrations to your database.

dns

PostgreSQL & MSSQL

Both PostgreSQL and Microsoft SQL Server are fully supported via EF Core's database provider abstraction. The connection string determines which provider is used. EF Core generates provider-appropriate SQL automatically, and migration files work across both providers.

database

PostgreSQL

Host=localhost;
Database=core;
Username=user;
Password=pass;

database

MSSQL

Server=localhost;
Database=core;
Integrated Security=true;
TrustServerCertificate=true;

How it works

chevron_right

The connection string determines the database provider at startup.

chevron_right

EF Core translates LINQ queries into provider-appropriate SQL automatically.

chevron_right

Migration files work across both providers without modification.

chevron_right

Application code remains provider-agnostic—no changes needed when switching databases.

lock

Encryption at Rest / TDE

Phoenix uses database-level Transparent Data Encryption (TDE) by default. Data is encrypted on disk and decrypted in memory during queries, making it completely transparent to the application layer—no code changes needed.

storage

SQL Server

TDE is built-in and enabled per database. Enterprise-grade encryption handled entirely by the database engine.

storage

PostgreSQL

Uses the pgcrypto extension with disk-level encryption options for at-rest protection.

chevron_right

Transparent to application layer—no code changes needed to enable or work with TDE.

chevron_right

Data is encrypted on disk and decrypted in memory during queries.

chevron_right

Combined with tokenization for PII (see the Security page for details).

help

Frequently Asked Questions

How does Entity Framework Core handle dual database support? expand_more

The platform uses EF Core's database provider abstraction to support both PostgreSQL and MSSQL. A single set of entity models and migrations works across both providers. The database provider is selected via configuration at startup, allowing clients to choose based on their existing infrastructure. Provider-specific SQL optimizations are handled transparently by EF Core's query translation layer. See Dual Database Support.

What is the soft deletion strategy? expand_more

All entities inherit from BaseEntity, which includes soft deletion support. When an entity is deleted, it is marked with a deletion timestamp rather than being physically removed from the database. Global query filters automatically exclude soft-deleted records from all queries, ensuring they are invisible to the application without losing the data. This supports audit trails and data recovery scenarios. See Soft Deletion.

How do migrations work across environments? expand_more

Migrations are generated using EF Core's migration tooling and committed to the repository. They run automatically on application startup, ensuring the database schema is always in sync with the code. Migrations are ordered and idempotent, so they can safely run multiple times. For dual-database support, migrations are generated for each provider and applied based on the configured database type. See Migrations.

On this page

Entity Framework Basics

Terminology

What does a query look like?

What is IQueryable<T>?

Enumeration Methods

Writing Queries

Accessing the Database Context

Set<T>() Syntax

Navigation Properties

Related Objects (Foreign Key → Single)

Associated Objects (Collection)

Updating & Deleting

Updating a Record

Deleting a Record

BaseEntity & SimpleKeyTable

BaseEntity Columns

[SQLTable] Attribute

SimpleKeyTable

Property Configuration Attributes

Models & Mapping

DTOs / Models

Map<T>() Utility

Mapping Semantics

[MapFrom] Attribute

Nested Model Mapping

Collection Mapping

warning IModel<T> Interface

Advanced: Property Maps

Soft Deletion

IsActive

IsDeleted

Shadow Properties

Client-Specific Columns

Querying Shadow Properties

Tags as an Alternative

Best Practices

check_circle Select only what you need

check_circle Prefer Async enumeration

check_circle Use Include for nested types (avoid N+1 queries)

check_circle Use AsNoTracking for read-only queries

check_circle Use EF.Functions for database functions

Migrations

PostgreSQL & MSSQL

PostgreSQL

MSSQL

How it works

Encryption at Rest / TDE

SQL Server

PostgreSQL

Frequently Asked Questions