Retrieve vs Search

Velocity provides three methods for executing queries and binding results, each optimized for different scenarios:

• Retrieve<T>() — executes, binds and disposes in one call, no using needed
• Retrieve - Returns a disconnected ResultSet with all results loaded into memory
• Search - Returns a connected ResultReader that streams results as you iterate

Each method has an async equivalent: RetrieveAsync<T>(), RetrieveAsync(), and SearchAsync(). See Async Query Execution below.

Retrieve (Disconnected)

Use Retrieve when you need all results in memory at once.

Table users = schema["users"];

Query query = new Query()
    .Select([users["username"], users["email"]])
    .From(users);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // All data is loaded into memory
    // Connection is closed immediately after this call

    Console.WriteLine($"Total rows: {results.Count}");

    // Iterate multiple times
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("username"));
    }

    // Access by index
    string firstName = results[0].GetFieldString("username");    
}

Advantages

• Immediate row count with results.Count
• Random access by index: results[5]
• Multiple iterations over the same data
• In-memory filtering and sorting
• Connection closes immediately - no resource holding
• Returns IList<T> when using data binding
• Re-bindable — call BindResults<T>() multiple times with different types

Disadvantages

• Loads all data into memory - can be problematic with large datasets
• Slower initial load for very large datasets

Retrieve<T> (One-Shot Binding)

Use Retrieve<T>() when you need a single binding and want the simplest possible call with no disposal management:

// One-shot: no using required
using (Manager manager = new Manager(connection))
{
    IList<User> users = manager.Retrieve<User>(query, BindingType.SnakeCase);
    // ResultSet was created and disposed internally
}

Internally, Retrieve<T>() calls Retrieve(), binds with BindResults<T>(), then disposes the ResultSet before returning. The tradeoff is that you can only bind once and to one type — if you need multiple bindings on the same data, call Retrieve() directly and bind the ResultSet as many times as needed.

Search (Connected)

Use Search for streaming large result sets or when you only need forward-only iteration.

Table logs = schema["logs"];

Query query = new Query()
    .Select([logs["timestamp"], logs["message"]])
    .From(logs)
    .Where(new Criterion<string>(logs["level"], ConditionalType.Equals, "ERROR"));

using (Manager manager = new Manager(connection))
using (ResultReader results = manager.Search(query))
{
    // Data is streamed row by row
    // Connection stays open during iteration

    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("message"));
        // Process each row as it arrives from the database
    }

    // Cannot iterate again - must re-execute query
}

Advantages

• Low memory footprint - processes one row at a time
• Fast for large datasets - no upfront loading delay
• Ideal for processing and streaming - export, transform, forward data
• Returns IEnumerable<T> when using binding (lazy evaluation)

Disadvantages

• Forward-only - single iteration only
• No random access to specific rows
• No count without iterating all rows
• Connection must stay open during iteration

Decision Matrix

Scenario	Use
Simple one-type binding, no disposal needed	Retrieve<T>
Bind same results to multiple types	Retrieve
Large result sets, streaming	Search
Small result sets (< 1,000 rows)	Retrieve
Large result sets (> 10,000 rows)	Search
Need to know row count first	Retrieve
Processing and forwarding data	Search
Need to iterate multiple times	Retrieve
Building reports with totals	Retrieve
Streaming to API/file/database	Search
Need random access by index	Retrieve
Memory-constrained environment	Search
Want connection to close quickly	Retrieve

Examples

Retrieve: Building a Report

Table orders = schema["orders"];
Table customers = schema["customers"];

var join = new Join(orders, customers, "customer_id");

Expression totalRevenue = new Expression("total_revenue",
    new Aggregate(AggregateType.Sum, orders["total"]));

Expression orderCount = new Expression("order_count",
    new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All)));

Query query = new Query()
    .Select([customers["company_name"], orderCount, totalRevenue])
    .From(join)
    .GroupBy([customers["customer_id"], customers["company_name"]])
    .OrderBy(totalRevenue, OrderClauseType.Descending);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // Get totals across all customers
    decimal grandTotal = 0;
    int totalOrders = 0;

    foreach (Result row in results)
    {
        grandTotal += row.GetFieldDecimal("total_revenue");
        totalOrders += row.GetFieldInteger("order_count");
    }

    Console.WriteLine($"Total customers: {results.Count}");
    Console.WriteLine($"Total orders: {totalOrders}");
    Console.WriteLine($"Grand total revenue: ${grandTotal:N2}");
    Console.WriteLine();

    // Iterate again to display details
    Console.WriteLine("Top 10 customers:");
    foreach (Result row in results.Take(10))
    {
        string company = row.GetFieldString("company_name");
        int orders = row.GetFieldInteger("order_count");
        decimal revenue = row.GetFieldDecimal("total_revenue");

        Console.WriteLine($"  {company}: {orders} orders, ${revenue:N2}");
    }
}

Search: Processing Large Log Files

Table logs = schema["logs"];

Filter filter = new Filter([
    new Criterion<DateTime>(logs["timestamp"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-7)),
    new BooleanItem(BooleanType.And),
    new Criterion<string>(logs["level"], ConditionalType.Equals, "ERROR")
]);

Query query = new Query()
    .Select([logs["timestamp"], logs["message"], logs["stack_trace"]])
    .From(logs)
    .Where(filter)
    .OrderBy(logs["timestamp"], OrderClauseType.Descending);

// Stream results to avoid loading millions of rows into memory
using (Manager manager = new Manager(connection))
using (ResultReader reader = manager.Search(query))
using (StreamWriter writer = new StreamWriter("errors.txt"))
{
    int count = 0;

    foreach (Result row in reader)
    {
        DateTime timestamp = row.GetFieldDateTime("timestamp");
        string message = row.GetFieldString("message");
        string stackTrace = row.GetFieldString("stack_trace");

        // Write each error to file as it arrives
        writer.WriteLine($"[{timestamp:yyyy-MM-dd HH:mm:ss}]");
        writer.WriteLine(message);
        writer.WriteLine(stackTrace);
        writer.WriteLine();

        count++;

        // Optional: limit processing
        if (count >= 10000) break;
    }

    Console.WriteLine($"Exported {count} error logs");
}

Search: ETL Pipeline

public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

Table users = sourceSchema["users"];

Query query = new Query()
    .SelectAll(users)
    .Where(new Criterion<DateTime>(
        users["created_at"],
        ConditionalType.GreaterThan,
        lastSyncTimestamp));

// Stream from source database to destination
using (Manager sourceManager = new Manager(sourceConnection))
using (Manager destManager = new Manager(destinationConnection))
using (ResultReader reader = sourceManager.Search(query))
{
    Table destUsers = destSchema["users"];
    int processed = 0;

    // Bind results to objects with lazy evaluation
    IEnumerable<User> users = reader.BindResults<User>(BindingType.SnakeCase);

    foreach (User user in users)
    {
        // Transform each user as it arrives
        Record<User> record = new Record<User>(destUsers, user, BindingType.SnakeCase);
        destManager.AddRecord(record);

        processed++;
        if (processed % 1000 == 0)
        {
            Console.WriteLine($"Processed {processed} users...");
        }
    }

    Console.WriteLine($"Total users synced: {processed}");
}

Best Practices

Use Retrieve<T> When

• You need a single binding and want no disposal management
• One type, one call, done

Use Retrieve When

• Building in-memory reports or dashboards
• Need to calculate totals or aggregates from the result set
• Result set is small (< 10,000 rows typically)
• Need to iterate over results multiple times
• Need random access to specific rows
• Want the database connection to close immediately
• Need to bind the same result set to more than one type

Use Search When

• Processing large datasets (> 10,000 rows)
• Streaming data to another destination (file, API, database)
• Performing ETL (Extract, Transform, Load) operations
• Memory is constrained
• Only need forward-only iteration
• Processing rows one at a time

Avoid Common Pitfalls

Don't use Retrieve for very large datasets:

// BAD: Loading 10 million rows into memory
Query query = new Query().SelectAll(hugeTable);
using (ResultSet results = manager.Retrieve(query))  // Out of memory!
{
    foreach (Result row in results)
    {
        ProcessRow(row);
    }
}

// GOOD: Stream large datasets
using (ResultReader results = manager.Search(query))
{
    foreach (Result row in results)
    {
        ProcessRow(row);
    }
}

Don't try to iterate Search results multiple times:

// BAD: Can only iterate once
using (ResultReader results = manager.Search(query))
{
    foreach (Result row in results) { /* First pass */ }
    foreach (Result row in results) { /* ERROR: Already consumed! */ }
}

// GOOD: Use Retrieve if you need multiple passes
using (ResultSet results = manager.Retrieve(query))
{
    foreach (Result row in results) { /* First pass */ }
    foreach (Result row in results) { /* Second pass works! */ }
}

Don't iterate a Search binding outside the using block:

IEnumerable<User> users;

// BAD: IEnumerable captured but ResultReader already disposed
using (ResultReader reader = manager.Search(query))
{
    users = reader.BindResults<User>(BindingType.SnakeCase);
}
foreach (User user in users) { /* ObjectDisposedException! */ }

// GOOD: Consume the IEnumerable inside the using block
using (ResultReader reader = manager.Search(query))
{
    foreach (User user in reader.BindResults<User>(BindingType.SnakeCase))
    {
        ProcessUser(user);
    }
}

Async Query Execution

Every read method has an async twin. Use async methods in web/API hosts and any context where releasing the calling thread during the database round-trip matters.

Sync	Async	Returns
Retrieve<T>(query)	RetrieveAsync<T>(query, ct)	Task<IList<T>>
Retrieve(query)	RetrieveAsync(query, ct)	Task<ResultSet>
Search(query)	SearchAsync(query, ct)	Task<ResultReader>

Thread release by provider

async + await releases the calling thread back to the thread pool during the network/disk round-trip. The benefit is real on:

• SQL Server, PostgreSQL, MySQL, Oracle — the ADO.NET provider performs a genuine non-blocking I/O wait.

SQLite, DB2, and Teradata fall back to a completed Task wrapper around the sync path (no thread is released), but the call is still safe and correct — callers using those databases lose no functionality by writing async code.

RetrieveAsync — disconnected async

RetrieveAsync returns a Task<ResultSet>. Disposal of the ResultSet is still synchronous (using):

Table users = schema["users"];
Query query = new Query().SelectAll(users);

using (Manager manager = new Manager(connection))
using (ResultSet results = await manager.RetrieveAsync(query))
{
    Console.WriteLine($"Total rows: {results.Count}");

    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("username"));
    }
}

RetrieveAsync<T> — one-shot async binding

using (Manager manager = new Manager(connection))
{
    IList<User> users = await manager.RetrieveAsync<User>(query, BindingType.SnakeCase);
    // ResultSet created and disposed internally
}

SearchAsync — streaming async

SearchAsync returns a Task<ResultReader>. Because ResultReader implements IAsyncDisposable, use await using to dispose it:

Table logs = schema["logs"];
Query query = new Query()
    .Select([logs["timestamp"], logs["message"]])
    .From(logs)
    .Where(new Criterion<string>(logs["level"], ConditionalType.Equals, "ERROR"));

using (Manager manager = new Manager(connection))
await using (ResultReader reader = await manager.SearchAsync(query))
{
    // Iterate the reader synchronously — the async benefit was the open + first-read round-trip
    foreach (Result row in reader)
    {
        Console.WriteLine(row.GetFieldString("message"));
    }
}

Note

await using is required for ResultReader because it implements IAsyncDisposable. Using a plain using will compile but will not await the async close of the underlying data reader.

Async ETL pipeline

Table source = sourceSchema["events"];
Table dest = destSchema["events"];

Query query = new Query()
    .SelectAll(source)
    .Where(new Criterion<DateTime>(source["created_at"],
        ConditionalType.GreaterThan,
        lastSyncTimestamp));

using (Manager src = new Manager(sourceConnection))
using (Manager dst = new Manager(destConnection))
await using (ResultReader reader = await src.SearchAsync(query))
{
    int processed = 0;

    foreach (Event ev in reader.BindResults<Event>(BindingType.SnakeCase))
    {
        var record = new Record<Event>(dest, BindingType.SnakeCase);
        record.Bind(ev);
        await dst.AddRecordAsync(record);

        if (++processed % 1000 == 0)
        {
            Console.WriteLine($"Processed {processed}...");
        }
    }
}

Async Decision Matrix

Scenario	Use
Web/API handler — want thread back during DB call	Any *Async variant
Simple one-type binding, async	RetrieveAsync<T>
Bind same results to multiple types, async	RetrieveAsync
Streaming large result sets, async	SearchAsync
Small result sets, sync fine	Retrieve
Large result sets, sync fine	Search
Background job — thread release not critical	Either

---

See Also

• Basic Queries - Building SELECT queries
• Binding Results - Mapping results to C# objects
• Performance - Query optimization strategies