ReadToLists Multi Result Sets - lobodava/artisan-orm GitHub Wiki

A stored procedure that returns several select result sets is faster than firing several procedures back-to-back: one round trip, one connection acquisition, one query plan compilation. ReadToLists<T1, T2[, T3[, T4]]> reads such a procedure into a typed C# tuple of lists.

Added in v4 — see What's New in v4.

When to use

Anywhere a single round-trip should pull more than one shape:

  • A page header plus its child rows (orders + line items, posts + comments).
  • A list plus its lookup tables (records + record types).
  • A list plus its cross-reference (users + user-roles + roles).

For graph assembly across the returned lists, pair with MergeJoin.

Two-result-set example

T-SQL — one procedure, two select statements:

create procedure dbo.GetUsersAndRoles
as
begin
    set nocount on;

    select Id, Login, Name, Email from dbo.Users    order by Id;
    select Id, Code, Name         from dbo.Roles    order by Id;
end;

C# — ReadToLists returns a tuple deconstructable in one line:

public class UserRepository : RepositoryBase
{
    public UserRepository(string connStr) : base(connStr) { }

    public (IList<User> users, IList<Role> roles) GetUsersAndRoles() =>
        ReadToLists<User, Role>("dbo.GetUsersAndRoles");

    public Task<(IList<User>, IList<Role>)> GetUsersAndRolesAsync(CancellationToken ct) =>
        ReadToListsAsync<User, Role>("dbo.GetUsersAndRoles", ct);
}

// Caller
var (users, roles) = repo.GetUsersAndRoles();

Three and four result sets

Same idea with three- and four-tuple variants:

public (IList<GrandRecord>, IList<Record>, IList<RecordType>) GetThreeLists() =>
    ReadToLists<GrandRecord, Record, RecordType>("dbo.GetThreeListsOfRecords");

public (IList<GrandRecord>, IList<Record>, IList<ChildRecord>, IList<RecordType>)
    GetFourLists() =>
    ReadToLists<GrandRecord, Record, ChildRecord, RecordType>("dbo.GetFourListsOfRecords");

Mapper or createFunc?

By default each Tn is read with its registered [MapperFor] mapper. For one-off shapes that don't have a mapper, pass explicit createFunc lambdas — only available on the command-level extension method (not the RepositoryBase shortcut):

using var cmd = repo.CreateCommand("dbo.GetIdsAndNames");

var (ids, names) = cmd.ReadToLists<int, string>(
    createFunc1: dr => dr.GetInt32(0),
    createFunc2: dr => dr.GetString(0));

Async + CancellationToken

Every overload has an Async sibling. RepositoryBase exposes them as:

Task<(IList<T1>, IList<T2>)>
ReadToListsAsync<T1, T2>(string sql, CancellationToken cancellationToken = default,
                        params SqlParameter[] sqlParameters);

Task<(IList<T1>, IList<T2>)>
ReadToListsAsync<T1, T2>(string sql, Action<SqlCommand> action,
                        CancellationToken cancellationToken = default);

The T1, T2, T3 and T1, T2, T3, T4 overloads follow the same shape.

Combining with MergeJoin for graph assembly

A common pattern: read parent and child tables in one trip, then wire up navigation properties without a GroupJoin Dictionary allocation.

-- Stored procedure must return both lists sorted by the join key.
create procedure dbo.GetGrandRecordsWithRecords
as
begin
    set nocount on;

    select * from dbo.GrandRecords order by Id;
    select * from dbo.Records      order by GrandRecordId;
end;
var (grandRecords, records) = repo.GetByCommand(cmd =>
{
    cmd.UseProcedure("dbo.GetGrandRecordsWithRecords");
    return cmd.ReadToLists<GrandRecord, Record>();
});

grandRecords.MergeJoin(
    gr => { gr.Records ??= new List<Record>(); },
    records,
    (gr, r) => gr.Id == r.GrandRecordId,
    (gr, r) => { r.GrandRecord = gr; gr.Records.Add(r); });

See MergeJoin for the full algorithm and three-level graph examples.

Implementation note

Internally ReadToLists opens one SqlDataReader with CommandBehavior.Default (so multiple result sets are reachable) and calls NextResult() between reads. The first Tn − 1 reads use getNextResult: true; the last uses getNextResult: false.

See also:

⚠️ **GitHub.com Fallback** ⚠️