SqlBuilder

Copyright © 2024 Kvr.SqlBuilder. All rights reserved.

A lightweight, fluent SQL query builder for .NET that provides type-safe SQL query construction with support for table aliases, column selection, and complex joins. With Table and Column attributes annotations support. RawSql methods support for appending raw sql script to builder. Could be used with Dapper, NPoco, etc.

Table of Contents

• Features
• Installation
• Quick Start Guide
• Usage
  • Table Names
  • Column Selection
  • Joins
  • Where Clauses
  • Order By
  • Naming Conventions
  • Raw Sql
• Best Practices
• Limitations
• Supported Frameworks
• Version History
• License
• Contributing
• Dependencies
• Support
• Build Status

Features

• Fluent API for building SQL queries
• Type-safe property and table selection
• Support for table aliases and prefixes
• Automatic handling of SQL Server and standard SQL syntax
• Support for complex JOIN operations
• Column selection with aliasing
• WHERE clause construction with multiple conditions
• ORDER BY clause support
• Minimal boilerplate code
• TableAttribute and ColumnAttribute annotations support

Installation

You can install the package via NuGet Package Manager:

dotnet add package Kvr.SqlBuilder

Quick Start Guide

Here's a quick example of how to use SqlBuilder to construct SQL queries:

using Kvr.SqlBuilder;

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public decimal Amount { get; set; }
}

// Basic query
var query = SqlBuilder.Create()
    .SelectAll<Customer>()
    .From<Customer>()
    .Build();
// Result: SELECT [Id], [Name], [Email] FROM [Customers]

// Join query with conditions
var joinQuery = SqlBuilder.Create()
    .SelectAll<Customer>(out var customerPrefix)
    .SelectAll<Order>(out var orderPrefix)
    .From<Customer>()
    .Join<Customer, Order, int>(
        customer => customer.Id,
        order => order.CustomerId)
    .Where<Customer>(c => c.Id, 1)
    .OrderBy<Order>(o => o.Amount, false)
    .Build();
// Result: SELECT kvr0.[Id], kvr0.[Name], kvr0.[Email], kvr1.[OrderId], kvr1.[CustomerId], kvr1.[Amount] 
//        FROM [Customers] kvr0 
//        JOIN [Orders] kvr1 ON kvr0.[Id] = kvr1.[CustomerId] 
//        WHERE kvr0.[Id] = 1 
//        ORDER BY kvr1.[Amount] DESC

Usage

Table Names

SqlBuilder supports both singular and plural table names, with customization options:

// Global setting for plural table names
SqlBuilder.UseGlobalPluralTableNames();

// Instance-specific setting
var builder = SqlBuilder.Create()
    .UsePluralTableNames()
    .SelectAll<Customer>()
    .From<Customer>();

// Custom table name mapping
SqlBuilder.MapGlobalTable<Customer>("tbl_customers");
// or instance-specific
builder.MapTable<Customer>("tbl_customers");

Column Selection

Multiple ways to select columns:

// Select all columns
SqlBuilder.Create().SelectAll<Customer>();

// Return c.Id as first column, it is important for Dapper's splitOn parameter
SqlBuilder.Create().SelectAll<Customer>(c => c.Id);

// Select specific columns
SqlBuilder.Create().Select<Customer>(c => new[] { c.Id, c.Name });

// Select with alias
SqlBuilder.Create().Select<Customer>(c => c.Name, "CustomerName");

// Exclude specific columns
SqlBuilder.Create().SelectAll<Customer>(excludeColumns: new[] { c => c.Email });

// Exclude specific column Email. Return c.Id as first column, it is important for Dapper's splitOn parameter
SqlBuilder.Create().SelectAll<Customer>(excludeColumns: new[] { c => c.Email }, c => c.Id);

Joins

Support for various JOIN types (Note: For joins, the left table must be used during sql building, otherwise will cause error):

// INNER JOIN
SqlBuilder.Create().Join<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// LEFT JOIN
SqlBuilder.Create().LeftJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// RIGHT JOIN
SqlBuilder.Create().RightJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

// FULL JOIN
SqlBuilder.Create().FullJoin<Customer, Order, int>(
    customer => customer.Id,
    order => order.CustomerId);

Where Clauses

Building WHERE conditions:

    SqlBuilder.Create()
    .Where<Customer>(c => c.Id, "1")
    .And<Customer>(c => c.Name, "'John'")
    .Or<Customer>(c => c.Email, "'[email protected]'");

NameConvention

SqlBuilder supports custom naming conventions which implements INameConvention interface:

SqlBuilder.UseGlobalNameConvention(new CustomNameConvention());

There are two built-in naming conventions: DefaultNameConvention and SnakeCaseNameConvention. Both of them have UseSqlServer property to specify whether to use SQL Server specific syntax to escape identifiers with square brackets and UsePlural property to specify whether to use plural table names.

Table Name determination priority:

1. Custom table name mapping (Highest priority)

• using MapGlobalTable or MapTable method to map table names which could not inherit from CustomNameConvention

// Global mapping
SqlBuilder.MapGlobalTable<Customer>("tbl_customers");
// or instance-specific mapping
this.sqlBuilder.MapTable<Customer>("tbl_customers");

2. TableAttribute on the entity class to specify the table name

[Table("tbl_customers")]
public class Customer

3. ToTableName method in CustomNameConvention to convert the table name

protected override string ToTableName(string typeName)

Column Name determination priority:

1. Custom column name sepecified in Select method (Highest priority)

   this.sqlBuilder.Select<Customer>("customer_id", "Name");

2. ColumnAttribute on the property to specify the column name

   [Column("customer_id")]
   public int Id { get; set; }

3. ToColumnName method in CustomNameConvention to convert the column name

   protected override string ToColumnName(string propertyName)

RawSql

SqlBuilder supports RawSql methods to build WHERE, AND, OR, ORDER BY clauses, also RawSql methods to append raw sql script to builder:

builder.SelectAll<Customer>().RawSql(", count(*) as TotalCount") // append total count of customers to the result
    .Where<Customer>(c => c.Email, "@customerEmail") // using parameter
    .Or<Customer>("@customerEmail is null") // using raw sql string for parameter condition
    .OrderBy<Customer>("Id desc")
    .RawSql(" having count(*) > 0") // using raw sql string for having clause
    .Build();
// Result: SELECT [Id], [Name], [Email], count(*) as TotalCount FROM [Customers] WHERE [Email] = @customerEmail OR @customerEmail is null ORDER BY Dd desc having count(*) > 0

Best Practices

• Use strongly-typed expressions when possible
• Leverage table aliases for complex queries
• Keep SQL Server compatibility in mind
• Use appropriate column selection instead of SELECT *
• Consider query performance when building complex JOINs

Limitations

1. Limited support for complex subqueries (could use RawSql methods for this)
2. No direct support for GROUP BY and HAVING clauses
3. No NamingConvention support for table and column (e.g. SnakeCase, PascalCase for table and column names)

Supported Frameworks

• .NET Standard 2.0+
• .NET 5.0+
• .NET 6.0+
• .NET 7.0+

Version History

• 1.2.7.1

  • Add fromBegin parameter to SelectAll method to specify whether to insert at the beginning of the SELECT clause. If true, will insert with comma if needed.

• 1.2.7

  • Update the code to support mix order of select and from. Previously, the order of select and from must be select first and from later. Now, the order of select and from could be mixed.

• 1.2.6

  • Add NotMappedAttribute support for SelectAll method to exclude columns.

• 1.2.5

  • Add SelectAll overload methods for more than 10 types.

• 1.2.4

  • Add SelectFrom method to generate a SELECT * FROM clause.

• 1.2.3

  • Add SelectFromWhere method to generate a SELECT * FROM WHERE clause with a WHERE clause to filter by a specific property value.

• 1.2.2

  • Fix incorrect alias for snake case name convention.
  • Add test project and cases.

• 1.2.1

  • Add prefix for missing Where, Join, Select, From, OrderBy methods.

• 1.2.0

  • Add NameConvention support

• 1.1.0

  • Add RawSql methods (Where, And, Or, OrderBy) and AppendRawSql method
  • Change parameter type from LambdaExpression to Expression<Func<T, object>> for strongly-typed expressions

• 1.0.0

  • Initial release
  • Basic SQL query building
  • Support for JOINs, WHERE clauses, and ORDER BY
  • Table name customization
  • SQL Server compatibility

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

Dependencies

System.ComponentModel.Annotations for Table and Column attributes

Support

If you encounter any issues or have questions, please file an issue on the GitHub repository.

Build Status