Dapper Generated SQL

Dapper: Generated SQL

Exactly what SqlQueryCompiler emits, statement by statement. Everything here is engine-independent structure; the parts that vary are supplied by the dialect and are marked.

---

The six statements

SqlQueryCompiler has six public compile methods. Which ones run depends on the query shape.

Method	Purpose	Used by
CompileSchemaProbe	discover the target's columns and types	once per object, ever
CompileRowCount	Meta.Total for a flat query	every flat query
CompileRows	the page of rows	every flat query
CompileGroupCount	Meta.Total for a grouped query	every grouped query
CompileGroupKeys	the page of outermost group keys	every grouped query
CompileGroupRows	every row under those keys	every grouped query

Each returns:

public sealed record CompiledSql(string Text, IReadOnlyDictionary<string, object?> Parameters);

Text contains only placeholders — never inlined literals. Parameters is keyed without the dialect's prefix (p0, not @p0).

---

1. Schema probe

SELECT * FROM <source> WHERE 1 = 0

Returns no rows. The reader's FieldCount, GetName(i) and GetFieldType(i) give the column whitelist and the CLR type of each column. Cached per provider | schema | name | type for the process lifetime.

If a driver cannot describe a column's type, the name is still recorded and the type is left unknown — in which case no coercion is applied to values compared against it.

2. Flat: row count

SELECT COUNT(*) FROM <source>[ WHERE <predicate>]

No ORDER BY, no paging. This is Meta.Total.Rows; Meta.Total.Pages is ceil(rows / size) computed in the application.

3. Flat: rows

SELECT <projection> FROM <source>[ WHERE <predicate>][ ORDER BY <terms>] <paging>

• <projection> is * when SelectColumns is empty or names nothing real, otherwise the quoted, comma-separated list of the recognised columns.
• <paging> is always emitted.
• When there is no usable sort column and the dialect requires an ORDER BY for paging (SQL Server), a placeholder is emitted first: ORDER BY (SELECT NULL).

4. Grouped: group count

SELECT COUNT(*) FROM (SELECT DISTINCT <key> FROM <source>[ WHERE <predicate>]) qf_groups

The alias is written without AS — Oracle rejects AS before a table alias, and every other engine accepts the bare form, so one spelling works everywhere.

This is the count of distinct outermost keys, which becomes Meta.Total.Rows for a grouped result.

5. Grouped: group keys

SELECT DISTINCT <key> FROM <source>[ WHERE <predicate>] ORDER BY <key term> <paging>

Ordered by the outermost GroupByDescriptor.SortOrder, with its null-ordering clause. This is the statement paging actually slices.

6. Grouped: rows

SELECT <projection + grouping columns> FROM <source>
WHERE (<predicate>) AND <key predicate>[ ORDER BY <sort terms>]

No paging clause — every row under the paged keys is fetched, because a node's Count is the number of leaf rows beneath it and that cannot be known from a partial set. See Grouping and Hierarchies.

When there is no filter, the WHERE is just the key predicate.

The key predicate

IN never matches NULL, so nulls are handled explicitly:

Keys on the page	Emitted
all non-null	<key> IN (@p0, @p1, …)
some null	(<key> IN (@p0, …) OR <key> IS NULL)
only null	<key> IS NULL
none	1 = 0

---

The FROM source

DapperObjectType	SQL Server / PostgreSQL	MySQL / SQLite	Oracle
Auto, Table, View	[schema].[name]	`name` (schema only if given)	"NAME"
TVF	[schema].[fn](@p0, @p1)	not supported	TABLE("FN"(:p0, :p1))
SP	not compiled — see below

TVF arguments are positional, taken in the order of the Parameters dictionary, and each becomes a parameter.

SP is never composed into a SELECT. Calling CompileSchemaProbe or any other compile method with an SP target throws InvalidOperationException — the executor routes procedures down a separate path that calls them and filters in memory. See Dapper Provider.

---

The WHERE clause

Built group by group. Only conditions that are usable and name a whitelisted column contribute.

(<condition> <AND|OR> <condition> …)          one group
NOT (<condition> …)                           a negated group (AndNot / OrNot)
<group> <AND|OR> <group> …                    groups joined by criteria.Logic

A group producing no fragments is skipped entirely — it does not emit () or 1=1. If no group produces anything, no WHERE clause is emitted at all.

Per-operator output

col is the quoted column, @pN a parameter reference in the dialect's form.

Operator	Emitted	Notes
Equals, value set	col = @p0
Equals, value null	col IS NULL	no parameter
NotEquals, value set	col <> @p0
NotEquals, value null	col IS NOT NULL	no parameter
LessThan	col < @p0
GreaterThan	col > @p0
LessThanOrEqualTo	col <= @p0
GreaterThanOrEqualTo	col >= @p0
Between	col BETWEEN @p0 AND @p1	two parameters
Contains	col LIKE @p0 ESCAPE '\'	pattern %value%
NotContains	col NOT LIKE @p0 ESCAPE '\'	pattern %value%
StartsWith	col LIKE @p0 ESCAPE '\'	pattern value%
EndsWith	col LIKE @p0 ESCAPE '\'	pattern %value

The ESCAPE clause is the dialect's; MySQL emits ESCAPE '\\' because it processes backslash escapes inside string literals.

The pattern is built after escaping the caller's value, so wildcards inside it match literally. A search for 50% becomes the parameter %50\%% with ESCAPE '\'.

Values are coerced to the column's real type before binding — see Query Semantics.

---

ORDER BY

One term per usable sort column, in list order:

<quoted column> <ASC|DESC><null ordering>

<null ordering> is the dialect's, and includes a leading space or is empty:

Engine	Ascending	Descending
PostgreSQL, Oracle	NULLS FIRST	NULLS LAST
SQL Server, MySQL, SQLite	(empty — default already matches)	(empty)

The standard is nulls first ascending, nulls last descending, everywhere. See Query Semantics.

Paging clause

Engine	Emitted
SQL Server, Oracle	OFFSET <n> ROWS FETCH NEXT <m> ROWS ONLY
PostgreSQL, MySQL, SQLite	LIMIT <m> OFFSET <n>

with m = Size > 0 ? Size : 12, n = (max(Number,1) - 1) * m.

The offset and size are inlined as integers, not parameters. They are computed from validated integers, never from caller text, and inlining lets the optimizer see the actual window.

---

Parameters

• Named p0, p1, … in the order the compiler encounters them.
• Numbered per statement — the count query and the row query each start at p0, and identical filters produce identical names in both.
• Rendered with the dialect's prefix: @p0 everywhere, :p0 on Oracle.
• Never derived from column names, which avoids reserved-word collisions such as Oracle's ORA-01745.
• Bound by name; BindByName is set reflectively on drivers that expose it.

---

Worked examples

Model:

public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string Country { get; set; }
    public string? Department { get; set; }
    public decimal Score { get; set; }
    public bool IsActive { get; set; }
}

Flat, filtered, sorted, paged, projected

var dq = DapperQueryBuilder
    .Where(new QueryCriteria([
        new ConditionGroup([
            new Condition("Country", ConditionOperator.Equals, "Germany"),
            new Condition("Score", ConditionOperator.GreaterThan, 50)])]))
    .Select("UserId", "FirstName", "Score")
    .Sort(new SortDescriptor("Score", SortOrder.Descending))
    .Page(20, 2)
    .ForObject("Users", "dbo")
    .Build();

SQL Server

-- count
SELECT COUNT(*) FROM [dbo].[Users] WHERE ([Country] = @p0 AND [Score] > @p1)

-- rows
SELECT [UserId], [FirstName], [Score] FROM [dbo].[Users]
WHERE ([Country] = @p0 AND [Score] > @p1)
ORDER BY [Score] DESC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

PostgreSQL

SELECT "UserId", "FirstName", "Score" FROM "public"."Users"
WHERE ("Country" = @p0 AND "Score" > @p1)
ORDER BY "Score" DESC NULLS LAST
LIMIT 20 OFFSET 20

MySQL

SELECT `UserId`, `FirstName`, `Score` FROM `Users`
WHERE (`Country` = @p0 AND `Score` > @p1)
ORDER BY `Score` DESC
LIMIT 20 OFFSET 20

Oracle

SELECT "UserId", "FirstName", "Score" FROM "Users"
WHERE ("Country" = :p0 AND "Score" > :p1)
ORDER BY "Score" DESC NULLS LAST
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

Parameters in all four: p0 = "Germany", p1 = 50m — coerced to decimal because that is the column's type, even if the client sent "50".

Nested logic with a negated group

{ "criteria": { "logic": 0, "groups": [
    { "logic": 1, "conditions": [
        { "columnName": "Country", "operator": 0, "value": "Germany" },
        { "columnName": "Country", "operator": 0, "value": "Canada" } ] },
    { "logic": 2, "conditions": [
        { "columnName": "Department", "operator": 0, "value": "HR" } ] } ] } }

WHERE ([Country] = @p0 OR [Country] = @p1) AND NOT ([Department] = @p2)

Text search with a wildcard in the value

new Condition("FirstName", ConditionOperator.Contains, "50%")

WHERE ([FirstName] LIKE @p0 ESCAPE '\')
-- p0 = "%50\%%"

Null checks

new Condition("Department", ConditionOperator.Equals, null)     // → [Department] IS NULL
new Condition("Department", ConditionOperator.NotEquals, null)  // → [Department] IS NOT NULL

Neither emits a parameter.

A grouped query

var dq = DapperQueryBuilder
    .Where(new QueryCriteria([ new ConditionGroup([
        new Condition("IsActive", ConditionOperator.Equals, true)])]))
    .Select("UserId", "FirstName", "Score")
    .Sort(new SortDescriptor("Score", SortOrder.Descending))
    .GroupBy(new GroupByDescriptor("Country"), new GroupByDescriptor("Department"))
    .Page(5, 1)
    .ForObject("Users", "dbo")
    .Build();

Three statements, on SQL Server:

-- 1. how many distinct countries match
SELECT COUNT(*) FROM (
    SELECT DISTINCT [Country] FROM [dbo].[Users] WHERE ([IsActive] = @p0)
) qf_groups

-- 2. the first five of them
SELECT DISTINCT [Country] FROM [dbo].[Users] WHERE ([IsActive] = @p0)
ORDER BY [Country] ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

-- 3. every row in those five countries.
--    Country and Department are appended to the projection because the tree is rebuilt from them.
SELECT [UserId], [FirstName], [Score], [Country], [Department] FROM [dbo].[Users]
WHERE ([IsActive] = @p0) AND [Country] IN (@p1, @p2, @p3, @p4, @p5)
ORDER BY [Score] DESC

The hierarchy is then assembled from statement 3's rows by HierarchyBuilder.

A table-valued function

.ForObject("tvf_GetUsersByTenant", "dbo", DapperObjectType.TVF,
    new Dictionary<string, object?> { ["TenantId"] = 1 })

-- SQL Server
SELECT * FROM [dbo].[tvf_GetUsersByTenant](@p0) WHERE …

-- PostgreSQL
SELECT * FROM "public"."tvf_GetUsersByTenant"(@p0) WHERE …

-- Oracle
SELECT * FROM TABLE("TVF_GETUSERSBYTENANT"(:p0)) WHERE …

---

Seeing the SQL yourself

The compiler is public, so you can inspect what a query produces without a database:

using PepperX.QueryForge.Dapper.Compiler;
using PepperX.QueryForge.Dapper.Dialects;

var compiler = new SqlQueryCompiler(new PostgreSqlDialect());

// Supply the whitelist yourself instead of probing a database.
var columns = new ColumnWhitelist(new Dictionary<string, Type?>
{
    ["UserId"]  = typeof(int),
    ["Country"] = typeof(string),
    ["Score"]   = typeof(decimal)
});

var sql = compiler.CompileRows(dapperQuery, columns);

Console.WriteLine(sql.Text);
foreach (var (name, value) in sql.Parameters)
    Console.WriteLine($"  {name} = {value}");

This is exactly how the repository's own dialect and compiler tests work — see Testing.

For the EF Core provider the equivalent is ToQueryString():

db.Users.ApplyQuery(query).ToQueryString();