ScopeFunction.GenericSqlBuilder 1.3.0

ScopeFunction.GenericSqlBuilder

A fluent, strongly-typed SQL query builder for .NET. Write maintainable SQL queries with compile-time safety on property names.

Overview

I wrote this package to make writing SQL queries that need to be maintainable a little bit more pleasant in certain use cases. The most common use case for this might be when using Dapper or when writing raw NoSQL queries for CosmosDB or any other database where this is relevant.

What is the point?

Mainly to have a C# based query syntax with strongly typed property names. Therefore, if a property name changes in your code base, all your queries are still valid. Another perk is that should you need to migrate your queries to another platform, you can just change a few options and "Bobs your uncle!" -- it should work.

Supported Platforms

• Default (no variant-specific formatting)
• CosmosDb
• MySql / MariaDb
• SqlServer (MsSql)
• PostgreSql

Package Behaviour

• When no prefix options are provided the table name will be appended to all SELECT, WHERE and ORDER BY clauses.
• If no casing options are provided the properties will remain with the default casing of the nameof(T) string output.

SELECT Statements

SelectAll

var sql = new SqlBuilder()
    .SelectAll()
    .From("people")
    .Build();
// SELECT * FROM people

SelectAll with Options

var sql = new SqlBuilder()
    .SelectAll(o => o.WithPropertyPrefix("p"))
    .From("people")
    .Build();
// SELECT * FROM people

Select with String Array

var sql = new SqlBuilder()
    .Select(new[] { "FirstName", "LastName", "Age" })
    .From("people")
    .Build();
// SELECT people.FirstName, people.LastName, people.Age FROM people

Generic Select (Reflection-Based)

Automatically selects all public read/write properties from the type:

var sql = new SqlBuilder()
    .Select<Person>()
    .From("p")
    .Build();
// SELECT p.FirstName, p.LastName, p.Age FROM p

Generic Select with Options

var sql = new SqlBuilder()
    .Select<Person>(o =>
    {
        o.WithoutProperty(nameof(Person.Age));
        o.WithPropertyPrefix("p");
    })
    .From("people")
    .Build();
// SELECT p.FirstName, p.LastName FROM people

Select with Explicit Properties (Typed)

var sql = new SqlBuilder()
    .Select<Person>(s => new[]
    {
        nameof(s.FirstName),
        nameof(s.LastName)
    }, o => o.WithTop(10).WithPropertyPrefix("p"))
    .From("people as p")
    .Build();
// SELECT TOP 10 p.FirstName, p.LastName FROM people as p

Select without FROM (Direct Build)

You can build a SELECT statement without a FROM clause for scalar queries or partial query composition:

var sql = new SqlBuilder()
    .Select("1")
    .Build();
// SELECT 1

var sql = new SqlBuilder()
    .SelectAll()
    .Build();
// SELECT *

var sql = new SqlBuilder()
    .Select<Person>(o => o.WithPropertyPrefix("p"))
    .AppendSelect(a => a.Select<Car>(o => o.WithPropertyPrefix("c")))
    .Build();
// SELECT p.FirstName, p.LastName, p.Age, c.Make, c.Model, c.Year, c.Age

DISTINCT

var sql = new SqlBuilder()
    .Select<Person>(o => o.WithDistinct())
    .From("people")
    .Build();
// SELECT DISTINCT people.FirstName, people.LastName, people.Age FROM people

var sql = new SqlBuilder()
    .SelectAll(o => o.WithDistinct())
    .From("people")
    .Build();
// SELECT DISTINCT * FROM people

// Combined with TOP:
var sql = new SqlBuilder()
    .Select<Person>(s => new[] { nameof(s.FirstName) },
        o => o.WithDistinct().WithTop(5))
    .From("people")
    .Build();
// SELECT DISTINCT TOP 5 people.FirstName FROM people

AppendSelect (Multi-Table Joins)

Chain multiple typed selects for join queries:

var sql = new SqlBuilder()
    .Select<Person>(o =>
    {
        o.WithPropertyPrefix("p");
        o.WithProperty("some_alias");
    })
    .AppendSelect(a =>
    {
        a.Select<Car>(o =>
        {
            o.WithPropertyPrefix("c");
            o.WithProperty("another_alias");
        });
    })
    .From("persons p")
    .InnerJoin("cars c")
    .On("c.person_id = p.id")
    .Build();
// SELECT p.FirstName, p.LastName, p.Age, p.some_alias, c.Make, c.Model, c.Year, c.Age, c.another_alias
//   FROM persons p INNER JOIN cars c ON c.person_id = p.id

SplitOn (for Dapper Multi-Mapping)

var sql = new SqlBuilder()
    .Select<Person>(o =>
    {
        o.WithSplitOn(nameof(Person.Age));
        o.WithPropertyPrefix("p");
    })
    .AppendSelect(a => a.Select<Car>(o => o.WithPropertyPrefix("c")))
    .From("p")
    .LeftJoin("c")
    .On("c.id = p.car_id")
    .Build();
// SELECT p.Age, p.FirstName, p.LastName, c.Age, c.Make, c.Model, c.Year
//   FROM p LEFT JOIN c ON c.id = p.car_id

WHERE Clauses

Simple String WHERE

var sql = new SqlBuilder()
    .SelectAll()
    .From("people")
    .Where("age > 18")
    .Build();
// SELECT * FROM people WHERE age > 18

Typed WHERE with Array

var sql = new SqlBuilder()
    .Select<Person>()
    .From("c")
    .Where<Person>(p => new[]
    {
        $"{nameof(p.Age)} = 18",
        $"{nameof(p.Age)} = 20"
    }, w => w.WithAndSeparator())
    .Build();
// SELECT c.FirstName, c.LastName, c.Age FROM c WHERE c.Age = 18 AND c.Age = 20

WHERE with Condition Builder

var sql = new SqlBuilder()
    .SelectAll(o => o.WithPropertyPrefix("p"))
    .From("people")
    .Where<Person>(p => nameof(p.Age), w => w.EqualsNumber(50))
    .Build();
// SELECT * FROM people WHERE p.Age = 50

Available condition methods:

• Equals(string) -- verbatim value: = value
• EqualsString(string) -- quoted string: = 'value'
• EqualsNumber(int) -- numeric: = 123
• Like(string) -- quoted LIKE: LIKE 'value'
• VerbatimLike(string) -- unquoted LIKE: LIKE value
• Append(string) -- raw append

WHERE with Outer Group

.Where<Person>(p => new[]
{
    $"{nameof(p.Age)} = 18",
    $"{nameof(p.Age)} = 20"
}, w =>
{
    w.WithAndSeparator();
    w.WithOuterGroup();
})
// WHERE (Age = 18 AND Age = 20)

AppendWhere / AppendWhereIf

var sql = new SqlBuilder()
    .Select<Person>(o => o.WithPropertyPrefix("p"))
    .From("people p")
    .Where("p.Id = 12")
    .AppendWhere(w => w.Where("p.Age = @Age", o => o.WithAndSeparator()))
    .Build();
// SELECT p.FirstName, p.LastName, p.Age FROM people p WHERE p.Id = 12 AND p.Age = @Age

// Conditional WHERE:
.AppendWhereIf(() => someCondition, f =>
    f.Where("status = @Status", o => o.WithAndSeparator()))

Nested WHERE

var sql = new SqlBuilder()
    .Select("o.id")
    .From("orders o")
    .Where("o.business_id = @BusinessId")
    .Append("AND")
    .AppendWhereIf(() => true, f =>
        f.NestedWhere(o =>
        {
            o.Where("c.email LIKE @Filter", w => w.WithoutSeparator());
        }))
    .Build();
// SELECT o.id FROM orders o WHERE o.business_id = @BusinessId AND ( c.email LIKE @Filter )

JOINs

All join types return a JoinStatement which requires .On():

var sql = new SqlBuilder()
    .Select<Person>()
    .From("p")
    .LeftJoin("q").On("q.p_id = p.id")
    .InnerJoin("o").On("o.q_id = q.id")
    .RightJoin("r").On("r.o_id = o.id")
    .Join("u").On("u.r_id = r.id")
    .Build();

Supported: Join, LeftJoin, RightJoin, InnerJoin.

ORDER BY

// Simple
.OrderBy("Age")

// With direction
.OrderBy("Age", o => o.WithDesc())

// Multiple columns
.OrderBy(new[] { "FirstName", "LastName" }, o => o.WithDesc())

// Typed
.OrderBy<Person>(p => nameof(p.Age), o => o.WithDesc())

// Typed with multiple columns
.OrderBy<Person>(p => new[] { nameof(p.FirstName), nameof(p.LastName) })

GROUP BY and HAVING

Available from both FromStatement (no WHERE) and SelectWhereStatement (after WHERE):

// GROUP BY without WHERE
var sql = new SqlBuilder()
    .Select("department, COUNT(*) as count")
    .From("employees")
    .GroupBy("department")
    .Build();
// SELECT department, COUNT(*) as count FROM employees GROUP BY department

// GROUP BY with HAVING
var sql = new SqlBuilder()
    .Select("department, COUNT(*) as count")
    .From("employees")
    .GroupBy("department")
    .Having("COUNT(*) > 5")
    .Build();
// SELECT department, COUNT(*) as count FROM employees GROUP BY department HAVING COUNT(*) > 5

// GROUP BY with multiple columns
var sql = new SqlBuilder()
    .Select("department, role, COUNT(*)")
    .From("employees")
    .GroupBy(new[] { "department", "role" })
    .Build();

// After WHERE, with ORDER BY
var sql = new SqlBuilder()
    .Select("department, COUNT(*) as count")
    .From("employees")
    .Where("active = 1")
    .GroupBy("department")
    .Having("COUNT(*) > 5")
    .OrderBy("count", o => o.WithDesc())
    .Build();

INSERT Statements

Simple Insert

var sql = new SqlBuilder()
    .Insert("FirstName", "LastName", "Age")
    .Into("people")
    .Build();
// INSERT INTO people (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)

Generic Insert (Reflection-Based)

var sql = new SqlBuilder()
    .Insert<Person>()
    .Into("people")
    .Build();
// INSERT INTO people (FirstName, LastName, Age) VALUES (@FirstName, @LastName, @Age)

Insert with Options

var sql = new SqlBuilder()
    .Insert<Person>(o =>
    {
        o.WithoutProperty(nameof(Person.Age));
        o.WithPropertyCasing(Casing.SnakeCase);
    })
    .Into("people")
    .Build();
// INSERT INTO people (first_name, last_name) VALUES (@FirstName, @LastName)

Insert with ON DUPLICATE KEY UPDATE

// Update specific properties on duplicate:
var sql = new SqlBuilder()
    .Insert<Person>(o =>
    {
        o.WithUpdateOnDuplicateKey(p => p.FirstName, p => p.LastName);
    })
    .Into("people")
    .Build();

// Update all except specific properties:
var sql = new SqlBuilder()
    .Insert<Person>(o =>
    {
        o.WithUpdateOnDuplicateKeyIgnore(p => p.Age);
    })
    .Into("people")
    .Build();

Insert with Last Inserted ID

var sql = new SqlBuilder()
    .Insert<Person>(o =>
    {
        o.WithSqlVariant(Variant.MsSql);
        o.WithAppendedLastInsertedId();
    })
    .Into("people")
    .Build();
// MsSql:      INSERT INTO people (...) VALUES (...); SELECT SCOPE_IDENTITY();
// MySql:      INSERT INTO people (...) VALUES (...); SELECT LAST_INSERT_ID();
// PostgreSql: INSERT INTO people (...) VALUES (...) RETURNING id;

UPDATE Statements

Simple Update

var sql = new SqlBuilder()
    .Update("people")
    .Set("FirstName = @FirstName, LastName = @LastName")
    .Where("Id = @Id")
    .Build();
// UPDATE people SET FirstName = @FirstName, LastName = @LastName WHERE Id = @Id

Generic Update (Reflection-Based)

var sql = new SqlBuilder()
    .Update<Person>("people")
    .Set()
    .Where("Id = @Id")
    .Build();
// UPDATE people SET FirstName = @FirstName, LastName = @LastName, Age = @Age WHERE Id = @Id

Update with Options

var sql = new SqlBuilder()
    .Update<Person>("people", o =>
    {
        o.WithoutProperties(p => p.Age);
        o.WithPropertyCasing(Casing.SnakeCase);
    })
    .Set()
    .Where("id = @Id")
    .Build();
// UPDATE people SET first_name = @FirstName, last_name = @LastName WHERE id = @Id

Update WHERE with Conditions

var sql = new SqlBuilder()
    .Update("people")
    .Set("name = @Name")
    .Where("id", w => w.EqualsNumber(1))
    .And("active", w => w.EqualsNumber(1))
    .Build();
// UPDATE people SET name = @Name WHERE id = 1 AND active = 1

DELETE Statements

Simple Delete

var sql = new SqlBuilder()
    .Delete("people")
    .Where("id = @Id")
    .Build();
// DELETE FROM people WHERE id = @Id

Delete with Chained Conditions

var sql = new SqlBuilder()
    .Delete("people")
    .Where("age < 18")
    .And("active = 0")
    .Or("deleted = 1")
    .Build();
// DELETE FROM people WHERE age < 18 AND active = 0 OR deleted = 1

Delete without WHERE

var sql = new SqlBuilder()
    .Delete("people")
    .Build();
// DELETE FROM people

Delete with SQL Variant

var sql = new SqlBuilder()
    .Delete("people", o => o.WithSqlVariant(Variant.MsSql))
    .Where("id = @Id")
    .Build();
// DELETE FROM people WHERE id = @Id;

Static Query Builder

Use the static Query method for a more concise syntax:

using static ScopeFunction.GenericSqlBuilder.GenericSqlBuilder;

var sql = Query(q => q
    .SelectAll(o => o.WithPropertyPrefix("p"))
    .From("people")
    .Where<Person>(p => nameof(p.Age), w => w.EqualsNumber(50))
    .OrderBy<Person>(o => nameof(o.Age))
);
// SELECT * FROM people WHERE p.Age = 50 ORDER BY p.Age ASC

Global Configuration

Set default options for all queries:

GenericSqlBuilder.Configure(c =>
{
    c.WithDefaultPropertyCase(Casing.SnakeCase);
    c.WithDefaultSqlVariant(Variant.PostgreSql);
});

Available Casings

Default, UpperCase, LowerCase, KebabCase, PascalCase, SnakeCase, CamelCase

Available SQL Variants

Default, MySql, MsSql, PostgreSql, CosmosDb

Variant-specific behaviour:

• MsSql/MySql/PostgreSql -- appends ; to the end of statements
• MsSql -- wraps column names with [brackets]
• MySql -- wraps column names with `backticks`

Attributes

IgnoreProperty

Exclude a property from reflection-based builders:

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    [IgnoreProperty]
    public string FullName => $"{FirstName} {LastName}";
}

ColumnName

Override the column name for a property:

public class Person
{
    [ColumnName("first_name")]
    public string FirstName { get; set; }
}

Fluent API Chain Reference

The builder enforces valid SQL ordering through its type system:

SqlBuilder
  -> SelectAll / Select / Select<T>    -> SelectStatement
  -> Insert / Insert<T>               -> InsertStatement
  -> Update / Update<T>               -> UpdateStatement
  -> Delete                           -> DeleteStatement

SelectStatement
  -> From          -> FromStatement
  -> AppendSelect  -> SelectStatement
  -> Build()

FromStatement
  -> Where         -> SelectWhereStatement
  -> Join/LeftJoin/RightJoin/InnerJoin -> JoinStatement -> On -> FromStatement
  -> GroupBy       -> GroupByStatement
  -> Append        -> FromStatement
  -> Build()

SelectWhereStatement
  -> And / Or      -> chain or FromStatement
  -> AppendWhere   -> SelectWhereStatement
  -> OrderBy       -> OrderByStatement
  -> GroupBy       -> GroupByStatement
  -> Build()

GroupByStatement
  -> Having        -> GroupByStatement
  -> OrderBy       -> OrderByStatement
  -> Build()

OrderByStatement
  -> Append        -> OrderByStatement
  -> Build()

InsertStatement
  -> Into          -> Finalise -> Build()

UpdateStatement
  -> Set           -> UpdateSetStatement -> Where -> UpdateWhereStatement -> Build()

DeleteStatement
  -> Where         -> DeleteWhereStatement -> And/Or -> chain
  -> Build()

Contributions and Bug Fixes

If you have any suggestions, bug fixes or features you would like to add to improve this code, feel free to tag me in a PR and I will have a look as soon as possible.

Cheerio! C.K.