Introduction
Dapper does not have a built-in query builder, so dynamic WHERE clauses must be constructed in C# code. The standard approach is to build a list of SQL conditions based on which model properties have values, then join them into a WHERE clause. You pass values as parameters using DynamicParameters or an anonymous object to prevent SQL injection. Libraries like SqlBuilder (from Dapper.Contrib) and DapperQueryBuilder can simplify this pattern.
Basic Dynamic Where Clause
1using Dapper;
2using System.Data.SqlClient;
3
4public class UserFilter
5{
6 public string? Name { get; set; }
7 public int? MinAge { get; set; }
8 public int? MaxAge { get; set; }
9 public string? City { get; set; }
10}
11
12public IEnumerable<User> GetUsers(UserFilter filter)
13{
14 var sql = "SELECT * FROM Users WHERE 1=1";
15 var parameters = new DynamicParameters();
16
17 if (!string.IsNullOrEmpty(filter.Name))
18 {
19 sql += " AND Name = @Name";
20 parameters.Add("Name", filter.Name);
21 }
22
23 if (filter.MinAge.HasValue)
24 {
25 sql += " AND Age >= @MinAge";
26 parameters.Add("MinAge", filter.MinAge.Value);
27 }
28
29 if (filter.MaxAge.HasValue)
30 {
31 sql += " AND Age <= @MaxAge";
32 parameters.Add("MaxAge", filter.MaxAge.Value);
33 }
34
35 if (!string.IsNullOrEmpty(filter.City))
36 {
37 sql += " AND City = @City";
38 parameters.Add("City", filter.City);
39 }
40
41 using var connection = new SqlConnection(connectionString);
42 return connection.Query<User>(sql, parameters);
43}
The WHERE 1=1 trick lets you append every condition with AND without worrying about whether it is the first condition. DynamicParameters adds only the parameters that are actually used.
Using a List of Conditions
1public IEnumerable<User> GetUsers(UserFilter filter)
2{
3 var conditions = new List<string>();
4 var parameters = new DynamicParameters();
5
6 if (!string.IsNullOrEmpty(filter.Name))
7 {
8 conditions.Add("Name = @Name");
9 parameters.Add("Name", filter.Name);
10 }
11
12 if (filter.MinAge.HasValue)
13 {
14 conditions.Add("Age >= @MinAge");
15 parameters.Add("MinAge", filter.MinAge.Value);
16 }
17
18 if (!string.IsNullOrEmpty(filter.City))
19 {
20 conditions.Add("City = @City");
21 parameters.Add("City", filter.City);
22 }
23
24 var whereClause = conditions.Count > 0
25 ? "WHERE " + string.Join(" AND ", conditions)
26 : "";
27
28 var sql = $"SELECT * FROM Users {whereClause} ORDER BY Name";
29
30 using var connection = new SqlConnection(connectionString);
31 return connection.Query<User>(sql, parameters);
32}
Building a list and joining with " AND " produces a clean WHERE clause without the 1=1 hack. When no conditions exist, the query runs without a WHERE clause.
LIKE and IN Clauses
1public IEnumerable<User> SearchUsers(string? namePattern, List<string>? cities)
2{
3 var conditions = new List<string>();
4 var parameters = new DynamicParameters();
5
6 if (!string.IsNullOrEmpty(namePattern))
7 {
8 conditions.Add("Name LIKE @NamePattern");
9 parameters.Add("NamePattern", $"%{namePattern}%");
10 }
11
12 if (cities != null && cities.Count > 0)
13 {
14 conditions.Add("City IN @Cities");
15 parameters.Add("Cities", cities);
16 }
17
18 var whereClause = conditions.Count > 0
19 ? "WHERE " + string.Join(" AND ", conditions)
20 : "";
21
22 var sql = $"SELECT * FROM Users {whereClause}";
23
24 using var connection = new SqlConnection(connectionString);
25 return connection.Query<User>(sql, parameters);
26}
Dapper automatically expands IN @Cities into IN (@Cities1, @Cities2, ...) when you pass a list. This prevents SQL injection without manual parameterization of each value.
Sorting and Paging
1public class UserQuery
2{
3 public string? Name { get; set; }
4 public string SortBy { get; set; } = "Name";
5 public string SortDirection { get; set; } = "ASC";
6 public int Page { get; set; } = 1;
7 public int PageSize { get; set; } = 20;
8}
9
10public IEnumerable<User> GetUsers(UserQuery query)
11{
12 var conditions = new List<string>();
13 var parameters = new DynamicParameters();
14
15 if (!string.IsNullOrEmpty(query.Name))
16 {
17 conditions.Add("Name LIKE @Name");
18 parameters.Add("Name", $"%{query.Name}%");
19 }
20
21 var whereClause = conditions.Count > 0
22 ? "WHERE " + string.Join(" AND ", conditions)
23 : "";
24
25 // Whitelist sort columns to prevent SQL injection
26 var allowedSorts = new HashSet<string> { "Name", "Age", "City", "CreatedAt" };
27 var sortCol = allowedSorts.Contains(query.SortBy) ? query.SortBy : "Name";
28 var sortDir = query.SortDirection.ToUpper() == "DESC" ? "DESC" : "ASC";
29
30 parameters.Add("Offset", (query.Page - 1) * query.PageSize);
31 parameters.Add("PageSize", query.PageSize);
32
33 var sql = $@"
34 SELECT * FROM Users {whereClause}
35 ORDER BY {sortCol} {sortDir}
36 OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
37
38 using var connection = new SqlConnection(connectionString);
39 return connection.Query<User>(sql, parameters);
40}
Sort column names cannot be parameterized in SQL. Always whitelist allowed column names to prevent SQL injection through the sort parameter.
Using Dapper's SqlBuilder
1using Dapper;
2
3public IEnumerable<User> GetUsersWithBuilder(UserFilter filter)
4{
5 var builder = new SqlBuilder();
6
7 if (!string.IsNullOrEmpty(filter.Name))
8 builder.Where("Name = @Name", new { filter.Name });
9
10 if (filter.MinAge.HasValue)
11 builder.Where("Age >= @MinAge", new { MinAge = filter.MinAge.Value });
12
13 if (!string.IsNullOrEmpty(filter.City))
14 builder.Where("City = @City", new { filter.City });
15
16 builder.OrderBy("Name");
17
18 var template = builder.AddTemplate(
19 "SELECT * FROM Users /**where**/ /**orderby**/"
20 );
21
22 using var connection = new SqlConnection(connectionString);
23 return connection.Query<User>(template.RawSql, template.Parameters);
24}
SqlBuilder from the Dapper contrib package provides /**where**/ and /**orderby**/ placeholders. It handles the AND joining and empty WHERE clause automatically.
Generic Extension Method
1public static class DapperExtensions
2{
3 public static IEnumerable<T> QueryWithFilter<T>(
4 this IDbConnection connection,
5 string baseQuery,
6 object filterModel)
7 {
8 var conditions = new List<string>();
9 var parameters = new DynamicParameters();
10
11 foreach (var prop in filterModel.GetType().GetProperties())
12 {
13 var value = prop.GetValue(filterModel);
14 if (value == null) continue;
15 if (value is string s && string.IsNullOrEmpty(s)) continue;
16
17 conditions.Add($"{prop.Name} = @{prop.Name}");
18 parameters.Add(prop.Name, value);
19 }
20
21 var whereClause = conditions.Count > 0
22 ? "WHERE " + string.Join(" AND ", conditions)
23 : "";
24
25 return connection.Query<T>($"{baseQuery} {whereClause}", parameters);
26 }
27}
28
29// Usage
30var users = connection.QueryWithFilter<User>("SELECT * FROM Users", filter);
This reflection-based extension generates WHERE clauses from any model's non-null properties. It works for simple equality filters but needs customization for LIKE, range, or IN queries.
Common Pitfalls
String concatenation without parameters: Building SQL with $"WHERE Name = '{filter.Name}'" is vulnerable to SQL injection. Always use Dapper parameters (@Name with DynamicParameters) to pass values safely.
Dynamic ORDER BY with user input: Sort column names cannot be SQL parameters. Passing user input directly into ORDER BY {userInput} enables SQL injection. Always whitelist allowed column names.
Passing null DynamicParameters: If you create DynamicParameters but add no parameters, passing it to Query is fine — Dapper handles empty parameters. Do not pass null though, as some overloads may not expect it.
IN clause with empty list: Passing an empty list to IN @Param generates invalid SQL (WHERE City IN ()). Check list.Count > 0 before adding the IN condition.
Forgetting async variants: Dapper provides QueryAsync, ExecuteAsync, etc. In web applications, always use the async variants to avoid blocking threads. Replace connection.Query with await connection.QueryAsync.
Summary
Build WHERE conditions as a list of strings and join with " AND "
Use DynamicParameters to add only the parameters for active filters
Use WHERE 1=1 or a conditions list to handle the first-condition edge case
Dapper automatically expands IN @Param for list parameters
Whitelist sort column names to prevent SQL injection in ORDER BY
Use SqlBuilder from Dapper contrib for a cleaner builder pattern
