Querying Multiple Results With Dapper

You can execute multiple queries in a single SQL statement using the QueryMultiple method (if the database provider supports it). Once executed, you can map the returned results to multiple objects using methods such as Read<T>, ReadFirst<T>, ReadSingle<T> and more.

The Dapper QueryMultiple method allows you to select multiple results from a database query. This feature is very useful for selecting multiple result sets at once, thus avoiding unnecessary round trips to the database server.

  • The QueryMultiple method provides a simple way of fetching data stored in different tables or views with a single database query.
  • By using Dapper's QueryMultiple method, you can select multiple rows from different tables in one go, saving time and resources.
  • Dapper's QueryMultiple method allows you to map results from multiple queries into strongly typed collections that can be easily consumed within your application code.
  • Using Dapper QueryMultiple is an efficient way to select multiple results from a single database query.
  • It saves time and resources by avoiding unnecessary round trips to the database server and eliminates the need for complex SQL queries.
Method Description
QueryMultiple Execute multiple queries within a single database command and return a GridReader to map the results to multiple objects
QueryMultipleAsync Execute multiple queries within a single database command and return a GridReader to map the results to multiple objects asynchronously

After using the QueryMultiple or QueryMultipleAsync method, you can use the following methods with the results:

Method Description
Read, ReadAsync Returns an IEnumerable of dynamic types
Read<T>, ReadAsync<T> Returns an IEnumerable of the type specified by the T parameter
ReadFirst, ReadFirstAsync Returns the first row as a dynamic type
ReadFirst<T>, ReadFirstAsync<T> Returns the first row as an instance of the type specified by the T type parameter
ReadFirstOrDefault, ReadFirstOrDefaultAsync Returns the first row as a dynamic type or null if no results are returned
ReadFirstOrDefault<T>, ReadFirstOrDefaultAsync<T> Returns the first row as an instance of the type specified by the T type parameter or null if no results are returned
ReadSingle, ReadSingleAsync Use when only one row is expected to be returned. Returns a dynamic type
ReadSingle<T>, ReadSingleAsync<T> Use when only one row is expected to be returned. Returns an instance of the type specified by the T type parameter
ReadSingleOrDefault, ReadSingleOrDefaultAsync Use when zero or one row is expected to be returned. Returns a dynamic type or null
ReadSingleOrDefault<T>, ReadSingleOrDefaultAsync<T> Use when zero or one row is expected to be returned. Returns an instance of the type specified by the T type parameter or null
🚀 Make Dapper Up to 75x Faster

Stop inserting records one by one. Dapper Plus lets you insert thousands of entities up to 75x faster with high-performance bulk operations.

Discover Dapper Plus →

Dapper QueryMultiple

The following example demonstrates how to use Dapper QueryMultiple to execute multiple SQL statements in one query.

language-csharp
|
string sql = @"
SELECT * FROM [Order] WHERE OrderID = @orderID;
SELECT * FROM [OrderItem] WHERE OrderID = @orderID;
";

using (var multi = connection.QueryMultiple(sql, new {orderID = 1}))
{
   var order = multi.ReadFirst<Order>();
   var orderItems = multi.Read<OrderItem>().ToList();
 
   FiddleHelper.WriteTable("Order", new List<Order>() { order});
   FiddleHelper.WriteTable("OrderItem", orderItems);
}

Online Example

With this, we can execute multiple SQL statements in one query and read each result set from the GridReader object returned by the QueryMultiple method. That can help increase the performance of your application in cases where multiple queries need to be executed and their results combined.

Using Dapper QueryMultiple is a great way to simplify data access and ensure that your application runs quickly and efficiently. With its easy-to-use API, you can quickly set up complex queries and access the data you need.

Dapper QueryMultipleAsync

The asynchronous version of the QueryMultiple<T> method is QueryMultipleAsync<T>:

language-csharp
|
var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

string sql = @"
SELECT * FROM [Order] WHERE OrderID = @orderID;
SELECT * FROM [OrderItem] WHERE OrderID = @orderID;
";

using (var multi = await connection.QueryMultipleAsync(sql, new {orderID = 1}))
{
   var order = multi.ReadFirst<Order>();
   var orderItems = multi.Read<OrderItem>().ToList();
 
   FiddleHelper.WriteTable("Order", new List<Order>() { order});
   FiddleHelper.WriteTable("OrderItem", orderItems);
}

Online Example

FAQ

How to retrieve multiple results returned by a stored procedure in Dapper?

To retrieve multiple results returned by a stored procedure, you need to set the stored procedure name in the command text and specify the command type to CommandType.StoredProcedure. Then you can read each result by either mapping to an anonymous type or a strongly typed object:

using (var connection = new SqlConnection("connectionString"))
{
    connection.Open();
    using (var results = connection.QueryMultiple("MyStoredProcedure", commandType: CommandType.StoredProcedure))
    {
        // Anonymous Type
        var resultSet1 = results.Read().ToList();
		
        // Strongly Typed Object
        var resultSet2 = results.Read<ResultType2>().ToList();
    }
}
	
public class ResultType2
{
	public int ID { get; set; }
	public string Name { get; set; }
}

What does the buffered parameter do for the Read and ReadAsync methods in Dapper?

In Dapper, the buffered parameter on the Read and ReadAsync methods indicates whether the data should be buffered (loaded into memory at once) or unbuffered (streamed one row at a time). By default, this parameter is true, meaning data is buffered. If set to false, data is read row by row, which can reduce memory usage for large result sets.

string sql = "SELECT * FROM Customer; SELECT * FROM Order";

using (var connection = new SqlConnection(connectionString))
{
    using (var multi = connection.QueryMultiple(sql))
    {
        var customers = multi.Read<Customer>(buffered: false);
        var orders = multi.Read<Order>(buffered: false);
    }
}


Date Modified: 2025-07-10
Author:

Edit this page in GitHub

Got any Dapper Question?