C#.NET SqlKata 使用详解：优雅构建动态 SQL 查询

简介

在复杂项目中，为了保持 SQL 灵活性与可读性，开发者往往需要手写大量拼接字符串或使用 ORMs 附带的 LINQ，但两者各有局限：手写拼接易出错、难以维护；LINQ 在某些场景下生成的 SQL 不够直观或性能不佳。
SqlKata 是一款轻量级、数据库无关的查询构建器（Query Builder），提供——
- 流式 API，链式调用拼装 SQL
- 可切换编译器，支持多种数据库方言（SQL Server、PostgreSQL、MySQL、SQLite、Oracle 等）
- 语法可读，生成的 SQL 与手写风格接近，便于调试和维护

支持环境与安装

目标框架：.NET Standard 2.0+，兼容 .NET Framework 4.6.1 及更高、.NET Core 2.1+、.NET 5/6/7/8+。
安装 NuGet 包：

1Install-Package SqlKata
2

若需内置执行支持（与 Dapper 集成），可安装：

1Install-Package SqlKata.Execution
2

在代码中引入命名空间：

1using SqlKata;
2using SqlKata.Compilers;
3using SqlKata.Execution;
4

数据库驱动

根据目标数据库，安装对应的 ADO.NET 提供程序：

SQL Server：System.Data.SqlClient
MySQL：MySql.Data 或 MysqlConnector
PostgreSQL：Npgsql
SQLite：System.Data.SQLite
Oracle：Oracle.ManagedDataAccess
Firebird：FirebirdSql.Data.FirebirdClient

项目配置

在 ASP.NET Core 项目中，可以通过依赖注入（DI）配置 QueryFactory：

1using Microsoft.Extensions.DependencyInjection;
2using SqlKata;
3using SqlKata.Execution;
4using System.Data.SqlClient;
5
6public class Startup
7{
8    public void ConfigureServices(IServiceCollection services)
9    {
10        services.AddTransient<QueryFactory>(sp =>
11        {
12            var connection = new SqlConnection("你的连接字符串");
13            var compiler = new SqlServerCompiler();
14            return new QueryFactory(connection, compiler);
15        });
16    }
17}
18

核心功能

Query Builder

基本查询：

1var query = new Query("Users")
2    .Select("Id", "Name", "Email")
3    .Where("IsActive", true)
4    .OrderByDesc("CreatedAt")
5    .Limit(10, 20);  // OFFSET 10 ROWS FETCH NEXT 20 ROWS
6

链式拼装：支持 .Where(), .OrWhere(), .WhereIn(), .WhereBetween(), .Join(), .GroupBy(), .Having() 等常用子句。
条件查询

使用 Where 方法添加条件：

1var cars = await db.Query("cars")
2    .Where("price", ">", 20000)
3    .Where("name", "like", "%Audi%")
4    .GetAsync<Car>();
5

动态查询

根据条件动态构建查询：

1public async Task<IEnumerable<Car>> SearchCars(string searchText, int? maxPrice)
2{
3    var query = db.Query("cars");
4
5    if (!string.IsNullOrEmpty(searchText))
6    {
7        query.WhereLike("name", $"%{searchText}%");
8    }
9
10    if (maxPrice.HasValue)
11    {
12        query.Where("price", "<=", maxPrice.Value);
13    }
14
15    return await query.GetAsync<Car>();
16}
17

连接（JOIN）

支持多种连接类型（如内连接、左连接）：

1var query = db.Query("Course")
2    .Join("Department", "Department.ID", "Course.DepartmentID")
3    .LeftJoin("Instructor", "Instructor.ID", "Course.InstructorID")
4    .Select("Course.Title", "Department.Name as DepartmentName")
5    .Where("Department.ID", 1);
6
7var results = await query.GetAsync();
8

子查询和复杂查询

支持子查询和嵌套条件：

1var lastPurchaseQuery = db.Query("Transactions")
2    .Where("Type", "Purchase")
3    .GroupBy("UserId")
4    .SelectRaw("MAX([Date]) as LastPurchaseDate");
5
6var users = await db.Query("Users")
7    .Include("LastPurchase", lastPurchaseQuery)
8    .ForPage(1, 10)
9    .GetAsync();
10

插入、更新和删除

SqlKata 也可以执行插入、更新和删除操作：

1// 插入
2var insertQuery = db.Query("cars").AsInsert(new { name = "BMW", price = 30000 });
3await insertQuery.ExecuteAsync();
4
5// 更新
6var updateQuery = db.Query("cars")
7    .Where("id", 1)
8    .AsUpdate(new { price = 35000 });
9await updateQuery.ExecuteAsync();
10
11// 删除
12var deleteQuery = db.Query("cars")
13    .Where("id", 1)
14    .AsDelete();
15await deleteQuery.ExecuteAsync();
16

编译器（Compiler）

多数据库方言：根据目标数据库，选择对应 Compiler，生成符合方言的 SQL 与参数。

1var compiler = new SqlServerCompiler();
2var sqlResult = compiler.Compile(query);
3
4// sqlResult.Sql => "SELECT [Id], [Name], [Email] FROM [Users] WHERE [IsActive] = @p0 ORDER BY [CreatedAt] DESC OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY"
5// sqlResult.NamedBindings => { p0 = true, p1 = 10, p2 = 20 }
6

参数化安全：所有输入自动转为参数，防止 SQL 注入。

扩展执行层（Execution）

与 Dapper 整合：通过 QueryFactory，可直接执行并映射结果。

1// 创建连接与工厂
2using var connection = new SqlConnection(connectionString);
3var compiler = new SqlServerCompiler();
4var db = new QueryFactory(connection, compiler);
5
6// 查询单条
7var user = await db.Query("Users")
8                   .Where("Id", 123)
9                   .FirstOrDefaultAsync<User>();
10
11// 查询列表
12var activeUsers = await db.Query("Users")
13                          .Where("IsActive", true)
14                          .GetAsync<User>();
15
16// 插入并返回自增 ID
17var newId = await db.Query("Users")
18                    .InsertGetIdAsync<int>(new {
19                        Name = "Bob",
20                        Email = "[email protected]",
21                        CreatedAt = DateTime.UtcNow
22                    });
23

事务支持：在 QueryFactory 上使用 db.Transaction(...) 或手动传入 IDbTransaction。

原生 SQL 混用

Raw SQL：可在查询中插入原生片段，或完全执行自定义语句。

1var query = new Query()
2    .FromRaw("Users u INNER JOIN Orders o ON u.Id = o.UserId")
3    .SelectRaw("u.Id, u.Name, COUNT(o.Id) AS OrderCount")
4    .GroupByRaw("u.Id, u.Name");
5

自定义函数：原样插入函数调用，例如 WhereRaw("DATEDIFF(day, CreatedAt, GETDATE()) < 30")。

常用 API 详解

API	说明
new Query(table)	创建针对指定表的查询对象
.Select(cols…)	指定要查询的列
.Where(col, op, val)	添加 WHERE 条件（支持省略 op，默认为 =）
.OrWhere(...)	添加 OR 条件
.WhereIn(col, array)	WHERE col IN (…)
.WhereBetween(col, lo, hi)	WHERE col BETWEEN lo AND hi
.Join(table, c1, op, c2)	内连接
.LeftJoin(…)/.RightJoin	左/右连接
.GroupBy(cols…)	分组
.Having(...)	HAVING 子句
.OrderBy(col)	升序排序
.OrderByDesc(col)	降序排序
.Limit(offset, count)	分页（SQL Server 使用 OFFSET…FETCH，MySQL/PG 使用 LIMIT）
.Compile(compiler)	生成 SQL 文本与参数
QueryFactory.GetAsync<T>()	执行查询并映射为实体列表
InsertAsync(object)	插入新记录
InsertGetIdAsync<T>(object)	插入并返回自增主键
UpdateAsync(object)	根据实体的主键更新记录
DeleteAsync(object)	根据实体的主键删除记录
QueryFactory.StatementAsync(sql, params)	执行任意 SQL 语句

实现原理

查询构建：SqlKata 使用 Query 类表示 SQL 查询，通过链式方法构造查询树（Query Tree）。
编译器：Compiler（如 SqlServerCompiler、PostgresCompiler）将查询树转换为特定数据库的 SQL 语句，并生成参数化查询。
执行：SqlKata.Execution 包通过 XQuery 类和 Dapper 执行编译后的 SQL，映射结果到 C# 对象。
参数化：SqlKata 默认使用参数化查询，防止 SQL 注入。

性能与对比

与手写 SQL：
- SqlKata 在拼装 SQL 和生成参数时有非常轻量的开销，实际运行时与 Dapper + 手写 SQL 相差极小。
- 优势在于可读性与可维护性，更少的拼接错误和参数绑定麻烦。
与 LINQ to SQL / EF Core：
- LINQ 在复杂联表、子查询场景下生成的 SQL 往往较冗长，且性能优化受限。
- SqlKata 生成的 SQL 与手写几乎一致，开发者可更精细地控制索引使用和执行计划。
跨数据库兼容性：同一套查询构建逻辑，通过不同 Compiler 可无缝切换数据库，减少重复代码和维护成本。

实战项目

1using Microsoft.AspNetCore.Mvc;
2using SqlKata;
3using SqlKata.Execution;
4using System.Data.SqlClient;
5using System.Threading.Tasks;
6
7public class Car
8{
9    public int Id { get; set; }
10    public string Name { get; set; }
11    public int Price { get; set; }
12}
13
14[ApiController]
15[Route("api/cars")]
16public class CarsController : ControllerBase
17{
18    private readonly QueryFactory _db;
19
20    public CarsController(QueryFactory db)
21    {
22        _db = db;
23    }
24
25    [HttpGet]
26    public async Task<IActionResult> Search([FromQuery] string searchText, [FromQuery] int? maxPrice)
27    {
28        var query = _db.Query("cars");
29
30        if (!string.IsNullOrEmpty(searchText))
31        {
32            query.WhereLike("name", $"%{searchText}%");
33        }
34
35        if (maxPrice.HasValue)
36        {
37            query.Where("price", "<=", maxPrice.Value);
38        }
39
40        var cars = await query.GetAsync<Car>();
41        return Ok(cars);
42    }
43}
44

启动配置：

1builder.Services.AddTransient<QueryFactory>(sp =>
2{
3    var connection = new MySqlConnection("Server=localhost;Database=master;User Id=root;Password=");
4    var compiler = new MySqlCompiler();
5    return new QueryFactory(connection, compiler);
6});
7

资源和文档

官方文档：https://sqlkata.com
GitHub 仓库：https://github.com/sqlkata/querybuilder
NuGet 包：
- SqlKata：https://www.nuget.org/packages/SqlKata
- SqlKata.Execution：https://www.nuget.org/packages/SqlKata.Execution

《C#.NET SqlKata 使用详解：优雅构建动态 SQL 查询》是转载文章，点击查看原文。