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

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);

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.

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();

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));

BaseEntity Columns

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

[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; }
}

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:

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; }
}

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

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

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

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();

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.

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

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