.NET：使用 LinqSharp 简化复杂查询

阅读：2177 添加日期：2021/3/27 23:15:59 原文链接：https://www.toutiao.com/item/6936533488132751872/

LinqSharp 是个开源 LINQ 扩展库，它允许您编写简单代码来生成复杂查询，包括查询扩展和动态查询生成。

LinqSharp.EFCore 是对 EntityFramework 的增强库，提供更多数据注解、数据库函数及自定义储存规则等。

https://github.com/zmjack/LinqSharp

由于内容较多，将分篇介绍公开内容、原理及案例分享：

LinqSharp：简化复杂查询
LinqSharp：动态构建 LINQ 查询
LinqSharp.EFCore：表设计数据注解
LinqSharp.EFCore：字段标准化数据注解
LinqSharp.EFCore：函数映射
LinqSharp.EFCore：列式存储代理
LinqSharp.EFCore：关联计算与审计

本文多数示例，提供在线运行测试（.NET Fiddle）。

LinqSharp 为 LINQ 提供了以下增强（“内存查询”或“SQL生成”）：

默认返回方法MinOrDefault：提供默认返回的 Min 方法；MaxOrDefault：提供默认返回的 Max 方法；AverageOrDefault：提供默认返回的 Average 方法。
查询值最小或最大的记录WhereMin：查询指定字段最小的记录；WhereMax：查询指定字段最大的记录。
数据搜索Search：在指定字段或链接表字段中模糊或精确查询数据；
分页查询SelectPage：查询结果分页或执行分页查询。
序列排序OrderByCase / ThenByCase：按指定字符串序列排序。
构建动态查询XWhere：构建动态查询。

以下方法仅提供于内存查询：

按组元素数量分组GroupByCount：按分组记录元素数量分组。
树结构查询SelectMore：按树结构遍历，选择“所有树节点中满足条件的节点”；SelectUntil：按树结构遍历，直到在每个子路径中找到满足条件的节点，选择该节点；SelectWhile：按树结构遍历，选择“所有子路径中连续满足条件的路径节点”。

示例库 Northwnd

Northwnd 是 SQL Server 早期附带的示例数据库，该数据库描述“公司销售产品网”简单案例场景。包括“雇员（Employees）”“产品订单（Orders）”“供应商（Suppliers）”的关系网络。

本文示例使用的是它的 Sqlite 版本（Code First）：

https://github.com/zmjack/Northwnd

通过 NuGet 安装：

dotnet add package Northwnd
dotnet add package LinqSharp
dotnet add package LinqSharp.EFCore

简单使用：

using (var sqlite = NorthwndContext.UseSqliteResource())
{
    ...
}

输出 SQL

“输出 SQL”是研究“SQL 生成”的基础，使用 LinqSharp.EFCore 中的 ToSql 方法：

（在线示例：ToSql | C# Online Compiler）

using (var sqlite = NorthwndContext.UseSqliteResource())
{
    var query = sqlite.Regions
        .Where(x => x.RegionDescription == "Northern");
    var sql = query.ToSql();
}

生成 SQL：

SELECT "r"."RegionID", "r"."RegionDescription"
FROM "Regions" AS "r"
WHERE "r"."RegionDescription" = 'Northern';

注1：由于不同版本的 EntityFrameworkCore 的 SQL 生成器设计不同，因此，生成 SQL 可能会存在差异。（EntityFrameworkCore 5.0 公开了 ToQueryString 来支持这项功能）。

注2：LinqSharp.EFCore 最新版本不兼容所有 EntityFrameworkCore，需使用“大版本号”与 EntityFrameworkCore 一致的发行库（例如，2.2.x，3.0.x，3.1.x）。

默认返回方法扩展

MinOrDefault：原函数 Min 的不抛异常版本，异常返回默认值；
MaxOrDefault：原函数 Max 的不抛异常版本，异常返回默认值；
AverageOrDefault：原函数 Average 的不抛异常版本，异常返回默认值。

（在线示例：MinOrDefault | C# Online Compiler）

// throw 'Sequence contains no elements'new int[0].Min();

new int[0].MinOrDefault();      // 0new int[0].MinOrDefault(-1);    // -1

查询值最小或最大的记录

WhereMin：查询指定字段最小的记录；
WhereMax：查询指定字段最大的记录。

WhereMin 和 WhereMax 会进行两次查询：

查询指定字段的“最小值”或“最大值”；
查询指定字段“最小值”或“最大值”的记录。

例如，查询员工（Empolyees）表中年龄最小的员工：

（在线示例：WhereMax | C# Online Compiler）

var query = sqlite.Employees
    .WhereMax(x => x.BirthDate);
var result = query.Select(x => new
{
    x.EmployeeID,
    x.FirstName,
    x.BirthDate,
}).ToArray();

生成 SQL：

/* Step 1 */
SELECT MIN("e"."BirthDate")
FROM "Employees" AS "e";

/* Step 2 */
SELECT *
FROM "Employees" AS "e"
WHERE "e"."BirthDate" = '1966-01-27 00:00:00';

运行结果：

数据搜索

Search：返回“从指定字段或外键表字段中进行模糊或精确查询”的查询结果。

Search 函数提供了四种搜索模式（SearchOption）：

Contains（默认）：任何指定字段中“包含”搜索字符串；
NotContains：所有指定字段中都“不包含”搜索字符串；
Equals：搜索字符串与某指定字段“相等”；
NotEquals：搜索字符串“不在”任何指定字段中。

例如，查询雇员（Employees）表中地址（Address）或城市（City）包含字母 m 的雇员：

（在线示例：Search | C# Online Compiler）

var query = sqlite.Employees
    .Search("m", e => new
    {
        e.Address,
        e.City,
    });
var result = query.Select(x => new
{
    x.EmployeeID,
    x.Address,
    x.City,
}).ToArray();

生成 SQL：

SELECT *
FROM "Employees" AS "e"
WHERE (('m' = '') OR (instr("e"."Address", 'm') > 0)) 
   OR (('m' = '') OR (instr("e"."City", 'm') > 0));

运行结果：

Search 函数同样提供了外链表的查询（主表或从表查询）。

例如，查询供应商（Suppliers）表中供应任何种类豆腐（Tofu）的供应商：

（在线示例：Search (Details) | C# Online Compiler）

var query = sqlite.Suppliers
    .Include(x => x.Products)
    .Search("Tofu", s => new
    {
        ProductNames = s.Products.Select(x => x.ProductName),
    });

var result = query.Select(x => new
{
    x.SupplierID,
    x.CompanyName,
    Products = string.Join(", ", x.Products.Select(p => p.ProductName)),
}).ToArray();

生成 SQL：

SELECT *
FROM "Suppliers" AS "s"
LEFT JOIN "Products" AS "p" ON "s"."SupplierID" = "p"."SupplierID"
WHERE EXISTS (
    SELECT 1
    FROM "Products" AS "p0"
    WHERE ("s"."SupplierID" = "p0"."SupplierID") 
      AND (('Tofu' = '') OR (instr("p0"."ProductName", 'Tofu') > 0)))
ORDER BY "s"."SupplierID", "p"."ProductID";

运行结果：

分页查询

SelectPage：查询结果分页或执行分页查询。（分页参数从第 1 页开始）

例如，查询雇员（Employees）表，按每页 2 条记录分页，选择第 3 页的记录返回：

（在线示例：SelectPage | C# Online Compiler）

var query = sqlite.Employees
    .SelectPage(pageNumber: 3, pageSize: 2);
var result = query.Select(x => new
{
    x.EmployeeID,
    x.Address,
    x.City,
}).ToArray();

生成 SQL：

SELECT *
FROM "Employees" AS "e"
ORDER BY (SELECT 1)
LIMIT 2 OFFSET 4;

运行结果：

序列排序

OrderByCase / ThenByCase：按指定字符串序列排序。

例如，查询地区（Regions）表，将结果按 N / E / W / S 的地区序列排序返回：

（在线示例：OrderByCase | C# Online Compiler）

var query = sqlite.Regions
    .OrderByCase(x => x.RegionDescription, new[]
    {
        "Northern",
        "Eastern",
        "Western",
        "Southern",
    });
var result = query.Select(x => new
{
    x.RegionID,
    x.RegionDescription,
});

执行 SQL：

SELECT *
FROM "Regions" AS "r"
ORDER BY CASE
    WHEN "r"."RegionDescription" = 'Northern' THEN 0
    ELSE CASE
        WHEN "r"."RegionDescription" = 'Eastern' THEN 1
        ELSE CASE
            WHEN "r"."RegionDescription" = 'Western' THEN 2
            ELSE CASE
                WHEN "r"."RegionDescription" = 'Southern' THEN 3
                ELSE 4
            END
        END
    END
END;

运行结果：

按组元素数量分组

数量分组函数 GroupByCount 用于根据指定每组记录数量（每组最多允许 n 条记录）进行特殊分组。

例如，将如下指定字符串按每行 16 个字符分成多行：

var s = "0123456789ABCDEF0123456789ABCDEF"
    .GroupByCount(16)
    .Select(g => new string(g.ToArray()));

0123456789ABCDEF
0123456789ABCDEF

树结构查询

SelectMore：按树结构遍历，选择“树节点中所有满足条件的节点”；
SelectUntil：按树结构遍历，直到在每个子路径中找到满足条件的节点，选择该节点；
SelectWhile：按树结构遍历，选择“所有子路径中连续满足条件的路径节点”。

例如，雇员（Employees）表按照 EmployeeID 和 ReportsTo 定义结构如下：

SelectMore

按树结构遍历，选择“树节点中所有满足条件的节点”。

例如，查询由 2 号雇员 Andrew 领导的所有成员（2, 1, 3, 4, 5, 6, 7, 9, 8）：

方法：使用 SelectMore 从根节点查找即可。

（在线示例：SelectMore | C# Online Compiler）

var employees = sqlite.Employees
    .Include(x => x.Superordinate)
    .Include(x => x.Subordinates)
    .ToArray();
var query = employees
    .Where(x => x.EmployeeID == 2)
    .SelectMore(x => x.Subordinates);

var result = query.Select(x => new
{
    x.EmployeeID,
    x.FirstName,
    x.ReportsTo,
    ReportsTo_ = x.Superordinate?.FirstName,
});

运行结果：

SelectUntil

按树结构遍历，直到在每个子路径中找到满足条件的节点，选择该节点。

例如，查询由 2 号雇员 Andrew 领导的所有基层员工（叶节点，1, 3, 6, 7, 9, 8）：

方法：使用 SelectUntil 从根节点查找，直到节点 Subordinates 为空。

（在线示例：SelectUntil | C# Online Compiler）

var employees = sqlite.Employees
    .Include(x => x.Superordinate)
    .Include(x => x.Subordinates)
    .ToArray();
var query = employees
    .Where(x => x.EmployeeID == 2)
    .SelectUntil(x => x.Subordinates, x => !x.Subordinates.Any());

var result = query.Select(x => new 
{
    x.EmployeeID,
    x.FirstName,
    x.ReportsTo,
    ReportsTo_ = x.Superordinate?.FirstName,
});

运行结果：

SelectWhile

按树结构遍历，选择“所有子路径中连续满足条件的路径节点”。

例如，查询由 2 号雇员 Andrew 领导的所有非基层员工（非叶节点，2, 5）：

方法：使用 SelectWhile 从根节点查找路径，直到节点 Subordinates 为空。

（在线示例：SelectWhile | C# Online Compiler）

var employees = sqlite.Employees
    .Include(x => x.Superordinate)
    .Include(x => x.Subordinates)
    .ToArray();
var query = employees
    .Where(x => x.EmployeeID == 2)
    .SelectWhile(x => x.Subordinates, x => x.Subordinates.Any());

var result = query.Select(x => new 
{
    x.EmployeeID,
    x.FirstName,
    Subordinates = string.Join(", ", x.Subordinates
        .SelectMore(s => s.Subordinates)
        .Select(s => s.FirstName)),
});

运行结果：

原文：
https://mp.weixinbridge.com/mp/wapredirect?url=
https%3A%2F%2Fgithub.com%2Fzmjack%2FLinqSharp%2Fblob%2Fmaster%2FREADME-CN.md