SqlOM 2.0.0

SqlOM

Generate dynamic SQL queries using C# code only

💡 For AI Tools (Cursor AI, GitHub Copilot, etc.): This README contains comprehensive examples and documentation for using SqlOM. When coding with SqlOM, refer to this README file for complete API usage, examples, and best practices. All examples progress from simple to complex queries.

SqlOM is a software component which allows you to programmatically create SQL queries at runtime using a convenient .NET object model, thus creating an abstraction layer over SQL. Dynamic SQL generation is useful in several scenarios:

• Generate SQL dynamically when query structure is not known at development time (i.e. user defined reports or filters)
• Generate SQL dynamically when database structure is not known at development time (i.e. user defined tables or fields)
• Create a database independent data layer

SqlOM automates the process of SQL generation in a dynamic, convenient, time saving, database independent way.

Installation

Package Manager

Install-Package SqlOM

.NET CLI

dotnet add package SqlOM

PackageReference

<PackageReference Include="SqlOM" Version="2.0.0" />

Supported Databases

Currently the following databases are supported. We continuously add support for additional databases. If your database is not on the list, contact us or tweak the source code on your own to add the desired functionality.

• SQL Server
• Oracle
• MySQL
• MariaDB
• SQLite
• PostgreSQL

Features

• ✅ Dynamic SQL query generation
• ✅ Database-agnostic query building
• ✅ Support for complex WHERE conditions
• ✅ Multiple JOIN types (INNER, LEFT, RIGHT, FULL, CROSS)
• ✅ CASE expressions
• ✅ UNION queries
• ✅ Common Table Expressions (CTEs), including recursive CTEs (WITH RECURSIVE)
• ✅ Paging support (legacy ROW_NUMBER() wrapper and modern OFFSET/FETCH for SQL Server 2012+, LIMIT/OFFSET for PostgreSQL/MySQL/SQLite)
• ✅ Parameterized queries (manual placeholders and automatic capture via RenderSelectCommand / RenderInsertCommand / RenderUpdateCommand / RenderDeleteCommand)
• ✅ Cross-Tabs (Pivot Tables)
• ✅ Attribute-based strongly-typed query building
• ✅ Fluent API for query building
• ✅ Modern C# 12 syntax support
• ✅ Multi-targets .NET Standard 2.1, .NET 8.0, and .NET 9.0
• ✅ Source Link, deterministic builds, and symbol packages for step-through debugging

Examples (Simple to Complex)

All examples use the following namespaces:

using Reeb.SqlOM;
using Reeb.SqlOM.Render;

1. Simple SELECT Query (Single Table)

The most basic query - selecting columns from a single table:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.Columns.Add(new SelectColumn("email"));
query.FromClause.BaseTable = FromTerm.Table("customers");

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [name], [email] FROM [customers]

2. SELECT with WHERE Clause

Adding a simple WHERE condition:

FromTerm tCustomers = FromTerm.Table("customers", "c");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("email", tCustomers));
query.FromClause.BaseTable = tCustomers;

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("name", tCustomers), 
    SqlExpression.String("John"), 
    CompareOperator.Equal));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [c].[name], [c].[email] FROM [customers] [c] WHERE [c].[name] = 'John'

3. SELECT with ORDER BY

Adding sorting to your query:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.Columns.Add(new SelectColumn("createdDate"));
query.FromClause.BaseTable = FromTerm.Table("customers");

query.OrderByTerms.Add(new OrderByTerm("name", OrderByDirection.Ascending));
query.OrderByTerms.Add(new OrderByTerm("createdDate", OrderByDirection.Descending));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [name], [createdDate] FROM [customers] ORDER BY [name] asc, [createdDate] desc

4. SELECT with Multiple WHERE Conditions

Using AND/OR logic in WHERE clauses:

FromTerm tProducts = FromTerm.Table("products", "p");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tProducts;

// AND condition (default)
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("price", tProducts), 
    SqlExpression.Number(10), 
    CompareOperator.Greater));

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("price", tProducts), 
    SqlExpression.Number(100), 
    CompareOperator.Less));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [p].[name], [p].[price] FROM [products] [p] WHERE ([p].[price] > 10 AND [p].[price] < 100)

5. Rendering Queries

After building your query, render it to SQL for your specific database:

SelectQuery query = new SelectQuery();
// ... configure query ...

// For SQL Server
string sql = new SqlServerRenderer().RenderSelect(query);

// For PostgreSQL
string sql = new PostgreSqlRenderer().RenderSelect(query);

// For MySQL
string sql = new MySqlRenderer().RenderSelect(query);

// For Oracle
string sql = new OracleRenderer().RenderSelect(query);

// For SQLite
string sql = new SQLiteRenderer().RenderSelect(query);

6. Simple JOIN (Two Tables)

Joining two tables with a simple key relationship:

FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tOrders = FromTerm.Table("orders", "o");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("orderDate", tOrders));
query.Columns.Add(new SelectColumn("total", tOrders));

query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Inner, tCustomers, tOrders, "customerId", "customerId");

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [c].[name], [o].[orderDate], [o].[total] FROM [customers] [c] INNER JOIN [orders] [o] ON [c].[customerId] = [o].[customerId]

7. Multiple JOINs (Three or More Tables)

Joining multiple tables:

FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tOrders = FromTerm.Table("orders", "o");
FromTerm tOrderItems = FromTerm.Table("orderItems", "oi");
FromTerm tProducts = FromTerm.Table("products", "p");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("orderDate", tOrders));
query.Columns.Add(new SelectColumn("productName", tProducts));
query.Columns.Add(new SelectColumn("quantity", tOrderItems));

query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tOrderItems, "orderId", "orderId");
query.FromClause.Join(JoinType.Inner, tOrderItems, tProducts, "productId", "productId");

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

8. Complex WHERE Conditions

Using various comparison operators and logical groupings:

FromTerm tProducts = FromTerm.Table("products", "p");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tProducts;

// Simple comparison
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("name", tProducts), 
    SqlExpression.String("John"), 
    CompareOperator.Equal));

// BETWEEN clause
query.WherePhrase.Terms.Add(WhereTerm.CreateBetween(
    SqlExpression.Field("price", tProducts), 
    SqlExpression.Number(1), 
    SqlExpression.Number(100)));

// IN clause
query.WherePhrase.Terms.Add(WhereTerm.CreateIn(
    SqlExpression.Field("category", tProducts), 
    SqlConstantCollection.FromList(new string[] {"Electronics", "Books", "Clothing"})));

// IS NULL
query.WherePhrase.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("deletedDate", tProducts)));

// OR group
WhereClause orGroup = new WhereClause(WhereClauseRelationship.Or);
orGroup.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("status", tProducts), 
    SqlExpression.String("Active"), 
    CompareOperator.Equal));
orGroup.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("status", tProducts), 
    SqlExpression.String("Pending"), 
    CompareOperator.Equal));
query.WherePhrase.SubClauses.Add(orGroup);

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

9. Complex JOINs with Multiple Conditions

Joining tables with multiple join conditions:

FromTerm tOrders = FromTerm.Table("orders", "o");
FromTerm tOrderItems = FromTerm.Table("orderItems", "oi");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("orderId", tOrders));
query.Columns.Add(new SelectColumn("itemId", tOrderItems));

query.FromClause.BaseTable = tOrders;

// Join with multiple conditions
query.FromClause.Join(JoinType.Left, tOrders, tOrderItems, 
    new JoinCondition("orderId", "orderId"),
    new JoinCondition("customerId", "customerId"));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

10. JOINs with Custom WHERE Conditions

Using WHERE clauses for complex join conditions:

FromTerm tOrders = FromTerm.Table("orders", "o");
FromTerm tProducts = FromTerm.Table("products", "p");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("orderId", tOrders));
query.Columns.Add(new SelectColumn("productName", tProducts));

query.FromClause.BaseTable = tOrders;

// Complex join condition using WHERE clause
WhereClause joinCondition = new WhereClause(WhereClauseRelationship.Or);
joinCondition.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("productId", tOrders), 
    SqlExpression.Field("productId", tProducts), 
    CompareOperator.Equal));
joinCondition.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("productName", tOrders), 
    SqlExpression.Field("name", tProducts), 
    CompareOperator.Equal));

query.FromClause.Join(JoinType.Left, tOrders, tProducts, joinCondition);

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

11. Aggregations and GROUP BY

Using aggregate functions and grouping:

FromTerm tOrders = FromTerm.Table("orders", "o");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("customerId", tOrders));
query.Columns.Add(new SelectColumn(
    SqlExpression.Function(SqlAggregationFunction.Sum, SqlExpression.Field("total", tOrders)), 
    "totalAmount"));
query.Columns.Add(new SelectColumn(
    SqlExpression.Function(SqlAggregationFunction.Count, SqlExpression.Field("orderId", tOrders)), 
    "orderCount"));

query.FromClause.BaseTable = tOrders;

query.GroupByTerms.Add(new GroupByTerm("customerId", tOrders));

query.OrderByTerms.Add(new OrderByTerm("totalAmount", OrderByDirection.Descending));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [o].[customerId], SUM([o].[total]) [totalAmount], COUNT([o].[orderId]) [orderCount] 
//         FROM [orders] [o] GROUP BY [o].[customerId] ORDER BY [totalAmount] desc

12. Strongly-Typed Query Building with Attributes

Use attributes to define table and column mappings, then build queries without magic strings:

Define Row Types with Attributes

using Reeb.SqlOM;

[TableName("customers"), TableAlias("c")]
public class CustomerRow
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    [IgnoreColumn] public List<OrderRow> Orders { get; set; } // Navigation property, not a column
}

[TableName("orders"), TableAlias("o")]
public class OrderRow
{
    public string Id { get; set; }
    [ColumnName("customer_id")] public string CustomerId { get; set; } // Maps to different column name
    public decimal Total { get; set; }
    public DateTime OrderDate { get; set; }
}

Build Queries Using Row Types

using Reeb.SqlOM;
using static Reeb.SqlOM.SqlOMExtensions;

// Create table references from attributes
var tCustomers = Table<CustomerRow>();  // FromTerm.Table("customers", "c")
var tOrders = Table<OrderRow>();        // FromTerm.Table("orders", "o")

SelectQuery query = new SelectQuery();

// Add all columns from a type (excludes [IgnoreColumn] properties)
query.Columns.AddAllColumns<CustomerRow>(tCustomers);

// Add specific columns with auto-aliasing
// CustomerId has [ColumnName("customer_id")], so this generates: customer_id AS CustomerId
query.Columns.Add<OrderRow>(x => x.CustomerId, tOrders);
query.Columns.Add<OrderRow>(x => x.Total, tOrders);

query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, 
    nameof(CustomerRow.Id), 
    ColumnName<OrderRow>(x => x.CustomerId));  // Returns "customer_id"

// Use strongly-typed field expressions in WHERE
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    Field<OrderRow>(x => x.Total, tOrders),
    SqlExpression.Number(100),
    CompareOperator.Greater));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

Quick Query Generation

Generate a complete SELECT query from a row type in one line:

// Generates: SELECT Id, Name, Email FROM customers c
SelectQuery query = GenerateSelectQuery<CustomerRow>();

// With custom alias
SelectQuery query = GenerateSelectQuery<CustomerRow>("cust");

Automatic Alias Generation

All query constructors (SelectQuery, UpdateQuery, DeleteQuery, InsertQuery, BulkInsertQuery) automatically reset alias tracking. Table<T>() then auto-generates unique aliases:

var query = new SelectQuery();  // resets aliases automatically
var tPhase = Table<Phase>();           // alias: "p"
var tSubphase = Table<Subphase>();     // alias: "s"
var tWorkPackage = Table<WorkPackage>(); // alias: "w"
var tActivity = Table<Activity>();     // alias: "a"
var tStatus = Table<ActivityExecutionStatus>(); // alias: "a2" (auto-incremented)

// Override if needed
var tCustom = Table<Phase>("custom");  // explicit alias

Available Attributes

Attribute	Target	Description
[TableName("name")]	Class	Specifies the database table name
[TableAlias("alias")]	Class	Specifies the default alias in queries
[ColumnName("name")]	Property	Maps property to a different column name
[IgnoreColumn]	Property	Excludes property from AddAllColumns<T>()

Helper Methods

Method	Description
FromTerm.Table<T>()	Creates FromTerm with auto-generated unique alias
FromTerm.Table<T>(alias)	Creates FromTerm with explicit alias
SqlExpression.Field<T>(x => x.Prop, table)	Creates field with correct column name
SelectQuery.For<T>()	Creates query with all columns from type
columns.Add<T>(x => x.Prop, table)	Adds column with auto-aliasing
columns.AddAllColumns<T>(table)	Adds all columns, auto-skips navigation/[NotMapped]
SqlConstantCollection.FromGuids(list)	Creates constants from Guid list

13. Fluent API Query Building

Use the fluent API for more readable and chainable query construction:

using Reeb.SqlOM;
using static Reeb.SqlOM.SqlOMExtensions;

FromTerm customers = Table<CustomerRow>();
FromTerm orders = Table<OrderRow>();

SelectQuery query = new SelectQuery()
    .Select<CustomerRow>(x => x.Name, customers)
    .Select<OrderRow>(x => x.Total, orders)
    .Join(JoinType.Inner, customers, orders, 
          ColumnName<CustomerRow>(x => x.Id), 
          ColumnName<OrderRow>(x => x.CustomerId))
    .Where<CustomerRow>(x => x.Name, customers, CompareOperator.Equal, SqlExpression.String("John"))
    .OrderBy("name", customers, OrderByDirection.Ascending)
    .Top(10);

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [c].[Name], [o].[Total] FROM [customers] [c] 
//         INNER JOIN [orders] [o] ON [c].[Id] = [o].[customer_id] 
//         WHERE [c].[Name] = 'John' ORDER BY [c].[name] LIMIT 10

Available Fluent Methods

Method	Description
.Where(term)	Adds a WHERE condition
.Where<T>(fieldExpr, table, op, value)	Adds a type-safe WHERE condition
.OrderBy(field, table?, direction?)	Adds ORDER BY clause
.OrderBy<T>(fieldExpr, table, direction?)	Adds type-safe ORDER BY
.GroupBy(field, table?)	Adds GROUP BY clause
.GroupBy<T>(fieldExpr, table)	Adds type-safe GROUP BY
.Select(column)	Adds a column to SELECT
.Select<T>(fieldExpr, table, alias?)	Adds type-safe column
.Join(type, leftTable, rightTable, leftField, rightField)	Adds JOIN
.WithCte(name, query, columnNames?)	Adds Common Table Expression
.Top(count)	Sets TOP/LIMIT
.Distinct()	Sets DISTINCT

14. CASE Expressions (Advanced)

Using CASE statements in SELECT columns:

FromTerm tProducts = FromTerm.Table("products", "p");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tProducts));
query.Columns.Add(new SelectColumn("price", tProducts));

// Create CASE expression
CaseClause caseClause = new CaseClause();
caseClause.Terms.Add(new CaseTerm(
    WhereTerm.CreateCompare(SqlExpression.Field("price", tProducts), SqlExpression.Number(100), CompareOperator.Less),
    SqlExpression.String("Budget")));
caseClause.Terms.Add(new CaseTerm(
    WhereTerm.CreateCompare(SqlExpression.Field("price", tProducts), SqlExpression.Number(500), CompareOperator.Less),
    SqlExpression.String("Mid-Range")));
caseClause.ElseValue = SqlExpression.String("Premium");

query.Columns.Add(new SelectColumn(SqlExpression.Case(caseClause), "priceCategory"));

query.FromClause.BaseTable = tProducts;

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

15. UNION Queries

Combining multiple SELECT queries:

SqlUnion union = new SqlUnion();

// First query
SelectQuery query1 = new SelectQuery();
query1.Columns.Add(new SelectColumn(SqlExpression.Raw("price * 10"), "priceX10"));
query1.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query1);

// Second query
SelectQuery query2 = new SelectQuery();
query2.Columns.Add(new SelectColumn(SqlExpression.Field("price"), "priceX10"));
query2.FromClause.BaseTable = FromTerm.Table("products");
union.Add(query2, DistinctModifier.All);

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderUnion(union);
// Result: SELECT price * 10 [priceX10] FROM [products] UNION ALL SELECT [price] [priceX10] FROM [products]

16. Common Table Expressions (CTEs)

Using WITH clauses for complex queries and recursive operations:

Simple CTE

// Create a CTE that calculates total sales per customer
SelectQuery cteQuery = new SelectQuery();
cteQuery.Columns.Add(new SelectColumn("customer_id"));
cteQuery.Columns.Add(new SelectColumn(SqlExpression.Raw("SUM(amount)"), "total_sales"));
cteQuery.FromClause.BaseTable = FromTerm.Table("orders");
cteQuery.GroupByTerms.Add(new GroupByTerm("customer_id"));

CommonTableExpression cte = new CommonTableExpression("customer_totals", cteQuery);

// Main query using the CTE
SelectQuery mainQuery = new SelectQuery();
mainQuery.CommonTableExpressions.Add(cte);
mainQuery.Columns.Add(new SelectColumn("customer_id"));
mainQuery.Columns.Add(new SelectColumn("total_sales"));
mainQuery.FromClause.BaseTable = FromTerm.Table("customer_totals", "ct");
mainQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("total_sales", "ct"), 
    SqlExpression.Number(1000), 
    CompareOperator.Greater));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(mainQuery);
// Result: WITH [customer_totals] AS (SELECT [customer_id], SUM(amount) [total_sales] FROM [orders] GROUP BY [customer_id]) SELECT [customer_id], [total_sales] FROM [customer_totals] ct WHERE [ct].[total_sales] > 1000

Multiple CTEs

// First CTE - high value customers
SelectQuery highValueQuery = new SelectQuery();
highValueQuery.Columns.Add(new SelectColumn("customer_id"));
highValueQuery.Columns.Add(new SelectColumn("name"));
highValueQuery.FromClause.BaseTable = FromTerm.Table("customers");
highValueQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("total_purchases"), 
    SqlExpression.Number(5000), 
    CompareOperator.Greater));

CommonTableExpression highValueCte = new CommonTableExpression("high_value_customers", highValueQuery);

// Second CTE - recent orders
SelectQuery recentOrdersQuery = new SelectQuery();
recentOrdersQuery.Columns.Add(new SelectColumn("customer_id"));
recentOrdersQuery.Columns.Add(new SelectColumn("order_date"));
recentOrdersQuery.Columns.Add(new SelectColumn("amount"));
recentOrdersQuery.FromClause.BaseTable = FromTerm.Table("orders");
recentOrdersQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("order_date"), 
    SqlExpression.Raw("DATEADD(month, -3, GETDATE())"), 
    CompareOperator.Greater));

CommonTableExpression recentOrdersCte = new CommonTableExpression("recent_orders", recentOrdersQuery);

// Main query joining both CTEs
SelectQuery mainQuery = new SelectQuery();
mainQuery.CommonTableExpressions.Add(highValueCte);
mainQuery.CommonTableExpressions.Add(recentOrdersCte);
mainQuery.Columns.Add(new SelectColumn("h.name"));
mainQuery.Columns.Add(new SelectColumn(SqlExpression.Raw("SUM(r.amount)"), "recent_total"));
mainQuery.FromClause.BaseTable = FromTerm.Table("high_value_customers", "h");
mainQuery.Joins.Add(JoinType.Inner, FromTerm.Table("recent_orders", "r"), 
    WhereTerm.CreateCompare(SqlExpression.Field("customer_id", "h"), SqlExpression.Field("customer_id", "r")));
mainQuery.GroupByTerms.Add(new GroupByTerm("h.customer_id"));
mainQuery.GroupByTerms.Add(new GroupByTerm("h.name"));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(mainQuery);
// Result: WITH [high_value_customers] AS (SELECT [customer_id], [name] FROM [customers] WHERE [total_purchases] > 5000), [recent_orders] AS (SELECT [customer_id], [order_date], [amount] FROM [orders] WHERE [order_date] > DATEADD(month, -3, GETDATE())) SELECT [h].[name], SUM(r.amount) [recent_total] FROM [high_value_customers] h INNER JOIN [recent_orders] r ON [h].[customer_id] = [r].[customer_id] GROUP BY [h].[customer_id], [h].[name]

Recursive CTE (Hierarchical Data)

A recursive CTE is modeled as a single SelectQuery that UNIONs the anchor and recursive members, wrapped in a CommonTableExpression with IsRecursive = true. Any recursive CTE in the list causes the renderer to emit WITH RECURSIVE (standard syntax accepted by PostgreSQL, SQLite, MySQL 8+, MariaDB 10.2+, and Oracle 12c+; SQL Server also accepts it).

// Anchor member: top-level employees (managers with no manager)
SelectQuery anchor = new SelectQuery();
anchor.Columns.Add(new SelectColumn("employee_id"));
anchor.Columns.Add(new SelectColumn("manager_id"));
anchor.Columns.Add(new SelectColumn("name"));
anchor.Columns.Add(new SelectColumn(SqlExpression.Number(0), "level"));
anchor.FromClause.BaseTable = FromTerm.Table("employees");
anchor.WherePhrase.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("manager_id")));

// Recursive member: employees joined to their manager already in the hierarchy
FromTerm tEmp = FromTerm.Table("employees", "e");
FromTerm tHierarchy = FromTerm.Table("hierarchy", "h");
SelectQuery recursive = new SelectQuery();
recursive.Columns.Add(new SelectColumn("employee_id", tEmp));
recursive.Columns.Add(new SelectColumn("manager_id", tEmp));
recursive.Columns.Add(new SelectColumn("name", tEmp));
recursive.Columns.Add(new SelectColumn(SqlExpression.Raw("h.level + 1"), "level"));
recursive.FromClause.BaseTable = tEmp;
recursive.FromClause.Join(JoinType.Inner, tEmp, tHierarchy, "manager_id", "employee_id");

// UNION ALL the anchor and recursive members into the CTE body
SqlUnion union = new SqlUnion();
union.Add(anchor);
union.Add(recursive, DistinctModifier.All);

// A UNION isn't a SelectQuery, so wrap it via a subquery FromTerm
SelectQuery cteBody = new SelectQuery(FromTerm.SubQueryObj(union, "u"));
cteBody.Columns.Add(new SelectColumn("*"));

CommonTableExpression hierarchyCte = new CommonTableExpression("hierarchy", cteBody)
{
    IsRecursive = true  // emits WITH RECURSIVE
};

// Main query
SelectQuery mainQuery = new SelectQuery();
mainQuery.CommonTableExpressions.Add(hierarchyCte);
mainQuery.Columns.Add(new SelectColumn("employee_id"));
mainQuery.Columns.Add(new SelectColumn("name"));
mainQuery.Columns.Add(new SelectColumn("level"));
mainQuery.FromClause.BaseTable = FromTerm.Table("hierarchy");
mainQuery.OrderByTerms.Add(new OrderByTerm("level"));
mainQuery.OrderByTerms.Add(new OrderByTerm("name"));

PostgreSqlRenderer renderer = new PostgreSqlRenderer();
string sql = renderer.RenderSelect(mainQuery);
// Result (PostgreSQL):
// WITH RECURSIVE "hierarchy" AS (
//   SELECT * FROM (
//     SELECT "employee_id", "manager_id", "name", 0 "level" FROM "employees" WHERE "manager_id" IS NULL
//     UNION ALL
//     SELECT "e"."employee_id", "e"."manager_id", "e"."name", h.level + 1 "level"
//     FROM "employees" "e" INNER JOIN "hierarchy" "h" ON "e"."manager_id" = "h"."employee_id"
//   ) "u"
// )
// SELECT "employee_id", "name", "level" FROM "hierarchy" ORDER BY "level" asc, "name" asc

17. Parameterized Queries

Using parameters for better performance and security:

FromTerm tCustomers = FromTerm.Table("customers");

SelectQuery query = new SelectQuery();
query.TableSpace = "MyDatabase.dbo"; // For execution plan caching
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("email", tCustomers));
query.FromClause.BaseTable = tCustomers;

// Use parameters instead of literal values
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Parameter("@customerName"), 
    SqlExpression.Field("name", tCustomers), 
    CompareOperator.Equal));

query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("age", tCustomers), 
    SqlExpression.Parameter("@minAge"), 
    CompareOperator.GreaterOrEqual));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

// Use with SqlCommand
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@customerName", SqlDbType.NVarChar).Value = "John";
command.Parameters.Add("@minAge", SqlDbType.Int).Value = 18;

Automatic parameter capture (RenderSelectCommand)

If you don't want to thread SqlExpression.Parameter(...) objects through your code, every renderer also exposes RenderSelectCommand / RenderInsertCommand / RenderUpdateCommand / RenderDeleteCommand / RenderBulkInsertCommand. These render the query, replace every constant literal with a placeholder (e.g. @p0, @p1, ...), and return both the SQL and the captured values:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("city"), SqlExpression.String("New York"), CompareOperator.Equal));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("age"), SqlExpression.Number(30), CompareOperator.Greater));

RenderedCommand cmd = new SqlServerRenderer().RenderSelectCommand(query);

// cmd.Sql:        select [name] from [customers] where ([city] = @p0 and [age] > @p1)
// cmd.Parameters: [ { @p0, "New York", String }, { @p1, 30, Number } ]

using var dbCmd = connection.CreateCommand();
dbCmd.CommandText = cmd.Sql;
foreach (var p in cmd.Parameters)
    dbCmd.Parameters.Add(new SqlParameter(p.Name, p.Value ?? DBNull.Value));

Dialect placeholder prefix:

Renderer	Prefix
SqlServerRenderer	@p0
MySqlRenderer	@p0
SQLiteRenderer	@p0
PostgreSqlRenderer	@p0 (works with Npgsql)
OracleRenderer	:p0

Override SqlOmRenderer.FormatParameterName(int) to customise.

18. Paging

Implementing pagination for large result sets:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.Columns.Add(new SelectColumn("email"));
query.FromClause.BaseTable = FromTerm.Table("customers");

// ORDER BY is required for paging
query.OrderByTerms.Add(new OrderByTerm("name", OrderByDirection.Ascending));

SqlServerRenderer renderer = new SqlServerRenderer();

// First, get total row count
string rowCountSql = renderer.RenderRowCount(query);
int totalRows = (int)ExecuteScalar(rowCountSql);

// Then get paged results
int pageIndex = 0; // Zero-based
int pageSize = 10;
string sql = renderer.RenderPage(pageIndex, pageSize, totalRows, query);

SQL Server OFFSET / FETCH paging (SQL Server 2012+)

By default, SqlServerRenderer emits a nested ROW_NUMBER() wrapper so the generated SQL works on SQL Server 2005+. If you're targeting SQL Server 2012 or later, enable the more efficient OFFSET ... FETCH NEXT form:

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name"));
query.FromClause.BaseTable = FromTerm.Table("customers");
query.OrderByTerms.Add(new OrderByTerm("name", OrderByDirection.Ascending));
query.SetPaging(pageIndex: 2, pageSize: 10);

SqlServerRenderer renderer = new SqlServerRenderer { UseOffsetFetchPaging = true };
string sql = renderer.RenderSelect(query);
// Result: select [name] from [customers] order by [name] asc offset 20 rows fetch next 10 rows only

PostgreSQL, MySQL, and SQLite renderers natively use LIMIT / OFFSET and need no flag.

19. Subqueries

Using subqueries in WHERE clauses:

FromTerm tCustomers = FromTerm.Table("customers", "c");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("email", tCustomers));
query.FromClause.BaseTable = tCustomers;

// IN subquery
SelectQuery subQuery = new SelectQuery();
subQuery.Columns.Add(new SelectColumn("customerId"));
subQuery.FromClause.BaseTable = FromTerm.Table("orders");
subQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("orderDate"), 
    SqlExpression.Date(DateTime.Now.AddDays(-30)), 
    CompareOperator.GreaterOrEqual));

query.WherePhrase.Terms.Add(WhereTerm.CreateIn(
    SqlExpression.Field("customerId", tCustomers), 
    subQuery));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);

20. EXISTS and NOT EXISTS

Using EXISTS clauses:

FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tOrders = FromTerm.Table("orders", "o");

SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn("email", tCustomers));
query.FromClause.BaseTable = tCustomers;

// EXISTS subquery
SelectQuery existsQuery = new SelectQuery();
existsQuery.Columns.Add(new SelectColumn("*"));
existsQuery.FromClause.BaseTable = tOrders;
existsQuery.WherePhrase.Terms.Add(WhereTerm.CreateCompare(
    SqlExpression.Field("customerId", tOrders), 
    SqlExpression.Field("customerId", tCustomers), 
    CompareOperator.Equal));

query.WherePhrase.Terms.Add(WhereTerm.CreateExists(existsQuery));

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(query);
// Result: SELECT [c].[name], [c].[email] FROM [customers] [c] 
//         WHERE EXISTS (SELECT * FROM [orders] [o] WHERE [o].[customerId] = [c].[customerId])

21. Cross-Tabs (Pivot Tables) - Advanced

Creating dynamic pivot tables for reporting:

PivotTable pivot = new PivotTable();
pivot.BaseSql = "select * from orders";
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantity";
pivot.RowField = "customerId";

// Date-based pivot column
PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("before2023", 
    new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("y2023", 
    new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("after2023", 
    new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);

// Product-based pivot column
pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product2", 2));
pivot.Columns.Add(pivotCol);

SelectQuery pivotQuery = pivot.BuildPivotSql();

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(pivotQuery);

22. Cross-Tab Drill-Down - Advanced

Drilling down into pivot table cells:

// Using the same pivot instance from example 18
SelectQuery drillDownQuery = pivot.BuildDrillDownSql(
    SqlConstant.Number(1), // customerId = 1
    "y2023"                // column name
);

SqlServerRenderer renderer = new SqlServerRenderer();
string sql = renderer.RenderSelect(drillDownQuery);

Requirements

• .NET 9.0 or higher

License

See License.txt for license information.

Contributing

Contributions are welcome! If you find a bug or have a feature request, please open an issue on the project repository.

Version History

2.0.0

Breaking changes

• All built-in collection types (SelectColumnCollection, WhereTermCollection, OrderByTermCollection, GroupByTermCollection, UpdateTermCollection, WhereClauseCollection, CaseTermCollection, CommonTableExpressionCollection, SqlConstantCollection, InsertQueryCollection, internal JoinCollection) now derive from System.Collections.ObjectModel.Collection<T> instead of the legacy non-generic CollectionBase.
  • The familiar surface (Add, Contains, IndexOf, Insert, Remove, indexer, foreach, Count, Clear) keeps working unchanged.
  • Add(T) now returns void instead of int (previously the inserted index).
  • The typed nested Enumerator classes (e.g. WhereTermCollection.WhereClauseEnumerator) were removed — use foreach or IEnumerator<T>.
  • The "copy from same collection type" constructor now accepts IEnumerable<T> (which still covers same-type collections, plus arrays and any other enumerable).
  • AddRange now accepts IEnumerable<T> (covers arrays and collections of the same element type).
• Target framework is now net9.0 only. The netstandard2.1 and net8.0 targets have been dropped — consumers must target .NET 9 or higher.
• Recompilation against the 2.0 assembly is required. Source code that uses the typical patterns compiles unchanged in nearly all cases.

New features

• RenderedCommand / RenderedParameter plus RenderSelectCommand / RenderInsertCommand / RenderUpdateCommand / RenderDeleteCommand / RenderBulkInsertCommand on every renderer. These render the SQL with parameter placeholders (@p0, @p1, ...) and return both the SQL string and the captured constant values, so query output can be safely bound through ADO.NET / Dapper / Npgsql / etc. without the renderer ever inlining a value.
• Oracle uses :p0 placeholders; SQL Server / MySQL / SQLite / PostgreSQL use @p0. Override SqlOmRenderer.FormatParameterName(int) to customise.
• SqlOMExtensions.BeginAliasScope() is now reliable for nested or parallel query construction: sub-query constructors invoked inside a manual scope no longer wipe the parent scope's alias counter.
• CommonTableExpressionCollection.Clone() now preserves CommonTableExpression.IsRecursive (previously silently dropped on clone).

1.2.0

New features

• Full C# nullable-reference-type (NRT) annotations across the public API. Callers get accurate null-flow analysis for FromTerm, SelectColumn, OrderByTerm, GroupByTerm, UpdateTerm, WhereTerm, SqlExpression, and the InsertQuery / UpdateQuery / DeleteQuery / BulkInsertQuery families
• SqlOMExtensions.BeginAliasScope() — an IDisposable that isolates auto-generated table aliases (FromTerm.Table<T>()) to a using block; lets you build nested or parallel queries without bleeding alias counters
• SQLite renderer rewritten with feature parity: Common Table Expressions, LIMIT/OFFSET paging, RenderPage, and SQLite-native ifnull(). Unsupported WITH CUBE / WITH ROLLUP now throw InvalidQueryException instead of silently emitting invalid SQL

Bug fixes

• Identifier rendering now escapes the closing-quote character inside identifier bodies (doubles ], ", or ` — standard SQL rule). Defense-in-depth against identifier-injection via user-supplied table/column names
• SQLiteRenderer.Constant() inherited the same "missing else before Number" bug that the base renderer had in 1.0.x — now fixed, uses InvariantCulture, and applies SqlEncode

1.1.0

New features

• Recursive CTE support (WITH RECURSIVE) via CommonTableExpression.IsRecursive
• SQL Server OFFSET/FETCH paging mode: new SqlServerRenderer { UseOffsetFetchPaging = true }
• Added netstandard2.1 target alongside net8.0 and net9.0
• Source Link, deterministic builds, and symbol packages (.snupkg) for better debugging

Bug fixes

• PostgreSQL renderer no longer emits T-SQL top N or with cube / with rollup; it now uses LIMIT for Top and throws a clear InvalidQueryException for the cube/rollup modifiers
• SelectQuery.Clone() now clones the HAVING phrase (was silently dropped)
• ReadUncommitted no longer leaks with (nolock) to Oracle, PostgreSQL, MySQL, or SQLite renderers
• Constant() rendering: added the missing else branch for Number, switched to InvariantCulture, and applies SqlEncode to string/guid literals for defense-in-depth
• Identifier casing is now culture-invariant (ToUpperInvariant) — fixes the Turkish-locale bug
• IS NULL / IS NOT NULL / WHERE rendering no longer produces double spaces

Performance / cleanup

• ByteArrayToHexString uses Convert.ToHexString on .NET 5+
• Replaced AppendFormat-with-no-args calls with direct Append
• Replaced the if (item != collection[0]) anti-pattern in rendering loops with index-based iteration
• Removed large blocks of commented-out dead code

1.0.9

• SqlOMExtensions promoted to public for using static convenience
• New Table<T>(), Field<T>(), ColumnName<T>(), GenerateSelectQuery<T>() convenience methods

1.0.8

• SelectColumnCollection.Add<T>() and AddAllColumns<T>() are now instance methods
• SelectQuery.For<T>() factory method for quick typed query creation
• SqlConstantCollection.FromGuids() factory method

1.0.7

• All query constructors automatically reset alias tracking
• Table<T>() auto-generates unique aliases (a, a2, a3... on collision)

1.0.4

• TableName<T>() now auto-pluralizes type names (Activity → Activities)
• AddAllColumns<T>() auto-skips navigation properties and [NotMapped]
• New Pluralize() helper for common English noun pluralization
• No need to add [IgnoreColumn] to navigation properties

1.0.3

• Attribute-based strongly-typed query building
• New attributes: [TableName], [TableAlias], [ColumnName], [IgnoreColumn]
• Helper methods: Table<T>(), Field<T>(), ColumnName<T>(), AddAllColumns<T>()
• Auto-aliasing when [ColumnName] differs from property name
• GenerateSelectQuery<T>() for quick query scaffolding

1.0.2

• Enhanced documentation with comprehensive examples
• Examples organized from simple to complex
• Added AI tool instruction note
• Improved README structure and clarity

1.0.1

• Initial release, originated from a former SourceForge project
• Modernized codebase with C# 12 features
• Multi-targets .NET 8.0 and .NET 9.0
• Improved nullable reference type support
• Enhanced code quality and performance
• File-scoped namespaces throughout
• Modern switch expressions
• Improved string handling and StringBuilder usage
• Better immutability with readonly modifiers