Query and Mapping with Dapper: How Do Columns Map to Properties?

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

We have a Book class with four properties:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int PublishedYear { get; set; }
}

We read Dune together with its publication year. In the books table, Dune's published_year is 1965:

var dune = connection.QuerySingle<Book>(
    "SELECT id, title, price, published_year FROM books WHERE id = 4");

Console.WriteLine($"{dune.Title} ({dune.PublishedYear})");

What do you see on screen? Dune (1965), an exception because of the unmatched column, or something else? For now think of QuerySingle as "a Query that returns one row"; the details come in the next post. We give the answer a few sections from now.

The Bookstore Database

This post uses the bookstore database from the first post again: a .NET 10 console project, Dapper 2.1.89 and Microsoft.Data.Sqlite 10.0.14. The BookstoreDb.cs file you add to the project drops the tables and rebuilds them with the same sample data on every run:

using Dapper;
using Microsoft.Data.Sqlite;

public static class BookstoreDb
{
    public const string ConnectionString = "Data Source=bookstore.db";

    public static void Reset()
    {
        using var connection = new SqliteConnection(ConnectionString);
        connection.Execute("""
            DROP TABLE IF EXISTS order_items;
            DROP TABLE IF EXISTS orders;
            DROP TABLE IF EXISTS books;
            DROP TABLE IF EXISTS authors;

            CREATE TABLE authors (
                id      INTEGER PRIMARY KEY,
                name    TEXT NOT NULL,
                country TEXT
            );
            CREATE TABLE books (
                id             INTEGER PRIMARY KEY,
                author_id      INTEGER NOT NULL REFERENCES authors(id),
                title          TEXT NOT NULL,
                price          REAL NOT NULL,
                published_year INTEGER,
                stock          INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE orders (
                id             INTEGER PRIMARY KEY,
                customer_email TEXT NOT NULL,
                created_at     TEXT NOT NULL
            );
            CREATE TABLE order_items (
                id         INTEGER PRIMARY KEY,
                order_id   INTEGER NOT NULL REFERENCES orders(id),
                book_id    INTEGER NOT NULL REFERENCES books(id),
                quantity   INTEGER NOT NULL,
                unit_price REAL NOT NULL
            );

            INSERT INTO authors (id, name, country) VALUES
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Stanislaw Lem', 'Poland'),
                (4, 'Douglas Adams', 'UK'),
                (5, 'Ted Chiang', 'USA');

            INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
                (1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
                (2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
                (3, 1, 'The Dispossessed', 15.25, 1974, 0),
                (4, 2, 'Dune', 18.90, 1965, 12),
                (5, 3, 'Solaris', 11.40, 1961, 5),
                (6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
                (7, 5, 'Exhalation', 13.20, NULL, 4);
            """);
    }
}

At the top of Program.cs we call BookstoreDb.Reset(); and create a connection with using var connection = new SqliteConnection(BookstoreDb.ConnectionString);; all the snippets below use that connection. Note that Exhalation's publication year is NULL; we will come back to that.

How Does Mapping Work?

When Dapper runs a query, it takes the reader's column names in order and, for each column, looks for a writable property (or field) with the same name on the target type. The comparison is case-insensitive. Let's try the crudest version and shout the column names:

var loud = connection.QuerySingle<Book>(
    "SELECT ID, TITLE, PRICE FROM books WHERE id = 4");

Console.WriteLine($"{loud.Id} | {loud.Title} | {loud.Price}");

4 | Dune | 18.9

ID went to Id and TITLE to Title without trouble. A silent type conversion also happened here. SQLite returns integers as 64-bit long and fractional numbers as double; Dapper converted the long value into an int property and the double value into a decimal property. These conversions are allowed in property mapping but, as you will see shortly, not in constructor mapping.

One more thing: mapping works by column name, not by position. For a class whose properties get filled, the order of the columns in the SELECT does not matter. In the last exercise of the first post you saw the ADO.NET version produce wrong data when the column order changed while Dapper was unaffected; this is why.

Unmatched Columns: Silent Loss

The answer to the opening question:

Dune (0)

No exception, no warning, just 0. Dapper looked for a property called published_year, did not find one and skipped the column. The PublishedYear property was never touched, so it stayed at the default value of int. This is not a bug but a deliberate design: Dapper treats neither unmatched columns nor properties without a column as a problem. It is flexible but unforgiving; if you misspell a column name, you notice only when zeros show up on screen.

The most direct fix is an alias on the SQL side:

var aliased = connection.QuerySingle<Book>(
    "SELECT id, title, price, published_year AS PublishedYear FROM books WHERE id = 4");

Console.WriteLine($"{aliased.Title} ({aliased.PublishedYear})");

var exhalation = connection.QuerySingle<Book>(
    "SELECT id, title, price, published_year AS PublishedYear FROM books WHERE id = 7");

Console.WriteLine($"{exhalation.Title} ({exhalation.PublishedYear})");

Dune (1965)
Exhalation (0)

Dune is fixed, but look at the second line. Exhalation's year is NULL in the database; Dapper does not write a column it sees as NULL to the property, so the int stays at 0 again. The difference between "year unknown" and "published in year 0" is gone. If a column can be NULL, the property type should say so: int?.

snake_case and MatchNamesWithUnderscores

Writing aliases in every query gets tiring as the number of columns grows. If your database is snake_case throughout, you can tell Dapper to ignore underscores. This time we use a class that also models the NULL case correctly:

public class BookDetails
{
    public int Id { get; set; }
    public int AuthorId { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int? PublishedYear { get; set; }
    public int Stock { get; set; }
}

DefaultTypeMap.MatchNamesWithUnderscores = true;

var details = connection.Query<BookDetails>(
    "SELECT id, author_id, title, price, published_year, stock FROM books WHERE id IN (4, 7)");

foreach (var d in details)
{
    Console.WriteLine($"{d.Title}: author {d.AuthorId}, year {d.PublishedYear?.ToString() ?? "unknown"}, stock {d.Stock}");
}

Dune: author 2, year 1965, stock 12
Exhalation: author 5, year unknown, stock 4

author_id now goes to AuthorId, published_year to PublishedYear, and Exhalation's year is honestly "unknown". This setting is static and affects the whole application; you cannot switch it on and off for a single query. Since 2.0.151 it also applies to constructor mapping. Set it once when the application starts and forget about it; the pitfalls section shows why I say "once and first".

dynamic Results

Sometimes writing a class to carry the result feels like overkill: a one-off report, a quick list on an admin screen, or an experiment where you just want to see what comes back. If you call Query without a generic argument, each row comes back as a dynamic:

var rows = connection.Query(
    """
    SELECT b.title, a.name AS author, b.price
    FROM books b
    JOIN authors a ON a.id = b.author_id
    WHERE b.price > 14
    """);

foreach (var row in rows)
{
    Console.WriteLine($"{row.title} by {row.author}: {row.price}");
}

// Every row is also a dictionary: column name and value
var first = (IDictionary<string, object>)rows.First();
foreach (var (column, value) in first)
{
    Console.WriteLine($"{column} = {value} ({value.GetType().Name})");
}

Console.WriteLine(rows.First().isbn is null);

The Dispossessed by Ursula K. Le Guin: 15.25
Dune by Frank Herbert: 18.9
title = The Dispossessed (String)
author = Ursula K. Le Guin (String)
price = 15.25 (Double)
True

The real type of the object is Dapper's DapperRow class; you can access properties by column name or walk it like a dictionary. The dictionary output shows the raw types the provider returned: the price is a double. In the typed query Dapper converted it to decimal; here there is no target type, so there is no conversion either.

The last line matters: the table has no isbn column, yet row.isbn did not throw, it returned null. A misspelled column name slips through silently in the dynamic world too, and the compiler cannot help you. Keep dynamic for exploration and one-off jobs; write typed queries in application code.

Records and Constructor Mapping

Records are a very natural choice for read models. When Dapper sees a type without a parameterless constructor, it looks for a constructor that matches the columns:

public record BookSummary(long Id, string Title, double Price);

var summaries = connection.Query<BookSummary>(
    "SELECT id, title, price FROM books WHERE author_id = 1");

foreach (var summary in summaries)
{
    Console.WriteLine(summary);
}

BookSummary { Id = 1, Title = A Wizard of Earthsea, Price = 12.5 }
BookSummary { Id = 2, Title = The Left Hand of Darkness, Price = 14 }
BookSummary { Id = 3, Title = The Dispossessed, Price = 15.25 }

It worked, but look at the types: long and double. Let's write the same record with the types we are used to:

public record BookCard(int Id, string Title, decimal Price);

try
{
    var cards = connection.Query<BookCard>("SELECT id, title, price FROM books").ToList();
}
catch (InvalidOperationException ex)
{
    Console.WriteLine(ex.Message);
}

A parameterless default constructor or one matching signature (System.Int64 id, System.String title, System.Double price) is required for BookCard materialization

The conversions that are allowed in property mapping do not exist in constructor mapping. Dapper looks for a constructor whose parameter names match the column names, whose order matches the column order and whose types match the types the reader returns. The message tells you exactly which signature it was looking for: (System.Int64 id, System.String title, System.Double price). Call the same BookSummary with SELECT title, id, price and you get a similar message, this time with title first in the signature. An extra or a missing column also makes the constructor fail to match.

If you want flexibility without giving up records, declare the properties with init. Since there are no positional parameters, the compiler generates a parameterless constructor and Dapper falls back to property mapping:

public record BookListItem
{
    public int Id { get; init; }
    public string Title { get; init; } = "";
    public decimal Price { get; init; }
}

BookListItem { Id = 1, Title = A Wizard of Earthsea, Price = 12.5 }
BookListItem { Id = 2, Title = The Left Hand of Darkness, Price = 14 }
BookListItem { Id = 3, Title = The Dispossessed, Price = 15.25 }

The same SELECT id, title, price query now works with int and decimal. If you want positional records, use long/double on SQLite, or on a provider with richer types such as SQL Server, match the column types exactly. For cases where you want to manage the conversion yourself, we will look at type handlers in post 11.

Common Pitfalls

The sneakiest one is this. What happens if you turn on MatchNamesWithUnderscores in the middle of the application, after a query has already run?

const string sql = "SELECT id, title, published_year FROM books WHERE id = 4";

Console.WriteLine(connection.QuerySingle<Book>(sql).PublishedYear);

DefaultTypeMap.MatchNamesWithUnderscores = true;
Console.WriteLine(connection.QuerySingle<Book>(sql).PublishedYear);

SqlMapper.PurgeQueryCache();
Console.WriteLine(connection.QuerySingle<Book>(sql).PublishedYear);

0
0
1965

The first call after turning the setting on still returned 0. It is the cache we talked about in the first post: Dapper generated the mapping code for this SQL and this type once and kept it; changing the setting does not invalidate that ready-made code. After purging the cache, the new setting takes effect. These settings are process-wide, so you set them when the application starts, before the first query.

The second is SELECT *. Because unmatched columns are skipped silently, SELECT * needlessly carries columns added to the table later, and it also hides why a property stayed empty. Write the columns out.

The third is mapping columns that can be NULL to non-nullable value types. As you saw, there is no exception, only misleading defaults such as 0, false or DateTime.MinValue. Every column that accepts NULL in the schema should map to a nullable type on the C# side.

The fourth is SQLite's type system. The constructor mapping error does not come from Dapper but from SQLite returning every integer as long and every fractional number as double. The same code may work fine against an INT column on SQL Server; when you switch providers, check your positional records again.

A Small Experiment

★☆☆ In the query from "Guess First", write the alias as published_year AS publishedyear, all lowercase. Does PublishedYear get filled? Why?

★★☆ Query the BookSummary record with SELECT id, title, price, stock. Why does a single extra column cause an error? What happens when you try the same column with BookListItem?

★★★ Add public string Country { get; set; } = ""; to BookDetails, JOIN the authors table and also select a.country. Compare the results with MatchNamesWithUnderscores off and on. Then bring the id columns of both tables in the JOIN with SELECT b.*, a.* and see which one ends up in Id.

So far we have mostly worked with Query, which can return many rows, and met QuerySingle along the way. In the next post we examine the four siblings of the single-row family, QueryFirst, QueryFirstOrDefault, QuerySingle and QuerySingleOrDefault, together with which exception each one throws and when. And the series does not end there; as new Dapper versions come out, we will keep going with new posts.