Reading data

CaeriusNet exposes four read methods on ICaeriusNetDbContext. Each method returns a different shape so you can choose the contract that fits your application.

Prerequisites

• A registered ICaeriusNetDbContext (via CaeriusNetBuilder — see Installation & Setup)
• A StoredProcedureParameters value built with StoredProcedureParametersBuilder
• A DTO implementing ISpMapper<T> (typically generated with [GenerateDto])

Choosing the right method

Method	Return type	When to use
QueryAsIEnumerableAsync<T>	IEnumerable<T>	Materialized sequence for LINQ pipelines
QueryAsReadOnlyCollectionAsync<T>	ReadOnlyCollection<T>	Public APIs that expose an immutable contract
QueryAsImmutableArrayAsync<T>	ImmutableArray<T>	Frozen, struct-backed, allocation-efficient data
FirstQueryAsync<T>	T?	Single-row lookups (returns null when empty)

Allocation vs. ergonomics

• IEnumerable<T> keeps the door open for downstream LINQ but exposes an enumerator allocation.
• ReadOnlyCollection<T> materializes a List<T> and wraps it — predictable, indexable, immutable.
• ImmutableArray<T> is a struct over an array — best for cached, hot-path data passed by value.

Repository setup

using CaeriusNet.Abstractions;
using CaeriusNet.Builders;
using CaeriusNet.Mappers;
using Microsoft.Data.SqlClient;
using System.Data;

public sealed record UserRepository(ICaeriusNetDbContext DbContext)
    : IUserRepository
{
    // ... method implementations below
}

QueryAsIEnumerableAsync

Returns IEnumerable<T>. Empty result sets return an empty sequence. Use this shape when callers want LINQ operations and do not need an indexable collection contract.

public async Task<IEnumerable<UserDto>> GetUsersOlderThanAsync(
    byte age, CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder("dbo", "sp_GetUsers_By_Age", ResultSetCapacity: 450)
        .AddParameter("Age", age, SqlDbType.TinyInt)
        .Build();

    return await DbContext.QueryAsIEnumerableAsync<UserDto>(sp, ct);
}

QueryAsReadOnlyCollectionAsync

Returns a ReadOnlyCollection<T>, empty when the stored procedure returns no rows. Ideal for public APIs where the caller should see an immutable contract.

public async Task<ReadOnlyCollection<UserDto>> GetAllUsersAsync(CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder("Users", "usp_Get_All_Users", 250)
        .AddFrozenCache("users:all:frozen")
        .Build();

    return await DbContext.QueryAsReadOnlyCollectionAsync<UserDto>(sp, ct);
}

QueryAsImmutableArrayAsync

Returns ImmutableArray<T> — a struct wrapper over an array. Ideal for frozen data sets that will be cached or passed around without mutation.

public async Task<ImmutableArray<UserDto>> GetUsersImmutableAsync(CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder("Users", "usp_Get_All_Users", 250)
        .Build();

    return await DbContext.QueryAsImmutableArrayAsync<UserDto>(sp, ct);
}

FirstQueryAsync

Returns T? — reads only the first row and returns null if the result set is empty. Use it for single-entity lookups.

public async Task<UserDto?> GetUserByGuidAsync(Guid guid, CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder("dbo", "sp_GetUser_By_Guid")
        .AddParameter("Guid", guid, SqlDbType.UniqueIdentifier)
        .Build();

    return await DbContext.FirstQueryAsync<UserDto>(sp, ct);
}

Result-set capacity

The third constructor argument of StoredProcedureParametersBuilder is resultSetCapacity. Use it to tell CaeriusNet the expected row count for materialized result sets.

// Expecting ~250 rows — pre-allocate to skip List<T> resizing
new StoredProcedureParametersBuilder("dbo", "usp_Get_All_Users", ResultSetCapacity: 250);

Capacity tuning

Pick a reasonable upper bound. Too low triggers extra allocations; too high wastes memory. For write operations (no result set), the default 1 is fine — capacity is ignored.

CancellationToken

Every read method takes a CancellationToken. Propagate it from your controller, hosted service, or caller so the SQL command is cancelled if the request is aborted:

public async Task<IEnumerable<UserDto>> GetAsync(CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder("dbo", "usp_Get_All_Users").Build();
    return await DbContext.QueryAsIEnumerableAsync<UserDto>(sp, ct);
}

Caching reads

Add per-call caching to any read by chaining a cache method on the builder. See Caching for the full guide.

var sp = new StoredProcedureParametersBuilder("dbo", "usp_Get_All_Users", 250)
    .AddInMemoryCache("users:all", TimeSpan.FromMinutes(2))
    .Build();

On a cache hit, no SQL command is executed and no DB span is created — only the caerius.cache.lookups{hit=true} counter ticks.

---

Next: Writing data - execute INSERT, UPDATE, DELETE, and scalar returns.