Multiple Query

This feature allows you to fetch multiple result sets in a single database call. Both ExecuteQueryMultiple and QueryMultiple operations are provided for this purpose.

ExecuteQueryMultiple accepts a raw SQL statement, while QueryMultiple accepts a Linq-based query expression and generates the SQL statement automatically.

Both operations abstract the Read() and NextResult() methods of the DbDataReader object.

Type of Return Types

ExecuteQueryMultiple returns an instance of QueryMultipleExtractor, which provides control over how result sets are extracted. Execution is deferred until explicit calls to the Extract() or Scalar() methods are made.

QueryMultiple returns a Tuple instance with a maximum of 7 generic types, matching the maximum defined for Tuple. The item properties of the tuple correspond to the order of the generic types passed during the call.

Hints

For ExecuteQueryMultiple, hints are written directly in the SQL statement. For QueryMultiple, each query in the execution has a corresponding hints argument.

var (customers, orders) = connection.QueryMultiple<Customer, Order>(c => c.Id == customerId, // Customer
    o => o.CustomerId == customerId, // Order
    hints1: "WITH (NOLOCK)", // Hints for Customer
    hints2: "WITH (NOLOCK)"); // Hints for Order

Use the SqlServerTableHints class to simplify hint values:

var (customers, orders) = connection.QueryMultiple<Customer, Order>(c => c.Id == customerId, // Customer
    o => o.CustomerId == customerId, // Order
    hints1: SqlServerTableHints.NoLock, // Hints for Customer
    hints2: SqlServerTableHints.NoLock); // Hints for Order

Ordering

For ExecuteQueryMultiple, ordering is written directly into the SQL statement. For QueryMultiple, each query has a corresponding orderBy argument via the OrderField class.

var (customers, orders) = connection.QueryMultiple<Customer, Order>(c => c.Id == customerId, // Customer
    o => o.CustomerId == customerId, // Order
    orderBy1: OrderField.Ascending<Customer>(c => c.DateInsertedUtc), // Ordering for Customer
    orderBy2: OrderField.Ascending<Order>(o => o.OrderDateUtc)); // Ordering for Order

Filtering

For ExecuteQueryMultiple, filtering is written using TOP or LIMIT in the SQL statement. For QueryMultiple, each query has a corresponding top argument.

var (customers, orders) = connection.QueryMultiple<Customer, Order>(c => c.Id == customerId, // Customer
    o => o.CustomerId == customerId, // Order
    top2: 10); // Filtering for Order

Single Parent with Multiple Children

For raw SQL, use the ExecuteQueryMultiple method:

using (var connection = new SqlConnection(connectionString))
{
    using (var extractor = connection.ExecuteQueryMultiple(@"SELECT * FROM [dbo].[Customer] WITH (NOLOCK) WHERE [Id] = @CustomerId;
        SELECT * FROM [dbo].[Order] WITH (NOLOCK) WHERE [CustomerId] = @CustomerId;",
        new { CustomerId = 10045 }))
    {
        // Extract the first result
        var customer = extractor.Extract<Customer>().FirstOrDefault();

        // Extract the second result
        var orders = extractor.Extract<Order>().AsList();

        // Set the child orders
        customer.Orders = orders;

        // Process the 'customer' here
    }
}

For the fluent method, use QueryMultiple:

using (var connection = new SqlConnection(connectionString))
{
    // The target key
    var customerId = 10045;

    // Execution by passing the order of entity
    var (customers, orders) = connection
        .QueryMultiple<Customer, Order>(c => c.Id == customerId, // Customer
            o => o.CustomerId == customerId, // Order
            hints1: SqlServerTableHints.NoLock, // Hints for Customer
            hints2: SqlServerTableHints.NoLock); // Hints for Order

    // Extract the customer
    var customer = customers.FirstOrDefault();

    // Extract the orders
    customer.Orders = orders.AsList();

    // Process the 'customer' here
}

Querying Multiple Parent and Multiple Children

For raw SQL, use the ExecuteQueryMultiple method:

using (var connection = new SqlConnection(connectionString))
{
    using (var extractor = connection.ExecuteQueryMultiple(@"SELECT [Id], [Name] FROM [dbo].[Customer] WITH (NOLOCK) WHERE [Id] IN (@Keys);
        SELECT [Id], [CustomerId], [ProductId], [Price], [Quantity], [OrderDateUtc] FROM [dbo].[Order] WITH (NOLOCK) WHERE [CustomerId] IN (@Keys);",
        new { Keys = new [] { 10045, ..., 11211 }))
    {
        // Extract the first result
        var customers = extractor.Extract<Customer>().AsList();

        // Extract the second result
        var orders = extractor.Extract<Order>();

        // Iterate the customers and map all the orders
        customers.ForEach(
            c => c.Orders = orders.Where(o => o.CustomerId == c.Id).AsList());

        // Process the 'customers' here
    }
}

For the fluent method, use QueryMultiple:

using (var connection = new SqlConnection(connectionString))
{
    // List of keys to query
    var keys = new [] { 10045, ..., 11211 };

    // Execution by passing the order of types
    var (customers, orders) = connection
        .QueryMultiple<Customer, Order>(c => keys.Contains(c.Id), o => keys.Contains(o.CustomerId),
            hints1: SqlServerTableHints.NoLock, hints2: SqlServerTableHints.NoLock);

    // Iterate the customers and map all the orders
    customers.AsList().ForEach(
        c => c.Orders = orders.Where(o => o.CustomerId == c.Id).AsList());

    // Process the 'customers' here
}

Please visit our Multiple Resultsets reference implementation page for more details.