Dynamic SQL with Dapper: SqlBuilder, Safe Filters and Contrib, Dommel, Dapper Plus

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

Guess First

Bookstore's search screen has two optional filters: a word in the title and a maximum price. The user filled in only the price. What SQL does this code produce?

var builder = new SqlBuilder();
var template = builder.AddTemplate("select id, title, price from books /**where**/ /**orderby**/");

string? titleContains = null;
decimal? maxPrice = 12m;

if (titleContains is not null)
    builder.Where("title like @pattern", new { pattern = $"%{titleContains}%" });
if (maxPrice is not null)
    builder.Where("price <= @maxPrice", new { maxPrice });
builder.OrderBy("price desc");

Console.WriteLine(template.RawSql);
foreach (var b in conn.Query<BookRow>(template.RawSql, template.Parameters))
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

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

There are three things to watch: what replaces /**where**/, who writes the word WHERE, and whether the filter we did not add for titleContains leaves any trace in the SQL.

select id, title, price from books WHERE price <= @maxPrice
 ORDER BY price desc

3 | The Left Hand of Darkness | 11
2 | Dune Messiah | 10

SqlBuilder wrote the WHERE and ORDER BY keywords, the filter we skipped left no trace, and the value went as a parameter. Had we added no filters at all, the placeholders would have been removed entirely, leaving select id, title, price from books. This post covers Dapper's way of building dynamic SQL without string concatenation, and the packages around Dapper.

The Price of String Concatenation

To see why SqlBuilder exists, let's take the shortcut. We embed the search term directly into the SQL:

var term = "x' or 1=1 or title like '";
var sql = $"select title from books where title like '%{term}%'";
Console.WriteLine(sql);
Console.WriteLine(conn.Query<string>(sql).Count());

select title from books where title like '%x' or 1=1 or title like '%'
4

With a single search box the user read the whole table. The rule from post 04 has not changed: Dapper only turns the values you pass as param into parameters; everything you embed in the SQL text goes as is. The first example also had a $"%{titleContains}%" interpolation, but that went into the parameter's value, not into the SQL. That is exactly the difference.

The classic answer for optional filters is to start with where 1 = 1, append " and ..." for each filter and collect parameters in a DynamicParameters. It works, but you have to track the ands, the spaces and which parameter was added by hand. SqlBuilder takes over that bookkeeping.

How SqlBuilder Works: Templates and Placeholders

SqlBuilder is a separate package, but it lives in Dapper's own repository and ships with the same version number:

dotnet add package Dapper.SqlBuilder --version 2.1.89

The idea is like filling in a form. AddTemplate gives you a form with blanks: /**where**/, /**orderby**/, /**select**/, /**innerjoin**/, /**leftjoin**/, /**groupby**/, /**having**/ and so on. Methods such as Where, OrderBy and InnerJoin add pieces to those blanks. The moment you read RawSql, the pieces are joined: pieces of the same kind are placed side by side with the right connector (AND for Where, a comma for OrderBy) and the keyword is added in front. Every placeholder left unfilled is removed.

The Parameters property collects the parameters you gave the template and those of every piece into a single DynamicParameters. That is why we always pass both to Query: template.RawSql and template.Parameters.

One Builder, Two Templates: Count and Page

My favorite SqlBuilder feature is that several templates can be attached to one builder. In a paged list you want both the total count and the rows of the current page, with the same filters:

var builder = new SqlBuilder();
var count = builder.AddTemplate("select count(*) from books b /**innerjoin**/ /**where**/");
var page = builder.AddTemplate("""
    select b.id, b.title, b.price
    from books b /**innerjoin**/ /**where**/ /**orderby**/
    limit @take offset @skip
    """, new { take = 1, skip = 0 });

builder.InnerJoin("authors a on a.id = b.author_id");
builder.Where("a.name = @author", new { author = "Frank Herbert" });
builder.OrderBy("b.price");

var total = conn.ExecuteScalar<int>(count.RawSql, count.Parameters);
var rows = conn.Query<BookRow>(page.RawSql, page.Parameters).AsList();
Console.WriteLine($"{rows.Count} of {total}: {rows[0].Title}");
Console.WriteLine(page.RawSql);

1 of 2: Dune Messiah
select b.id, b.title, b.price
from books b
INNER JOIN authors a on a.id = b.author_id
 WHERE a.name = @author
 ORDER BY b.price

limit @take offset @skip

The join and the filter were written once and went into both queries. Because the count template has no /**orderby**/, the ordering never reached it; keeping a needless ORDER BY out of the count query came for free. take and skip are parameters that belong only to the page template.

Sorting Chosen by the User

Here we hit SqlBuilder's limit. The text you pass to Where and OrderBy is not a parameter, it is raw SQL. Take the sort column from the query string and write builder.OrderBy(sortKey), and all the risks of string concatenation come back. Column names cannot be parameters; the only safe way is to pick allowed values from code:

partial class Program
{
    static readonly Dictionary<string, string> SortColumns = new(StringComparer.OrdinalIgnoreCase)
    {
        ["title"] = "title",
        ["price"] = "price",
        ["newest"] = "id desc"
    };
}

var builder = new SqlBuilder();
var template = builder.AddTemplate("select id, title, price from books /**orderby**/");
builder.OrderBy(SortColumns.GetValueOrDefault(sortKey, "title"));

Console.WriteLine(template.RawSql);
Console.WriteLine(string.Join(", ", conn.Query<BookRow>(template.RawSql).Select(b => b.Id)));

When I passed "newest" and "price; drop table books" as sortKey:

select id, title, price from books ORDER BY id desc
4, 3, 2, 1
select id, title, price from books ORDER BY title
1, 2, 4, 3

The text the user sends never touches the SQL; it only selects a key in the dictionary. Any value we don't recognize falls back to the default ordering. The same rule applies to table names, column lists and the asc/desc direction.

The OrWhere Surprise

SqlBuilder also has an OrWhere method, and it does not behave the way its name suggests. A separate warning about it was added to the documentation in 2.1.72. Let's try it:

var builder = new SqlBuilder();
var template = builder.AddTemplate("select title from books /**where**/");

builder.Where("author_id = @authorId", new { authorId = 1 });
builder.OrWhere("price > @minPrice", new { minPrice = 12.0 });
builder.Where("title like @pattern", new { pattern = "%Dune%" });

Console.WriteLine(template.RawSql);
Console.WriteLine(string.Join(", ", conn.Query<string>(template.RawSql, template.Parameters)));

select title from books WHERE author_id = @authorId AND title like @pattern AND  ( price > @minPrice )
Dune

Call order was ignored. SqlBuilder collects all Wheres together and all OrWheres together, then joins the two groups; because the first piece added was a Where, it joined the groups with AND. Looking at the source, I saw that the connector is chosen by the kind of the first piece. My advice is not to use OrWhere at all: if you need an OR, write it yourself inside a single Where, parentheses included.

builder.Where("(author_id = @authorId or price > @minPrice)", new { authorId = 1, minPrice = 12.0 });

The Ecosystem: Who Is Still Standing?

Dapper is kept small on purpose; for people tired of writing CRUD, an ecosystem of packages grew around it. Here is where they stand on NuGet as of November 2026.

Dapper.Contrib

It used to be the "official" CRUD helper: it generates Get, Insert, Update and Delete for classes marked with the [Table] and [Key] attributes. It still works on SQLite:

var id = conn.Insert(new Author { Name = "Isaac Asimov" });
Console.WriteLine(id);

var author = conn.Get<Author>(id);
author.Name = "Isaac Asimov (1920-1992)";
Console.WriteLine(conn.Update(author));
Console.WriteLine(conn.Get<Author>(id).Name);
Console.WriteLine(conn.Delete(author));

[Table("authors")]
public class Author
{
    [Key]
    public long Id { get; set; }
    public string Name { get; set; } = "";
}

3
True
Isaac Asimov (1920-1992)
True

But its latest version is 2.0.78, from November 2020. It moved to a separate repository and its last code commit was in May 2021; it is not formally marked "deprecated" on NuGet, but it is effectively unmaintained. It also has an everyday limitation: it uses the property name as the column name and does not know snake_case. When I tried to insert a books class with an AuthorId property:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'table books has no column named AuthorId'.

Dapper.Rainbow

It lives in Dapper's main repository and ships with it as 2.1.89, but its code was last touched in February 2024. It provides per-table CRUD through Database<T> and Table<T>; the insert statement in its source uses set nocount on and scope_identity(), so in practice it is tied to SQL Server. For a new project, knowing about it briefly is enough.

Dommel

The only actively maintained open-source CRUD layer: 3.5.3 came out in June 2026, it is MIT licensed, targets net8.0/net9.0/net10.0 and depends on Dapper 2.1.72 or later. It picks a SQL builder based on the connection type and translates LINQ expressions into the where. Dommel recognizes the [Column] attribute that core Dapper ignored in post 11:

DommelMapper.LogReceived = message =>
{
    if (message.StartsWith("Select<") || message.StartsWith("Insert<"))
        Console.WriteLine(message);
};

var cheap = conn.Select<Book>(b => b.Price < 12);
foreach (var b in cheap)
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

var newId = conn.Insert(new Book { Title = "Foundation", AuthorId = 1, Price = 9.5 });
Console.WriteLine(newId);

[Table("books")]
public class Book
{
    public long Id { get; set; }
    public string Title { get; set; } = "";
    [Column("author_id")]
    public long AuthorId { get; set; }
    public double Price { get; set; }
}

Select<Book>: select * from "books" where "books"."Price" < @p1
2 | Dune Messiah | 10
3 | The Left Hand of Darkness | 11
Insert<Book>: insert into "books" ("Title", "author_id", "Price") values (@Title, @AuthorId, @Price); select last_insert_rowid() id
5

Look at the generated SQL: select *. As the table gains columns, the data read grows too; the Dapper.AOT analyzer flags this pattern separately (post 16).

Dapper Plus

A commercial product from ZZZ Projects (Z.Dapper.Plus, latest version 9.3.6, October 2026). It provides bulk insert, update, delete and merge and comes with its own license agreement. Some context you should have: learndapper.com is run by the same company, and its "Bulk Operations" pages describe Dapper Plus. Dapper itself has no bulk operations; in post 15 we look at free alternatives.

Which One Should You Pick?

Let me give a two-option answer. Option one: write the SQL yourself, gather repetitive CRUD into a small repository or a few helper methods, and use SqlBuilder for dynamic filters. Few dependencies, and the generated SQL is entirely under your control; the cost is a bit more code. Option two: add a maintained layer such as Dommel. On simple tables it saves a lot of code; the cost is that part of the SQL now comes from somewhere you did not write, and choices like select * are imposed on you.

I would not add Dapper.Contrib to a new project. If you want that much CRUD automation, the honest question is: do you need EF Core? Change tracking, migrations and the full power of LINQ are already there; post 09 of the EF Core series shows how dynamic queries are built naturally there by composing pieces on an IQueryable. Dapper's strength is its closeness to SQL; a layer that automates everything weakens that strength.

Common Pitfalls

Mistaking SqlBuilder pieces for parameters. Write Where("title like '%" + term + "%'") and SqlBuilder puts it into the SQL as is. Values must always come from the anonymous object in the second argument.

Using the same parameter name with different values in two pieces. The parameters of all pieces are merged into a single DynamicParameters. When I wrote Where("price >= @price", new { price = 11.0 }) and Where("price <= @price", new { price = 12.0 }), both conditions ran with 12.0 and the query silently came back empty; the value added later wins. Give every piece a name of its own.

Adding two CRUD packages to the same project. When I added Dapper.Contrib and Dommel together and wrote conn.Insert(...), the project did not compile:

error CS0121: The call is ambiguous between the following methods or properties: 'Dapper.Contrib.Extensions.SqlMapperExtensions.Insert<T>(System.Data.IDbConnection, T, System.Data.IDbTransaction, int?)' and 'Dommel.DommelMapper.Insert<TEntity>(System.Data.IDbConnection, TEntity, System.Data.IDbTransaction?)'

Both add an extension method with the same name on IDbConnection. Even while migrating from one to the other, don't put usings for both in the same file.

Never reading the generated SQL. During development, always print RawSql for SqlBuilder and use LogReceived for Dommel. Bugs in dynamic SQL often show up only for one particular combination of filters.

A Small Experiment

★☆☆ Add an authorName filter to the opening example. If the filter is set, add an InnerJoin and a Where; if it is empty, don't join at all. Print RawSql for three combinations.

★★☆ Extend the SortColumns dictionary to support the asc/desc direction too: separate the two directions with the keys "price" and "-price", and still never put the user's text into the SQL under any condition.

★★★ Set up three templates on the same builder: count, page, and a select min(price), max(price) that gives the price range. Run all three in a single round trip with QueryMultiple from post 09; check how parameters are shared between the three templates.

We have seen how to build dynamic SQL safely. In the next post we return to performance: Dapper's query cache and CommandFlags.NoCache, choosing a type per row with GetRowParser, and alternatives for the bulk operations Dapper lacks, such as SqlBulkCopy, Npgsql COPY and batched inserts inside a transaction on SQLite.