Operations · 2 min read

QueryMultiple

This method queries data as multiple result sets from the table based on the given target types.

Code Snippets

The following example fetches a single Customer row and all related Order rows.

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.QueryMultiple<Customer, Order>(p => p.Id == 10045,
        o => o.PersonId == 10045);
    var customer = result.Item1.FirstOrDefault();
    var orders = result.Item2.AsList();

    // Process 'customer' and 'orders' here
}

Returns a Tuple<T1, .., T7> object. Up to 7 types are supported. Each tuple item is an IEnumerable of the corresponding generic type.

Table Hints

Pass table hints via the hints arguments.

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.QueryMultiple<Customer, Order>(p => p.Id == 10045,
        o => o.PersonId == 10045,
        hints1: "WITH (NOLOCK)",
        hints2: "WITH (NOLOCK)");
    var customer = result.Item1.FirstOrDefault();
    var orders = result.Item2.AsList();

    // Process 'customer' and 'orders' here
}

Or use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.QueryMultiple<Customer, Order>(p => p.Id == 10045,
        o => o.PersonId == 10045,
        hints1: SqlServerTableHints.TabLock,
        hints2: SqlServerTableHints.TabLock);
    var customer = result.Item1.FirstOrDefault();
    var orders = result.Item2.AsList();

    // Process 'customer' and 'orders' here
}

Ordering the Results

Pass an array of OrderField objects in the orderBy argument.

using (var connection = new SqlConnection(connectionString))
{
    var orderBy = OrderField.Parse(new
    {
        OrderDateUtc = RepoDb.Enumerations.Order.Descending
    });
    var result = connection.QueryMultiple<Customer, Order>(p => p.Id == 10045,
        o => o.PersonId == 10045,
        orderBy2: orderBy);
    var customer = result.Item1.FirstOrDefault();
    var orders = result.Item2.AsList();

    // Process 'customer' and 'orders' here
}

Filtering the Results

Pass a value in the top argument to limit the number of rows returned.

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.QueryMultiple<Customer, Order>(p => p.Id == 10045,
        o => o.PersonId == 10045,
        top2: 100);
    var customer = result.Item1.FirstOrDefault();
    var orders = result.Item2.AsList();

    // Process 'customer' and 'orders' here
}