Querying Records (READ Operations)

Summary

This section covers all methods for retrieving records from the database, including LINQ-based queries, extension methods for execution and sorting, pagination, eager loading, counting records, aggregate queries, and system mode execution.

Basic LINQ Queries

The most common way to query records is using LINQ expressions with Database.Query<T>().

Query All Records:

// Query all accounts (call .ToList() to execute)
var accounts = Database.Query<Account>().ToList();

Query with Simple Filter:

// Query partner accounts
var partners = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .ToList();

// Query active contacts
var activeContacts = Database.Query<Contact>()
    .Where(f => f.IsActive == true)
    .ToList();

Query with Multiple Conditions:

// AND conditions
var accounts = Database.Query<Account>()
    .Where(f => 
        f.Type == "Partner" && 
        f.IsActive == true && 
        f.AnnualRevenue > 1000000
    )
    .ToList();

// OR conditions
var accounts = Database.Query<Account>()
    .Where(f => 
        f.Type == "Partner" || 
        f.Type == "Customer"
    )
    .ToList();

Query with Sorting:

// Sort ascending
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name)
    .ToList();

// Sort descending
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderByDescending(f => f.AnnualRevenue)
    .ToList();

// Multiple sort fields
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Industry)
    .ThenBy(f => f.Name)
    .ToList();

Query with Limit:

// Get first 100 records
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Limit(100)
    .ToList();

// Get top 10 by revenue
var topAccounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderByDescending(f => f.AnnualRevenue)
    .Limit(10)
    .ToList();

Pagination:

// Page 1: First 25 records (take 25, skip 0)
var page1 = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name)
    .Limit(25, 0)
    .ToList();

// Page 2: Records 26-50 (take 25, skip 25)
var page2 = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name)
    .Limit(25, 25)
    .ToList();

// Generic pagination helper
public List<Account> GetAccountsPage(int pageNumber, int pageSize)
{
    var skip = (pageNumber - 1) * pageSize;
    
    return Database.Query<Account>()
        .Where(f => f.Type == "Partner")
        .OrderBy(f => f.Name)
        .Limit(pageSize, skip)
        .ToList();
}

LINQ Queries with Filters

String Field Filtering:

// Exact match
var accounts = Database.Query<Account>()
    .Where(f => f.Name == "Partner Company")
    .ToList();

// Contains
var accounts = Database.Query<Account>()
    .Where(f => f.Name.Contains("Tech"))
    .ToList();

// Starts with
var accounts = Database.Query<Account>()
    .Where(f => f.Name.StartsWith("Mag"))
    .ToList();

// Ends with
var accounts = Database.Query<Account>()
    .Where(f => f.Email.EndsWith("@magentrix.com"))
    .ToList();

// Case-insensitive comparison
var accounts = Database.Query<Account>()
    .Where(f => f.Name.ToLower().Contains("partner"))
    .ToList();

Numeric Field Filtering:

// Greater than
var accounts = Database.Query<Account>()
    .Where(f => f.AnnualRevenue > 1000000)
    .ToList();

// Less than or equal
var accounts = Database.Query<Account>()
    .Where(f => f.NumberOfEmployees <= 50)
    .ToList();

// Between values
var accounts = Database.Query<Account>()
    .Where(f => f.AnnualRevenue >= 100000 && f.AnnualRevenue <= 500000)
    .ToList();

Date/DateTime Filtering:

// Created in last 30 days
var thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);
var recentAccounts = Database.Query<Account>()
    .Where(f => f.CreatedOn >= thirtyDaysAgo)
    .ToList();

// Created this year
var yearStart = new DateTime(DateTime.UtcNow.Year, 1, 1);
var thisYearAccounts = Database.Query<Account>()
    .Where(f => f.CreatedOn >= yearStart)
    .ToList();

// Date range
var startDate = new DateTime(2024, 1, 1);
var endDate = new DateTime(2024, 12, 31);
var accounts = Database.Query<Account>()
    .Where(f => f.CreatedOn >= startDate && f.CreatedOn <= endDate)
    .ToList();

Boolean Field Filtering:

// True values
var activeAccounts = Database.Query<Account>()
    .Where(f => f.IsActive == true)
    .ToList();

// False values
var inactiveAccounts = Database.Query<Account>()
    .Where(f => f.IsActive == false)
    .ToList();

Null Checks:

// Null values
var contactsWithoutAccount = Database.Query<Contact>()
    .Where(f => f.AccountId == null)
    .ToList();

// Not null values
var contactsWithAccount = Database.Query<Contact>()
    .Where(f => f.AccountId != null)
    .ToList();

// String null or empty
var contactsWithoutEmail = Database.Query<Contact>()
    .Where(f => string.IsNullOrEmpty(f.Email))
    .ToList();

Collection Filtering (IN clause):

// Field value in list
var validTypes = new List<string> { "Partner", "Customer", "Vendor" };
var accounts = Database.Query<Account>()
    .Where(f => validTypes.Contains(f.Type))
    .ToList();

// Field value not in list
var excludedTypes = new List<string> { "Prospect", "Inactive" };
var accounts = Database.Query<Account>()
    .Where(f => !excludedTypes.Contains(f.Type))
    .ToList();

// ID in list
var accountIds = new List<string> { "001R000000abc123", "001R000000def456" };
var accounts = Database.Query<Account>()
    .Where(f => accountIds.Contains(f.Id))
    .ToList();

Related Entity Filtering:

Navigation properties allow you to access and filter by fields on related entities without writing explicit joins. When you reference a relationship field like f.Account.Type, Magentrix automatically resolves the relationship and includes the related entity in the query. This provides a clean, intuitive syntax for filtering records based on their parent or related record attributes.

// Filter by related entity field
var contacts = Database.Query<Contact>()
    .Where(f => f.Account.Type == "Partner")
    .ToList();

// Multiple related entity conditions
var contacts = Database.Query<Contact>()
    .Where(f => 
        f.Account.Type == "Partner" &&
        f.Account.IsActive == true &&
        f.Account.AnnualRevenue > 1000000
    )
    .ToList();

Eager Loading Related Records

Use the Include() extension method to eager load related entities and avoid N+1 query problems.

Basic Include:

// Load related Account (Id and Name only)
var contacts = Database.Query<Contact>()
    .Where(f => f.IsActive == true)
    .Include(r => r.Account)
    .ToList();

// Access loaded relationship without additional query
foreach (var contact in contacts)
{
    // Account.Id and Account.Name are already loaded
    if (contact.Account != null)
    {
        SystemInfo.Debug($"{contact.Name} works at {contact.Account.Name}");
    }
}

Include with Specific Fields:

// Load Account with specific fields: Id, Name, and Type
var contacts = Database.Query<Contact>()
    .Where(f => f.IsActive == true)
    .Include(r => r.Account, f => new { f.Id, f.Name, f.Type })
    .ToList();

// Access all loaded fields
foreach (var contact in contacts)
{
    if (contact.Account != null)
    {
        SystemInfo.Debug($"{contact.Name} at {contact.Account.Name} ({contact.Account.Type})");
    }
}

Multiple Includes:

// Load multiple relationships
var contacts = Database.Query<Contact>()
    .Where(f => f.IsActive == true)
    .Include(r => r.Account, f => new { f.Id, f.Name })
    .Include(r => r.Owner, f => new { f.Email, f.Name })
    .Include(r => r.ReportsTo, f => new { f.Name, f.Email })
    .ToList();

// Access all loaded relationships
foreach (var contact in contacts)
{
    SystemInfo.Debug($"Contact: {contact.Name}");
    
    if (contact.Account != null)
    {
        SystemInfo.Debug($"  Account: {contact.Account.Name}");
    }
    
    if (contact.Owner != null)
    {
        SystemInfo.Debug($"  Owner: {contact.Owner.Name}");
    }
    
    if (contact.ReportsTo != null)
    {
        SystemInfo.Debug($"  Reports To: {contact.ReportsTo.Name}");
    }
}

Counting Records

Use .Count() extension method to count records without retrieving them.

Count Extension Method:

// Count using extension method
var partnerCount = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Count();

// Count in system mode
var allAccountsCount = Database.Query<Account>()
    .CountAsAdmin();

Count with Pagination Context:

// Get total count for pagination
var totalRecords = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Count();

// Calculate total pages
var pageSize = 25;
var totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

// Get current page
var pageNumber = 1;
var skip = (pageNumber - 1) * pageSize;

var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .OrderBy(f => f.Name)
    .Limit(pageSize, skip)
    .ToList();

SystemInfo.Debug($"Page {pageNumber} of {totalPages} (Total: {totalRecords} records)");

Running Queries in System Mode

Execute queries with administrator privileges using extension methods that end with AsAdmin.

Query with System Mode:

// Execute query with system privileges
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .ToListAsAdmin();

// Get first record with system privileges
var account = Database.Query<Account>()
    .Where(f => f.Name == "Partner Company")
    .FirstAsAdmin();

// Count with system privileges
var totalCount = Database.Query<Account>()
    .CountAsAdmin();

When to Use System Mode:

• Automated processes that must run regardless of user permissions
• System-level integrations
• Administrative reporting requiring access to all records

When NOT to Use System Mode:

• Normal controller actions
• User-facing queries
• Standard data retrieval where user permissions should apply

Retrieving Single Records

Use Database.Retrieve<T>() or the .First() extension method to get a single record.

Retrieve by ID:

// Retrieve by ID
var account = Database.Retrieve<Account>("001R000000abc123");

if (account != null)
{
    SystemInfo.Debug($"Found: {account.Name}");
}
else
{
    SystemInfo.Debug("Account not found");
}

First Extension Method:

// Get first matching record (or null)
var account = Database.Query<Account>()
    .Where(f => f.Name == "Partner Company")
    .First();

// Get first record with system privileges
var account = Database.Query<Account>()
    .Where(f => f.Name == "Partner Company")
    .FirstAsAdmin();

Retrieve with DatabaseOptions:

// Retrieve in system mode using DatabaseOptions
var account = Database.Retrieve<Account>(
    accountId,
    new DatabaseOptions { SystemMode = true }
);

Non-Generic Retrieve:

// Automatically determines entity type from ID
var record = Database.Retrieve(recordId);

if (record != null)
{
    SystemInfo.Debug($"Found record: {record.Id}");
}

Query All Including Deleted Records

Use Database.QueryAll<T>() to include soft-deleted records in results.

Query All Records:

// Include active and deleted records
var allAccounts = Database.QueryAll<Account>()
    .Where(f => f.Type == "Partner")
    .ToList();

// Filter for only deleted records
var deletedAccounts = Database.QueryAll<Account>()
    .Where(f => f.Type == "Partner" && f.IsDeleted == true)
    .ToList();

Find Deleted Record to Restore:

var deletedContact = Database.QueryAll<Contact>()
    .Where(f => f.Email == "restore@example.com" && f.IsDeleted == true)
    .First();

if (deletedContact != null)
{
    Database.Restore(deletedContact.Id);
    SystemInfo.Debug("Contact restored");
}

Advanced Query Patterns

Dynamic Query Building:

public List<Account> SearchAccounts(string type, string industry, decimal? minRevenue)
{
    var query = Database.Query<Account>();
    
    // Add filters conditionally
    if (!string.IsNullOrEmpty(type))
        query.Where(f => f.Type == type);
    
    if (!string.IsNullOrEmpty(industry))
        query.Where(f => f.Industry == industry);
    
    if (minRevenue.HasValue)
        query.Where(f => f.AnnualRevenue >= minRevenue.Value);
    
    return query.OrderBy(f => f.Name).ToList();
}

Select Specific Fields:

// Select only needed fields for display
var accountList = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Select(f => new { f.Id, f.Name, f.Type })
    .ToList();

// Display list
return Json(accountList, true);

Aggregate Queries with QueryFlex

Database.QueryFlex() executes aggregate queries with GROUP BY support, returning dynamic results for scenarios where the data structure is not predictable.

When to Use QueryFlex:

• Performing aggregate calculations (COUNT, SUM, AVG, MAX, MIN)
• Grouping data by one or more fields
• Building reports with dynamic columns
• Scenarios where the result structure varies based on query

Query Syntax:

QueryFlex uses SQL-like MEQL (Magentrix Entity Query Language) syntax with the following structure:

SELECT <fields>, <aggregates> FROM <Entity> [WHERE <conditions>] [GROUP BY <fields>] ORDER BY <field>

Important Requirements:

• ✅ Aggregate functions MUST have aliases (e.g., COUNT(Id) as Total)
• ✅ GROUP BY queries MUST include ORDER BY clause
• ✅ Parameters use @0, @1, etc. placeholders
• ✅ Field names in results are case-sensitive
• ✅ Can GROUP BY related entity fields (e.g., Account.Type)

Supported Aggregate Functions:

For a complete list of supported aggregates, see the Magentrix SQL Reference.

Common aggregates include:

• COUNT(field) - Count non-null values
• SUM(field) - Sum numeric values
• AVG(field) - Average of numeric values
• MAX(field) - Maximum value
• MIN(field) - Minimum value

Example 1 - Simple Aggregate:

// Count total partners
var result = Database.QueryFlex(
    "SELECT COUNT(Id) as Total FROM Account WHERE Type = @0",
    "Partner"
);

// Result is a single dynamic object
var totalCount = result.Total;
SystemInfo.Debug($"Total partners: {totalCount}");

Example 2 - GROUP BY Single Field:

// Count accounts by type
var results = Database.QueryFlex(
    "SELECT Type, COUNT(Id) as Total FROM Account GROUP BY Type ORDER BY Type"
);

// Result is List<dynamic> with one item per group
foreach (var item in results)
{
    SystemInfo.Debug($"{item.Type}: {item.Total} accounts");
}

// Output:
// Customer: 32 accounts
// Partner: 15 accounts
// Vendor: 8 accounts

Example 3 - GROUP BY Related Entity Field:

// Group contacts by account type
var results = Database.QueryFlex(
    "SELECT Account.Type, COUNT(Id) as Total FROM Contact GROUP BY Account.Type ORDER BY Account.Type"
);

foreach (var item in results)
{
    SystemInfo.Debug($"Contacts at {item.Type} accounts: {item.Total}");
}

Example 4 - Multiple Aggregates:

// Revenue analysis by industry
var results = Database.QueryFlex(
    "SELECT Industry, COUNT(Id) as AccountCount, SUM(AnnualRevenue) as TotalRevenue, AVG(AnnualRevenue) as AvgRevenue FROM Account WHERE Type = @0 GROUP BY Industry ORDER BY TotalRevenue DESC",
    "Partner"
);

foreach (var item in results)
{
    SystemInfo.Debug($"{item.Industry}:");
    SystemInfo.Debug($"  Accounts: {item.AccountCount}");
    SystemInfo.Debug($"  Total Revenue: ${item.TotalRevenue}");
    SystemInfo.Debug($"  Avg Revenue: ${item.AvgRevenue}");
}

Example 5 - Filtered Aggregates:

// Count high-value opportunities by stage
var results = Database.QueryFlex(
    "SELECT Stage, COUNT(Id) as OpportunityCount, SUM(Amount) as TotalAmount FROM Opportunity WHERE Amount > @0 AND CloseDate >= @1 GROUP BY Stage ORDER BY TotalAmount DESC",
    50000,
    DateTime.UtcNow
);

foreach (var item in results)
{
    SystemInfo.Debug($"{item.Stage}: {item.OpportunityCount} opportunities, ${item.TotalAmount} total");
}

Example 6 - Date-Based Grouping:

// Accounts created by year and month
var results = Database.QueryFlex(
    "SELECT YEAR(CreatedOn) as Year, MONTH(CreatedOn) as Month, COUNT(Id) as Total FROM Account GROUP BY YEAR(CreatedOn), MONTH(CreatedOn) ORDER BY Year DESC, Month DESC"
);

foreach (var item in results)
{
    SystemInfo.Debug($"{item.Year}-{item.Month:00}: {item.Total} accounts created");
}

Accessing Dynamic Results:

// Single aggregate result
var result = Database.QueryFlex("SELECT COUNT(Id) as totalCount FROM Account");
var total = result.totalCount;  // Access property directly

// GROUP BY results
var results = Database.QueryFlex(
    "SELECT Type, COUNT(Id) as Total FROM Account GROUP BY Type ORDER BY Type"
);

// Results is IEnumerable, can be converted to List
var resultList = results.ToList();
SystemInfo.Debug($"Found {resultList.Count} groups");

// Access first result
if (results.Any())
{
    var first = results.First();
    SystemInfo.Debug($"First group: {first.Type} = {first.Total}");
}

// Iterate all results
foreach (var item in results)
{
    var type = item.Type;
    var count = item.Total;
}

Error Handling with QueryFlex:

try
{
    var results = Database.QueryFlex(
        "SELECT Type, COUNT(Id) as Total FROM Account GROUP BY Type ORDER BY Type"
    );
    
    foreach (var item in results)
    {
        SystemInfo.Debug($"{item.Type}: {item.Total}");
    }
}
catch (Exception ex)
{
    SystemInfo.Debug($"QueryFlex error: {ex.Message}");
}

Important Notes:

// ❌ WRONG - No alias
var result = Database.QueryFlex("SELECT COUNT(Id) FROM Account");

// ✅ CORRECT - Has alias
var result = Database.QueryFlex("SELECT COUNT(Id) as totalCount FROM Account");

// ❌ WRONG - Missing ORDER BY
var results = Database.QueryFlex("SELECT Type, COUNT(Id) as Total FROM Account GROUP BY Type");

// ✅ CORRECT - Has ORDER BY
var results = Database.QueryFlex("SELECT Type, COUNT(Id) as Total FROM Account GROUP BY Type ORDER BY Type");

var result = Database.QueryFlex("SELECT COUNT(Id) as totalCount FROM Account");
var total = result.totalCount;  // Correct
var total = result.TotalCount;  // ERROR - case mismatch

Use Cases for QueryFlex:

Dashboard Metrics:

// Get summary statistics for dashboard
var stats = Database.QueryFlex(
    "SELECT COUNT(Id) as TotalAccounts, SUM(AnnualRevenue) as TotalRevenue, AVG(AnnualRevenue) as AvgRevenue FROM Account WHERE IsActive = @0",
    true
);

SystemInfo.Debug($"Total Accounts: {stats.TotalAccounts}");
SystemInfo.Debug($"Total Revenue: ${stats.TotalRevenue}");
SystemInfo.Debug($"Average Revenue: ${stats.AvgRevenue}");

Sales Pipeline Analysis:

// Analyze pipeline by stage
var pipeline = Database.QueryFlex(
    "SELECT Stage, COUNT(Id) as OpportunityCount, SUM(Amount) as TotalValue FROM Opportunity WHERE IsClosed = @0 GROUP BY Stage ORDER BY TotalValue DESC",
    false
);

foreach (var stage in pipeline)
{
    SystemInfo.Debug($"{stage.Stage}: {stage.OpportunityCount} opps, ${stage.TotalValue}");
}

Activity Reports:

// User activity summary
var activity = Database.QueryFlex(
    "SELECT OwnerId, COUNT(Id) as TaskCount FROM Task WHERE Status = @0 AND DueDate >= @1 GROUP BY OwnerId ORDER BY TaskCount DESC",
    "Open",
    DateTime.UtcNow
);

QueryFlex vs Regular Query:

When to Use Each:

Use QueryFlex when:

• You need aggregate calculations (COUNT, SUM, AVG, etc.)
• You need to GROUP BY fields
• Building dynamic reports or dashboards
• Result structure varies based on query

Use Query<T>() when:

• Retrieving entity records
• Need strongly-typed results
• Need LINQ method chaining
• Standard filtering and sorting without aggregates

Best Practices for Querying

✅ Always call .ToList() to execute queries

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

// Execute query
var accounts = query.ToList();

✅ Use Limit() to prevent excessive data retrieval

var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Limit(100)  // Prevent loading thousands of records
    .ToList();

✅ Use Count() instead of querying all records to count

// ✅ GOOD
var count = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Count();

// ❌ BAD - Retrieves all records just to count
var count = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .ToList()
    .Count();

✅ Method chaining order doesn't matter

// You can chain methods in any order
var accounts = Database.Query<Account>()
    .OrderBy(f => f.Name)
    .Where(f => f.Type == "Partner")
    .Limit(100)
    .ToList();

// This produces the same result
var accounts = Database.Query<Account>()
    .Where(f => f.Type == "Partner")
    .Limit(100)
    .OrderBy(f => f.Name)
    .ToList();

❌ Never query in loops

// ❌ BAD
foreach (var id in accountIds)
{
    var account = Database.Retrieve<Account>(id);  // Multiple queries
}

// ✅ GOOD
var accounts = Database.Query<Account>()
    .Where(f => accountIds.Contains(f.Id))
    .ToList();