Dapper 的动态查询生成器

引言

DapperQueryBuilder 是 Dapper 的一个封装库，主要用于帮助构建动态 SQL 查询和命令。它基于两个基本原则：

1. 字符串插值，而不是手动使用 DynamicParameters
2. 查询和参数并行处理

这看起来很简单，但你会发现它非常强大。

背景

我们都喜欢 Dapper 微型 ORM。

许多开发者已经意识到，虽然 Entity Framework 提供了一些 Dapper 没有的功能，但 Dapper 仍然比 EF 更灵活（而且速度快得多）。许多人也认识到，为特定任务选择合适的工具有时需要同时使用 EF 和 Dapper。而在构建动态查询方面，Dapper 堪称王者。

动态 SQL

老派开发者（那些从 90 年代末或 2000 年代初开始编程的，可能使用 VB6、ASP3 或 PHP）可能会记得这样的代码：

string sql = "SELECT * FROM [Product] WHERE 1=1";

if (!string.IsNullOrEmpty(Request["ProductName"]))
   sql += " AND Name LIKE '" + Request["ProductName"].Replace("'", "''") + "'"; // this replace!
if (!string.IsNullOrEmpty(Request["SubCategoryId"]))
    sql += " AND ProductSubcategoryID = " + 
             Request["SubCategoryId"].Replace("'", "''");  // this replace!

// here is where you pray that you've correctly sanitized inputs against sql-injection
var products = cn.Query<Product>(sql);

基本上，我们都是直接将动态 SQL 语句发送到数据库。由于用户输入可能存在恶意（见下文），我们必须手动清理用户输入，以避免 SQL 注入攻击。

你好 Bobby Tables

如果你没有正确清理输入，你将面临严重风险。SQL 注入可能是最流行的网络黑客技术。如果你忘记清理像 "id" querystring 这样的内容，你可能会打开巨大的漏洞，并可能暴露你所有的数据库，不仅允许读取，还允许修改。

参数化 SQL

即使你是老派开发者（喂，VB6 的朋友，我又在看你了），你可能也知道像这样动态构建 SQL 不仅容易出错（容易受到 SQL 注入攻击），而且无法受益于 SQL Server 缓存执行计划。你可能使用过某种代码生成工具来帮助你构建这样的代码（也许是为了调用存储过程，因为在 2000 年代初，3 层架构和 "Windows DNA" 非常流行）。

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
    & " NEEDS_ID     = @NEEDS_ID, " & vbNewLine _
    & " OBJ_ID       = @OBJ_ID, " & vbNewLine _
    & " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
    & " TIME21_ID    = @TIME21_ID, " & vbNewLine _
    & " WHERE ID     = @WHEREID"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter
                ("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 456)
    .Execute
End With 

我希望千禧一代的开发者不会对参数化 SQL 在上个世纪就已经存在感到过于惊讶。

回到现代软件

时光飞逝，Java 和 .NET 出现（然后可能稍微沉寂了一点？），反射、字节码发射、全功能 ORM、微型 ORM，3 层架构被许多现代架构取代，现在数据库访问要容易得多，对吧？

现在我们不必手动为 SQL 描述每一个参数了。我们喜欢的 ORM 会为我们完成这项工作。

Dapper 示例

var dynamicParams = new DynamicParameters();

string sql = "SELECT * FROM [Product] WHERE 1=1";

if (productName != null)
{
    sql += " AND Name LIKE @productName"; 
    dynamicParams.Add("productName", productName);
}

if (subCategoryId != null)
{
    sql += " AND ProductSubcategoryID = @subCategoryId"; 
    dynamicParams.Add("subCategoryId", subCategoryId);
}

var products = cn.Query<Product>(sql, dynamicParams);

不要重复自己

下面的示例让我有些沮丧，原因有两个：

• 我必须将 productName 传递两次，一次是给 sql 字符串，一次是给 dynamicParams 字典。
  它们的名称必须匹配。
• 我必须将这两个变量（SQL 语句和参数列表）分开，尽管它们彼此之间密切相关。

字符串插值内部机制

字符串插值 于 2016 年在 C# 中引入。

而不是写这样的代码：

string name = "Rick";
int accesses = 10;
string output = string.Format("{0}, you've been here {1:n0} times.", name, accesses);

你可以这样做：

string name = "Rick";
int accesses = 10;
string output = $"{name}, you've been here {accesses:n0} times.";

内部原理是，当你写一个插值字符串（以 $ 开头）时，编译器会生成一个 FormattableString 类，它包含模板（就像 "{0}, 您已在此处访问了 {1:n0} 次。"），以及参数列表（string name 和 int accesses）。

如果你的方法期望的是一个普通字符串，FormattableString 会被隐式转换为普通字符串，你将获得与直接将 string.format 传递给方法相同的行为。但是，如果你的方法期望的是一个 FormattableString 类，那么你就可以访问格式和参数，它们是彼此隔离的。

FormattableString 可以很有用，例如，如果我们想构建参数化 SQL 语句，同时允许用户像编写普通字符串连接一样构建他们的 string。

QueryBuilder ParseSQL(FormattableString query)
{
    QueryBuilder myWrapper = new QueryBuilder();

    string dapperSql = query.Format;
    // replace {0} by "@p0", 
    // replace {1} by "@p1", etc..
    myWrapper.SQL = dapperSql;

    var dapperArgs = new Dapper.DynamicParameters();
    var args = query.GetArguments();
    // dapperArgs.Add("p0", args[0]);
    // dapperArgs.Add("p1", args[1]); ...
    myWrapper.Arguments = dapperArgs;

    return myWrapper;
    // now anyone can use Dapper like  
    // var pocos = connection.Query<POCO>(myWrapper.SQL, myWrapper.Parameters);
}

DapperQueryBuilder

基于上述想法，我创建了 DapperQueryBuilder - 它是 Dapper 的一个简单封装，允许我们通过字符串插值传递 SQL 参数。

你可以这样写代码：

var query = cn.QueryBuilder($"SELECT * FROM [Product] WHERE 1=1");

if (productName != null)
    query.Append($"AND Name LIKE {productName}"); 

if (subCategoryId != null)
    query.Append($"AND ProductSubcategoryID = {subCategoryId}"); 

var products = query.Query<Product>(); 

虽然看起来你只是在构建一个动态 SQL（带有内联字面量值），但实际上你得到的是参数化 SQL。

在这种情况下，query 将具有以下底层语句：

SELECT * FROM [Product] WHERE 1=1 AND Name LIKE @p0 AND ProductSubcategoryId = @p1

并且还将包含参数：

@p0 = productName
@p1 = subCategoryId

总结一下，与使用 Dapper 扩展 .Query<T>（它扩展了 IDbConnection 并接受 SQL 字符串和参数列表）不同，你可以使用 QueryBuilder() 扩展，它会创建一个 QueryBuilder，你可以在其中动态地（在一个语句中）添加新参数和关联的 SQL 子句。

快速入门

如果你喜欢它并想立即开始使用它：

1. 安装 NuGet 包 Dapper-QueryBuilder
2. 像这样开始使用：

   using DapperQueryBuilder;
   // ...
   
   cn = new SqlConnection(connectionString);
   
   // If you have all your parameters at once and 
   // just want to benefit from string interpolation:
   var products = cn.QueryBuilder($@"
       SELECT ProductId, Name, ListPrice, Weight
       FROM [Product]
       WHERE [ListPrice] <= {maxPrice}
       AND [Weight] <= {maxWeight}
       AND [Name] LIKE {search}
       ORDER BY ProductId").Query<Product>();

   或者像这样构建动态条件：

   using DapperQueryBuilder;
   // ...
   
   cn = new SqlConnection(connectionString);
   
   // If you want to dynamically add conditions
   var q = cn.QueryBuilder($@"
       SELECT ProductId, Name, ListPrice, Weight
       FROM [Product]
       WHERE 1=1 ");
   
   q.AppendLine("AND [ListPrice] <= {maxPrice}");
   q.AppendLine("AND [Weight] <= {maxWeight}");
   q.AppendLine("AND [Name] LIKE {search}");
   q.AppendLine("ORDER BY ProductId");
   
   var products = q.Query<Product>();

过滤器列表

有些人错误地认为 "WHERE 1=1" 会降低性能。实际上不会。所以使用这个占位符条件是一个很好的技巧，可以让你像上面示例那样简单地追加其他条件（如果有）。

另一种选择是，你可以构建整个查询，但将过滤器留待以后动态定义（和渲染）。DapperQueryBuilder 有一个特殊的命令 Where()，用于在内部保存过滤器，之后再进行替换。

int maxPrice = 1000;
int maxWeight = 15;
string search = "%Mountain%";

var cn = new SqlConnection(connectionString);

// You can build the query manually and 
// just use QueryBuilder to replace "where" filters (if any)
var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
    FROM [Product]
    /**where**/
    ORDER BY ProductId
    ");
    
// You just pass the parameters as if it was an interpolated string, 
// and QueryBuilder will automatically convert them to Dapper parameters (injection-safe)
q.Where($"[ListPrice] <= {maxPrice}");
q.Where($"[Weight] <= {maxWeight}");
q.Where($"[Name] LIKE {search}");

// Query() will automatically build your query and replace 
// your /**where**/ (if any filter was added)
var products = q.Query<Product>();

你也可以这样创建 OR 过滤器：

q.Where(new Filters(Filters.FiltersType.OR) 
{
    new Filter($"[Weight] <= {maxWeight}"), 
    new Filter($"[Name] LIKE {search}") 
});

或者你可以像这样混合使用 OR/AND：

q.Where(new Filters(Filters.FiltersType.OR)
{
    new Filters(Filters.FiltersType.AND)
    {
        $"[Weight] <= {maxWeight}",
        $"[Weight] >= {minWeight}",
    }
    new Filter($"[Name] LIKE {search}")
});

数组也可以用作插值字符串：

var categories = new string[] { "Components", "Clothing", "Acessories" };
q.Append($"WHERE c.[Name] IN {categories}");

流畅 API（链式方法）

对于喜欢方法链式指导（或者允许最终用户构建自己的查询）的人来说，有一个流畅 API，可以让你一步一步地构建查询，模拟动态 SQL 语句的拼接。

所以，基本上，不是从一个完整的查询开始然后只追加新的过滤器（.Where()），而是 QueryBuilder 会为你构建整个查询：

var q = cn.QueryBuilder()
    .Select($"ProductId")
    .Select($"Name")
    .Select($"ListPrice")
    .Select($"Weight")
    .From($"[Product]")
    .Where($"[ListPrice] <= {maxPrice}")
    .Where($"[Weight] <= {maxWeight}")
    .Where($"[Name] LIKE {search}")
    .OrderBy($"ProductId");
    
var products = q.Query<Product>();

你将得到这个查询：

SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE [ListPrice] <= @p0 AND [Weight] <= @p1 AND [Name] LIKE @p2
ORDER BY ProductId

希望你喜欢这篇博文，就像我写这个库时一样喜欢它！

你可以在 这里 找到完整的源代码。如果你喜欢它，请在 GitHub 上给它一个 star 并 fork 它。

历史

• 2020 年 8 月 6 日：第一个版本

最初的想法来自于这篇博文。