Introduction

Dapper's QueryMultiple returns a GridReader that allows reading multiple result sets from a single database command. When result sets are read in the wrong order, when ReadAsync is called more times than there are result sets, or when the grid reader is disposed prematurely, data is silently lost or null is returned. This is common with stored procedures that return multiple result sets or batch SQL queries.

Symptoms

  • Read<T>() returns empty list for a result set that should have data
  • IsConsumed is true before all result sets are read
  • ReadNextGrid() returns null for subsequent result sets
  • First result set reads fine but second is empty
  • ObjectDisposedException when trying to read after grid reader is disposed

Debug grid reader: ``csharp using var multi = connection.QueryMultiple(sql, parameters); var result1 = multi.Read<User>().ToList(); Console.WriteLine($"Result 1: {result1.Count} items"); Console.WriteLine($"IsConsumed: {multi.IsConsumed}"); var result2 = multi.Read<Order>().ToList(); Console.WriteLine($"Result 2: {result2.Count} items");

Common Causes

  • Reading result sets in different order than SQL returns them
  • Calling Read<T>() more times than there are result sets
  • Grid reader disposed before all reads are complete
  • Stored procedure returns variable number of result sets based on logic
  • Type mismatch between SQL columns and C# type properties

Step-by-Step Fix

  1. 1.Read result sets in the correct order:
  2. 2.```csharp
  3. 3.var sql = @"
  4. 4.SELECT * FROM Users WHERE IsActive = 1;
  5. 5.SELECT * FROM Orders WHERE Status = 'Pending';
  6. 6.SELECT COUNT(*) FROM Products;";

using var multi = await connection.QueryMultipleAsync(sql);

// MUST read in the same order as the SQL statements var users = await multi.ReadAsync<User>(); // First SELECT var orders = await multi.ReadAsync<Order>(); // Second SELECT var productCount = await multi.ReadFirstAsync<int>(); // Third SELECT

// WRONG - reading out of order var orders = await multi.ReadAsync<Order>(); // Reads Users data! var users = await multi.ReadAsync<User>(); // Returns empty or null ```

  1. 1.**Handle variable number of result sets from stored procedures":
  2. 2.```csharp
  3. 3.using var multi = await connection.QueryMultipleAsync(
  4. 4."GetUserWithDetails",
  5. 5.new { UserId = userId },
  6. 6.commandType: CommandType.StoredProcedure);

// Always check IsConsumed before reading var user = await multi.ReadSingleOrDefaultAsync<User>();

if (!multi.IsConsumed) { var orders = await multi.ReadAsync<Order>().ToList(); }

if (!multi.IsConsumed) { var addresses = await multi.ReadAsync<Address>().ToList(); }

// Safe approach - read until consumed var results = new List<dynamic>(); while (!multi.IsConsumed) { var grid = await multi.ReadAsync(); results.AddRange(grid); } ```

  1. 1.**Handle type mapping mismatches":
  2. 2.```csharp
  3. 3.// SQL returns: user_id, full_name, email_address
  4. 4.// C# class has: Id, Name, Email

// WRONG - column names do not match public class User { public int Id { get; set; } // SQL: user_id public string Name { get; set; } // SQL: full_name public string Email { get; set; } // SQL: email_address }

// CORRECT - use aliases in SQL var sql = @" SELECT user_id as Id, full_name as Name, email_address as Email FROM Users WHERE IsActive = 1; SELECT order_id as Id, user_id as UserId, total as Total FROM Orders;";

// Or use dynamic and map manually var multi = connection.QueryMultiple(sql); var userRows = multi.Read<dynamic>(); var users = userRows.Select(r => new User { Id = (int)r.user_id, Name = (string)r.full_name, Email = (string)r.email_address }).ToList(); ```

Prevention

  • Always read result sets in the same order as the SQL produces them
  • Check IsConsumed before calling Read<T>() for optional result sets
  • Use column aliases in SQL to match C# property names
  • Wrap GridReader in using statement but do not dispose early
  • Test multi-result queries with data that verifies each result set
  • Add logging to track which result sets are read and how many items