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

dotNET跨平台 2021-03-06 07:46

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

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

https://github.com/zmjack/LinqSharp

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

  1. LinqSharp:简化复杂查询

  2. LinqSharp:动态构建 LINQ 查询

  3. LinqSharp.EFCore:表设计数据注解

  4. LinqSharp.EFCore:字段标准化数据注解

  5. LinqSharp.EFCore:函数映射

  6. LinqSharp.EFCore:列式存储代理

  7. 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 来支持这项功能)。

注2LinqSharp.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(); // 0
new int[0].MinOrDefault(-1); // -1

查询值最小或最大的记录

  • WhereMin:查询指定字段最小的记录;

  • WhereMax:查询指定字段最大的记录。

WhereMin 和 WhereMax 会进行两次查询:

  1. 查询指定字段的“最小值”或“最大值”;

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

例如,查询员工(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)),
});

运行结果:


下篇文章将介绍如何使用 LinqSharp “动态生成查询”,敬请关注。