Database Class Methods Reference

Summary

This section provides a comprehensive reference of all Database class methods organized by operation type. All methods are static and can be called directly using the Database class.

Query Methods

Query methods retrieve entity records using LINQ expressions. All query methods return SqlExpressionVisitor<T> which supports method chaining and lazy evaluation.

Query Extension Methods

Extension methods provide additional query functionality including execution, filtering, sorting, pagination, and eager loading.

Count Methods

Count methods return the number of records matching specified criteria without retrieving the actual records.

Search Methods

Search methods perform full-text search across searchable entity fields with additional filtering capabilities.

Retrieve Methods

Retrieve methods fetch a single record by its unique identifier.

Create Methods

Create methods insert new records into the database.

Edit Methods

Edit methods update existing records in the database.

Upsert Methods

Upsert methods insert new records or update existing records based on matching criteria.

Delete Methods

Delete methods remove records from the database.

Utility Methods

Utility methods provide additional database functionality.

QueryFlex Method

QueryFlex performs aggregate queries with GROUP BY support, returning dynamic results for unpredictable data structures.

Async Method Variants

Almost every Database operation has an asynchronous counterpart that returns a Task rather than blocking the request thread. Async variants are required in AspxAsyncController actions (which must be fully async) and recommended in any I/O-heavy code path in AspxController or Active Class code.

Most async variants are drop-in replacements: the method name is the sync name plus Async (or the AsAdmin suffix becomes AsAdminAsync). The return type is wrapped in Task<T>.

Example — async controller action returning IrisData:

public class ContactApiController : AspxAsyncController
{
    [Authorize]
    public async Task<ActionResponse> GetByAccount(string accountId)
    {
        if (string.IsNullOrEmpty(accountId))
            throw new ModelValidationException("accountId is required.");

        var contacts = await Database.Query<Contact>()
            .Where(c => c.AccountId == accountId)
            .OrderBy(c => c.LastName)
            .ToListAsync();

        return IrisData(new { contacts }, With.Update | With.Delete);
    }
}

Async create followed by an async edit:

[Authorize]
public async Task<ActionResponse> CreateAndAssign(Contact model, string ownerId)
{
    if (string.IsNullOrWhiteSpace(model.LastName))
        throw new ModelValidationException("Last name is required.");

    await Database.CreateAsync(model);

    model.OwnerId = ownerId;
    await Database.EditAsync(model);

    return IrisData(new { contact = model });
}

Running independent queries in parallel:

public async Task<ActionResponse> Dashboard(string accountId)
{
    var accountTask = Database.RetrieveAsync<Account>(accountId);
    var contactsTask = Database.Query<Contact>()
        .Where(c => c.AccountId == accountId)
        .ToListAsync();
    var opportunityCountTask = Database.Query<Opportunity>()
        .Where(o => o.AccountId == accountId)
        .CountAsync();

    await Task.WhenAll(accountTask, contactsTask, opportunityCountTask);

    return IrisData(new
    {
        account = accountTask.Result,
        contacts = contactsTask.Result,
        opportunityCount = opportunityCountTask.Result
    });
}

When to choose async:

• Required: Actions on AspxAsyncController — every action must be async Task<ActionResponse>.
• Recommended: Long-running or I/O-bound code paths in any controller. Async releases the request thread while the database or external call is in flight.
• Required for parallel work: When fetching multiple independent results, async lets you start the queries together and await Task.WhenAll(...) to wait for all of them concurrently.
• Optional: Short, simple, CPU-bound logic or one-off Setup-area handlers that already run synchronously can stay sync. Mixing sync and async unnecessarily adds noise without measurable benefit.

Common pitfalls:

• Do not call .Result or .Wait() on a Task in a controller action. Doing so blocks the thread and can deadlock the request pipeline. Always await.
• Mark the action async. Calling an async method without await returns a Task object instead of the actual result.
• Cancel-safe by default. The platform handles request cancellation; you do not need to thread CancellationToken through routine queries.

Method Naming Conventions

Create vs Insert: The Database class uses Create() for insert operations, not Insert(). This aligns with Magentrix terminology where "Create" represents the action of adding new records to the database.

Edit vs Update: The Database class uses Edit() for update operations, not Update(). This aligns with Magentrix terminology where "Edit" represents the action of modifying existing records.

Generic Type Parameter: Methods with generic type parameter <T> return strongly-typed results. Methods without generic parameters work with the base dbObject type and are useful when entity types are determined dynamically at runtime.

Overloads for Single and Bulk: Create, Edit, Upsert, and Delete methods provide overloads for both single-record and bulk operations. Always use the bulk overload when working with multiple records to optimize performance and reduce database round trips.

Common Parameters

func (Expression<Func<T, bool>>): A LINQ expression that defines filtering criteria. Example: f => f.Name.Contains("Test") && f.IsActive == true

relationship (Expression<Func<T, object>>): A LINQ expression specifying the related entity to eager load. Example: r => r.Account

fields (Expression<Func<T, object>>): A LINQ expression specifying which fields to load. Example: f => new { f.Id, f.Name, f.Type }

dbOptions (DatabaseOptions): Controls database operation behavior including system mode, transactional processing, and error handling mode.

Return Type Details

SqlExpressionVisitor<T>: Query methods return SqlExpressionVisitor<T> which supports LINQ method chaining. Call .ToList(), .First(), or .Count() to execute the query.

// Query returns SqlExpressionVisitor<Account>
var query = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name);

// Execute query
var accounts = query.ToList();  // Returns List<Account>

SaveResult: Contains operation results for Create, Edit, and Upsert operations including the record ID, error status, error messages, and timestamp. See Working with Database Results for complete reference.

DeleteResult: Contains operation results for Delete operations including error status and error messages. See Working with Database Results for complete reference.

IEnumerable<SaveResult> / IEnumerable<DeleteResult>: Bulk operations return collections of results, one for each record processed.

Usage Notes

Method Chaining: Query methods returning SqlExpressionVisitor<T> support LINQ method chaining. Chain methods in any order—the query is only executed when you call .ToList(), .ToListAsAdmin(), .First(), .FirstAsAdmin(), .Count(), or .CountAsAdmin().

Execution Context: All methods execute in the security context of the current user unless using .ToListAsAdmin(), .FirstAsAdmin(), or .CountAsAdmin() extension methods, which execute with system privileges.

Transaction Support: Operations support transactional execution using AllOrNone = true in DatabaseOptions, ensuring all records succeed or all fail together.

Error Handling: By default, operations throw exceptions on error. Set IsApiMode = true in DatabaseOptions to return errors in results without throwing exceptions.

Query Execution: Queries are lazy-evaluated. The actual database query executes only when you call .ToList(), .ToListAsAdmin(), .First(), .FirstAsAdmin(), .Count(), .CountAsAdmin(), or iterate over the results.

// Query not yet executed
var query = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name)
    .Limit(50);

// Query executes here
var accounts = query.ToList();