TinyDb 的查询入口有两种：

  • Find/FindOne/Count/Exists：语义明确，适合简单筛选。

  • Query()：IQueryable<T> 风格，适合链式组合。

本文重点是“业务可落地”的查询写法。

1. 准备实体与数据

using TinyDb.Attributes;
using TinyDb.Bson;

[Entity("employees")]
public partial class Employee
{
    [Id]
    public ObjectId Id { get; set; } = ObjectId.NewObjectId();
    [Index] public string Name { get; set; } = string.Empty;
    [Index] public string Department { get; set; } = string.Empty;
    [Index] public int Age { get; set; }
    public decimal Salary { get; set; }
    public DateTime JoinDate { get; set; }
}
using var db = new TinyDb.Core.TinyDbEngine("query.db");
var employees = db.GetCollection<Employee>();

employees.Insert(new[]
{
    new Employee { Name = "张三", Department = "技术部", Age = 35, Salary = 25000, JoinDate = DateTime.UtcNow.AddYears(-5) },
    new Employee { Name = "李四", Department = "技术部", Age = 28, Salary = 18000, JoinDate = DateTime.UtcNow.AddYears(-3) },
    new Employee { Name = "王五", Department = "市场部", Age = 40, Salary = 30000, JoinDate = DateTime.UtcNow.AddYears(-8) },
    new Employee { Name = "赵六", Department = "市场部", Age = 25, Salary = 12000, JoinDate = DateTime.UtcNow.AddYears(-1) }
});

2. 过滤：Where 与 Find 的等价写法

// 写法 A: Find
var techA = employees.Find(e => e.Department == "技术部").ToList();

// 写法 B: Query().Where
var techB = employees.Query().Where(e => e.Department == "技术部").ToList();

// 多条件
var highSalaryTech = employees.Query()
    .Where(e => e.Department == "技术部" && e.Salary > 20000)
    .ToList();

3. 排序 + 分页

int page = 2;
int pageSize = 10;

var pageData = employees.Query()
    .Where(e => e.Age >= 25)
    .OrderByDescending(e => e.Salary)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToList();

建议：用于排序/过滤的字段尽量建索引，尤其是高频条件字段。

4. 聚合查询

var total = employees.Query().Count();
var exists = employees.Query().Any(e => e.Department == "技术部");

var avgSalary = employees.Query()
    .Where(e => e.Department == "技术部")
    .Average(e => e.Salary);

var maxSalary = employees.Query().Max(e => e.Salary);
var minAge = employees.Query().Min(e => e.Age);

5. 分组统计

var byDept = employees.Query()
    .GroupBy(e => e.Department)
    .Select(g => new
    {
        Department = g.Key,
        Count = g.Count(),
        AvgSalary = g.Average(x => x.Salary)
    })
    .OrderByDescending(x => x.Count)
    .ToList();

foreach (var row in byDept)
{
    Console.WriteLine($"{row.Department}: {row.Count} 人, 平均薪资 {row.AvgSalary}");
}

6. 投影查询

var simple = employees.Query()
    .Where(e => e.Age >= 30)
    .Select(e => new
    {
        e.Name,
        e.Department,
        SalaryLevel = e.Salary >= 25000 ? "A" : "B"
    })
    .ToList();

7. 查询策略建议（实战）

  1. 有明确主键：优先 FindById。

  2. 有高选择性条件且有索引：优先 Query().Where(...)。

  3. 大结果集分页：必须有确定排序字段，避免“翻页抖动”。

  4. 多条件动态拼装：统一在仓储层构建表达式，避免业务层复制粘贴。

8. 一个动态查询封装示例

public sealed record EmployeeSearchInput(
    string? Department,
    int? MinAge,
    int? MaxAge,
    decimal? MinSalary,
    decimal? MaxSalary,
    int Page,
    int PageSize);

public static IReadOnlyList<Employee> SearchEmployees(
    TinyDb.Collections.ITinyCollection<Employee> col,
    EmployeeSearchInput input)
{
    var q = col.Query();

    if (!string.IsNullOrWhiteSpace(input.Department))
        q = q.Where(x => x.Department == input.Department);

    if (input.MinAge.HasValue)
        q = q.Where(x => x.Age >= input.MinAge.Value);

    if (input.MaxAge.HasValue)
        q = q.Where(x => x.Age <= input.MaxAge.Value);

    if (input.MinSalary.HasValue)
        q = q.Where(x => x.Salary >= input.MinSalary.Value);

    if (input.MaxSalary.HasValue)
        q = q.Where(x => x.Salary <= input.MaxSalary.Value);

    return q.OrderByDescending(x => x.JoinDate)
        .Skip((input.Page - 1) * input.PageSize)
        .Take(input.PageSize)
        .ToList();
}

9. 小结

  • TinyDb 的 LINQ 查询足够覆盖中等复杂度业务查询。

  • 对高频接口，先做索引设计，再写查询代码。

  • 分页查询必须加排序，避免结果不稳定。

下一篇进入一致性核心：事务和保存点。