Query Methods

Level: Beginner

ℹ️ Where This Fits

Dapper's query methods are where you retrieve data. Understanding Query vs QuerySingle vs QueryFirstOrDefault prevents common mistakes and improves API reliability.

ℹ️ What You'll Learn

• Query method: Get multiple rows as List of entities
• QuerySingle: Exactly one row (throws if none or multiple)
• QueryFirstOrDefault: First row or null if none
• QuerySingleOrDefault: Exactly one or null (throws if multiple)
• QueryAsync variants: Async versions (preferred for web apps)
• ExecuteScalar: Get single value (COUNT, SUM, MAX)
• ExecuteScalarAsync: Async scalar
• Result mapping: Automatic property to column matching
• Column name mismatch: [Name] or SplitOn for different column names
• Buffering: Buffered: true loads all rows (default), false streams
• QueryMultiple: Execute multiple queries in one batch
• Dynamic parameters: Parameters object for SQL values
• School Management examples: Get students, count by class, paginate results
• Common mistakes: QuerySingle vs QueryFirstOrDefault confusion, missing await

Query - Multiple Rows

using (var conn = new SqlConnection(connString))
{
    // Get all active students
    var students = conn.Query<Student>(
        "SELECT Id, Name, ClassName FROM Students WHERE Status = @status",
        new { status = "Active" }
    ).ToList();

    return students;
}

QuerySingle vs QueryFirstOrDefault

// QuerySingle - expects exactly one row (throws if 0 or 2+)
var student = conn.QuerySingle<Student>(
    "SELECT Id, Name FROM Students WHERE Id = @id",
    new { id = 101 }
);

// QueryFirstOrDefault - returns first or null
var student = conn.QueryFirstOrDefault<Student>(
    "SELECT Id, Name FROM Students WHERE ClassName = @className ORDER BY Name",
    new { className = "10-A" }
);

if (student == null)
    return NotFound();

QuerySingleOrDefault - One or None

// Exact match or null (throws if 2+)
var fee = conn.QuerySingleOrDefault<FeeAccount>(
    "SELECT Id, StudentId, TotalFees FROM FeeAccount WHERE StudentId = @studentId",
    new { studentId = 101 }
);

Async Query Methods

// Get students asynchronously
var students = await conn.QueryAsync<Student>(
    "SELECT Id, Name, ClassName FROM Students WHERE Status = @status",
    new { status = "Active" }
);

// Get first or null asynchronously
var student = await conn.QueryFirstOrDefaultAsync<Student>(
    "SELECT Id, Name FROM Students WHERE Id = @id",
    new { id = 101 }
);

ExecuteScalar - Single Value

// Count students in class
var count = conn.QuerySingle<int>(
    "SELECT COUNT(*) FROM Students WHERE ClassName = @className",
    new { className = "10-A" }
);

// Get total pending fees
var totalPending = conn.QuerySingle<decimal>(
    "SELECT SUM(TotalFees - PaidAmount) FROM FeeAccount WHERE Status = 'Pending'",
    new { }
);

Pagination Example

public async Task<List<Student>> GetStudentPageAsync(int pageNumber, int pageSize)
{
    var offset = (pageNumber - 1) * pageSize;
    
    return (await _connection.QueryAsync<Student>(
        @"SELECT Id, Name, ClassName, RollNumber 
          FROM Students 
          WHERE Status = 'Active'
          ORDER BY Name
          OFFSET @offset ROWS
          FETCH NEXT @pageSize ROWS ONLY",
        new { offset = offset, pageSize = pageSize }
    )).ToList();
}

Column Name Mismatch

// Student class has Name, but column is StudentName
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// Manual mapping
var students = conn.Query<Student>(
    "SELECT Id, StudentName as Name FROM Students"  // Alias to match property
).ToList();

QueryMultiple - Multiple Result Sets

var sql = @"
    SELECT Id, Name FROM Students WHERE ClassName = @className;
    SELECT Id, ExamName FROM Exams WHERE Status = 'Active';";

using (var multi = conn.QueryMultiple(sql, new { className = "10-A" }))
{
    var students = multi.Read<Student>().ToList();
    var exams = multi.Read<Exam>().ToList();
    
    return new { Students = students, Exams = exams };
}

Buffering Control

// Default - buffers all rows in memory
var students = conn.Query<Student>(
    "SELECT Id, Name FROM Students",
    buffered: true  // Default
).ToList();

// Stream mode - no buffering (memory efficient for large result sets)
var students = conn.Query<Student>(
    "SELECT Id, Name FROM Students", 
    buffered: false
).ToList();  // Must enumerate before closing connection

Best Practices

✓ Use QueryFirstOrDefault for single record (safe) ✓ Use QuerySingle only when you know exactly one exists ✓ Use async methods for web APIs ✓ Check for null before accessing result

💡 Which Method to Use

• Multiple rows → Query method (returns List)
• One specific row (must exist) → QuerySingle method (throws if not found)
• One specific row (might not exist) → QueryFirstOrDefault method (returns null if not found)
• Single value → QuerySingle with scalar results

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Query Methods. Try these prompts:

• "What's the difference between QuerySingle and QueryFirstOrDefault?"
• "When should you use QueryMultiple?"
• "How do you handle column name mismatches in Dapper?"
• "What's the difference between buffered: true and buffered: false?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 03. Parameters & Mapping