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

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"], "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);
    }
}

Table of Contents

Retrieve vs Search

Retrieve (Disconnected)

Advantages

Disadvantages

Retrieve<T> (One-Shot Binding)

Search (Connected)

Advantages

Disadvantages

Decision Matrix

Examples

Retrieve: Building a Report

Search: Processing Large Log Files

Search: ETL Pipeline

Best Practices

Use Retrieve<T> When

Use Retrieve When

Use Search When

Avoid Common Pitfalls

See Also