C#.NET SqlKata 使用詳解：優雅構建動態 SQL 查詢詳情 - c#,.net 唐青楓動態日志

簡介

在複雜項目中，為了保持 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 包：

Install-Package SqlKata

若需內置執行支持（與 Dapper 集成），可安裝：

Install-Package SqlKata.Execution

在代碼中引入命名空間：

using SqlKata;
using SqlKata.Compilers;
using SqlKata.Execution;

數據庫驅動

根據目標數據庫，安裝對應的 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：

using Microsoft.Extensions.DependencyInjection;
using SqlKata;
using SqlKata.Execution;
using System.Data.SqlClient;

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddTransient<QueryFactory>(sp =>
        {
            var connection = new SqlConnection("你的連接字符串");
            var compiler = new SqlServerCompiler();
            return new QueryFactory(connection, compiler);
        });
    }
}

核心功能

Query Builder

基本查詢：

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

鏈式拼裝：支持 .Where(), .OrWhere(), .WhereIn(), .WhereBetween(), .Join(), .GroupBy(), .Having() 等常用子句。
條件查詢

使用 Where 方法添加條件：

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

動態查詢

根據條件動態構建查詢：

public async Task<IEnumerable<Car>> SearchCars(string searchText, int? maxPrice)
{
    var query = db.Query("cars");

    if (!string.IsNullOrEmpty(searchText))
    {
        query.WhereLike("name", $"%{searchText}%");
    }

    if (maxPrice.HasValue)
    {
        query.Where("price", "<=", maxPrice.Value);
    }

    return await query.GetAsync<Car>();
}

連接（JOIN）

支持多種連接類型（如內連接、左連接）：

var query = db.Query("Course")
    .Join("Department", "Department.ID", "Course.DepartmentID")
    .LeftJoin("Instructor", "Instructor.ID", "Course.InstructorID")
    .Select("Course.Title", "Department.Name as DepartmentName")
    .Where("Department.ID", 1);

var results = await query.GetAsync();

子查詢和複雜查詢

支持子查詢和嵌套條件：

var lastPurchaseQuery = db.Query("Transactions")
    .Where("Type", "Purchase")
    .GroupBy("UserId")
    .SelectRaw("MAX([Date]) as LastPurchaseDate");

var users = await db.Query("Users")
    .Include("LastPurchase", lastPurchaseQuery)
    .ForPage(1, 10)
    .GetAsync();

插入、更新和刪除

SqlKata 也可以執行插入、更新和刪除操作：

// 插入
var insertQuery = db.Query("cars").AsInsert(new { name = "BMW", price = 30000 });
await insertQuery.ExecuteAsync();

// 更新
var updateQuery = db.Query("cars")
    .Where("id", 1)
    .AsUpdate(new { price = 35000 });
await updateQuery.ExecuteAsync();

// 刪除
var deleteQuery = db.Query("cars")
    .Where("id", 1)
    .AsDelete();
await deleteQuery.ExecuteAsync();

編譯器（Compiler）

多數據庫方言：根據目標數據庫，選擇對應 Compiler，生成符合方言的 SQL 與參數。

var compiler = new SqlServerCompiler();
var sqlResult = compiler.Compile(query);

// sqlResult.Sql => "SELECT [Id], [Name], [Email] FROM [Users] WHERE [IsActive] = @p0 ORDER BY [CreatedAt] DESC OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY"
// sqlResult.NamedBindings => { p0 = true, p1 = 10, p2 = 20 }

參數化安全：所有輸入自動轉為參數，防止 SQL 注入。

擴展執行層（Execution）

與 Dapper 整合：通過 QueryFactory，可直接執行並映射結果。

// 創建連接與工廠
using var connection = new SqlConnection(connectionString);
var compiler = new SqlServerCompiler();
var db = new QueryFactory(connection, compiler);

// 查詢單條
var user = await db.Query("Users")
                   .Where("Id", 123)
                   .FirstOrDefaultAsync<User>();

// 查詢列表
var activeUsers = await db.Query("Users")
                          .Where("IsActive", true)
                          .GetAsync<User>();

// 插入並返回自增 ID
var newId = await db.Query("Users")
                    .InsertGetIdAsync<int>(new {
                        Name = "Bob",
                        Email = "bob@example.com",
                        CreatedAt = DateTime.UtcNow
                    });

事務支持：在 QueryFactory 上使用 db.Transaction(...) 或手動傳入 IDbTransaction。

原生 SQL 混用

Raw SQL：可在查詢中插入原生片段，或完全執行自定義語句。

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

自定義函數：原樣插入函數調用，例如 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 可無縫切換數據庫，減少重複代碼和維護成本。

實戰項目

using Microsoft.AspNetCore.Mvc;
using SqlKata;
using SqlKata.Execution;
using System.Data.SqlClient;
using System.Threading.Tasks;

public class Car
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Price { get; set; }
}

[ApiController]
[Route("api/cars")]
public class CarsController : ControllerBase
{
    private readonly QueryFactory _db;

    public CarsController(QueryFactory db)
    {
        _db = db;
    }

    [HttpGet]
    public async Task<IActionResult> Search([FromQuery] string searchText, [FromQuery] int? maxPrice)
    {
        var query = _db.Query("cars");

        if (!string.IsNullOrEmpty(searchText))
        {
            query.WhereLike("name", $"%{searchText}%");
        }

        if (maxPrice.HasValue)
        {
            query.Where("price", "<=", maxPrice.Value);
        }

        var cars = await query.GetAsync<Car>();
        return Ok(cars);
    }
}

啓動配置：

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

資源和文檔

官方文檔：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

唐青楓動態日志

@tangqingfeng

標簽

php (185)

Linux (168)

HTTP (69)

c# (66)

.net (58)

intellij-idea (54)

Git (53)

設計模式 (50)

ide (27)

vim (20)

yii2 (20)

composer (19)

動態

C#.NET SqlKata 使用詳解：優雅構建動態 SQL 查詢 - 動態詳情

簡介

支持環境與安裝

數據庫驅動

項目配置

核心功能

Query Builder

編譯器（Compiler）

擴展執行層（Execution）

原生 SQL 混用

常用 API 詳解

實現原理

性能與對比

實戰項目

資源和文檔

Add a new 評論

唐青楓 動態日志

@tangqingfeng

標簽

php (185)

Linux (168)

HTTP (69)

c# (66)

.net (58)

intellij-idea (54)

Git (53)

設計模式 (50)

ide (27)

vim (20)

yii2 (20)

composer (19)

動態

C#.NET SqlKata 使用詳解：優雅構建動態 SQL 查詢 - 動態 詳情

簡介

支持環境與安裝

數據庫驅動

項目配置

核心功能

Query Builder

編譯器（Compiler）

擴展執行層（Execution）

原生 SQL 混用

常用 API 詳解

實現原理

性能與對比

實戰項目

資源和文檔

Add a new 評論

唐青楓動態日志

C#.NET SqlKata 使用詳解：優雅構建動態 SQL 查詢 - 動態詳情