Languages / 语言
English  ·  简体中文

• Overview
• Why mooSQL?
• Highlights
• When to use it
• Quick start
• Core capabilities
• Supported databases
• Architecture
• Documentation
• Design principles
• Roadmap
• Comparison
• Tech stack
• License
• Contributing

mooSQL is a lightweight ORM for .NET Framework 4.5+, .NET 6, .NET 8, and .NET 10. It is built around database-first thinking and SQL-semantic APIs.

Positioning: above Dapper in convenience, below EF Core in abstraction — keeping Dapper-like performance and flexibility while staying close to how SQL actually reads and runs.

A dialect layer smooths out differences across databases for common CRUD patterns, so you can target multiple engines without rewriting everything.

1. You know SQL — fluent APIs mirror SQL; shallow learning curve
2. You need control — compose SQL fragments without ORM walls
3. You need extension points — extension methods for auth, rules, cross-cutting logic
4. You care about performance — driver-style execution, no heavy LINQ translation tax
5. You need multi-DB — dialects and multi-database / primary–replica setups
6. You have legacy models — entity shapes compatible with EF Core / SqlSugar-style usage
7. You need platform-level control — engines, low-code platforms, and similar stacks

• Multi-database — SQL Server, MySQL, PostgreSQL, Oracle, SQLite, OceanBase, Taos, and more
• Multi-DB by design — switch databases with minimal ceremony; primary / replica friendly
• Three query styles — SQLBuilder (fluent SQL), SQLClip (type-safe), Repository (DDD-friendly)
• Performance — rich ADO-style surface (Dapper-like) with SqlSugar-like ergonomics where it helps
• Type safety — SQLClip reduces magic strings; entity-oriented predicates
• Data authorization — AuthBuilder for fine-grained data scopes
• Unit of work — UnitOfWork for transactions spanning entities and raw SQL
• SQL semantics — method chains read like SQL
• Interoperability — reuse attribute-heavy entities from common .NET ORMs
• Advanced SQL — CTEs (WITH), MERGE, multi-table JOIN projections, virtual SQL columns
• Extensibility — extension methods on the builder for project-specific rules
• Observability — logging, errors, slow-SQL hooks
• MyBatis-like reuse — composable SQL fragments with full C# control flow (no XML cage)

• Projects that want SQL-shaped C# APIs
• Teams migrating from raw ADO.NET or stored procedures
• Services that want high performance without full LINQ translation
• Enterprise apps that must run on several databases
• Complex SQL where you keep full control of the statement
• DDD with Repository + Unit of Work
• Systems that need data-level authorization
• Workflow / platform products that must reach deep into the data layer

dotnet add package mooSQL.Ext

var builder = new DBClientBuilder();
var cache = new MooCache();
var cash = builder
    .useCache(cache)
    .useEnityAnalyser(new SugarEnitiyParser())
    .doBuild();

cash.addConfig(connections);

1. SQLBuilder — fluent, SQL-shaped

var kit = DBCash.useSQL(0);
var dt = kit.select("t.Id, t.Title, t.CreateTime")
    .from("Users t")
    .where("t.Status", 1)
    .whereLike("t.Title", "demo")
    .orderby("t.CreateTime desc")
    .setPage(10, 1)
    .query();

2. SQLClip — typed, fewer magic strings

var clip = DBCash.useClip(0);
var result = clip.from<User>(out var u)
    .join<Department>(out var d)
    .on(() => u.DepartmentId == d.Id)
    .where(() => u.Status == 1)
    .whereIn(() => u.Id, userIds)
    .select(() => new { u.Name, u.Email, d.DepartmentName })
    .queryList();

3. Repository — CRUD-oriented

var repo = DBCash.useRepo<User>(0);
var users = repo.GetList(u => u.Status == 1);
var user = repo.GetFirst(u => u.Id == userId);
repo.Insert(newUser);
repo.Update(user);

SQLBuilder maps closely to SQL (select / from / where, setPage, orderby, groupBy, having, doInsert / doUpdate / doDelete). It is also the integration point for dialects, drivers, caching, interceptors, repositories, and extensions — not “just string concat.”

SQLClip builds SQL from entities and lambdas with compile-time checking.

UnitOfWork ties SQLBuilder-driven commands, repositories, batches, and ad hoc SQL into one explicit transaction when you open a unit of work — mixing entities and hand-written SQL in the same transaction is a first-class scenario.

See the Chinese section below for longer examples (bulk operations, auth integration, logging, and comparison tables) and the doc/ links for step-by-step tutorials (Chinese).

┌─────────────────────────────────────────┐
│              Application                 │
├─────────────────────────────────────────┤
│  Repository  │  UnitOfWork  │  SQLClip   │
├─────────────────────────────────────────┤
│           SQLBuilder (core)              │
├─────────────────────────────────────────┤
│  Expression │ SQL weaving │ Execution    │
├─────────────────────────────────────────┤
│         Dialect abstraction              │
├─────────────────────────────────────────┤
│  SQL Server │ MySQL │ PostgreSQL │ …     │
└─────────────────────────────────────────┘

Main pieces: SQLBuilder, SQLClip, Repository, UnitOfWork, AuthBuilder, expression / LINQ-style helpers.

• Source repository: github.com/sailuosi/mooSQL
• Online documentation (site): sailuosi.github.io/moosql-doc

Tutorials also live under doc/ in this repo (Chinese):

• SQLBuilder 完整教程
• 基础查询
• 新增数据
• 修改数据
• 删除数据
• 多表查询
• 翻页查询
• 子查询
• 复杂 where 条件

• Database first — SQL stays honest and visible
• SQL semantics — APIs read like SQL
• Multi-database — dialects isolate differences
• Multi-DB deployments — switch engines and support replication patterns
• Pragmatic performance — Dapper-like execution paths where it matters
• Interop — common ORM entity patterns carry over
• Stable surface — avoid churn in public APIs
• Ship what real projects need — evolve from production feedback

• More batteries-included helpers and tooling
• Stronger dynamic entity querying
• Sharding navigation and related features
• Migrations — schema bootstrap and seed data
• Read/write split and advanced replication scenarios
• Business-entity versioning
• More built-in dialects (custom dialects remain straightforward)

Why not “everything through LINQ”?
Complex joins, nested SQL, and provider edge cases make pure LINQ a footgun for teams who do not live inside expression trees. mooSQL keeps SQL obvious while still offering typed building blocks (SQLClip) where they help.

• Runtime: .NET Framework 4.5+, .NET 6 / 8 / 10
• Focus: SQL-shaped fluent APIs, parameters, typing, transactions
• Extension: events, custom dialects, expression helpers, virtual columns
• Advanced: CTEs, MERGE, bulk insert paths, multi-table projections

MIT License

Issues and pull requests are welcome.

• 项目介绍
• 为什么选择 mooSQL？
• 核心亮点
• 适用场景
• 快速开始
• 核心功能
• 支持的数据库
• 架构设计
• 文档
• 设计原则
• 未来规划
• 与同类 ORM 的差异
• 技术栈
• 许可证
• 贡献

mooSQL 是一个 .NET 下的轻量级 ORM 库，适用于 .NET Framework 4.5+、.NET 6、.NET 8、.NET 10。核心设计理念是数据库优先和 SQL 语义化。

设计哲学：为喜欢操作 SQL、熟悉 SQL 的开发者提供趁手的工具。

定位：介于 Dapper 与 EF Core 之间 —— 保持 Dapper 的高性能与灵活性，又比 EF Core 更贴近 SQL 的读写方式。

通过方言抽象，可抹平多数据库在基础增删改查上的差异，降低移植与多库部署成本。

核心优势：SQLBuilder 不仅是字符串拼接；在集成方言、驱动、切面、事件、监听器、缓存、注解、仓储、实体查询/修改等能力后，它是可扩展的一体化 SQL 构造与执行入口。

1. 熟悉 SQL — 链式 API 与 SQL 结构一致，上手快
2. 需要灵活度 — 可直接拼 SQL 片段，不被厚重抽象绑死
3. 需要扩展 — 扩展方法承载权限、业务规则等横切逻辑
4. 关注性能 — 驱动层风格接近 Dapper，避免重 LINQ 翻译开销
5. 多数据库 — 方言 + 多库/主从场景友好
6. 遗留实体 — 可与 EF Core、SqlSugar 等实体风格兼容使用
7. 平台级控制 — 流程引擎、低代码平台等需要细粒度操控数据层

• 多数据库原生支持 — SQL Server、MySQL、PostgreSQL、Oracle、SQLite、OceanBase、Taos 等
• 天生多库模式 — 切换数据库成本低，支持主从架构
• 三种查询方式 — SQLBuilder（灵活）、SQLClip（类型安全）、Repository（领域驱动）
• 高性能 — 驱动层提供丰富访问方式（类比 Dapper），兼顾便捷性
• 类型安全 — SQLClip 减少魔法字符串，实体条件更直观
• 数据权限 — 内置 AuthBuilder，支持细粒度数据范围
• 工作单元 — 完整的 UnitOfWork 事务管理
• SQL 语义化 — 链式方法贴近原生 SQL，学习曲线平缓
• 零迁移成本 — 与 EF Core、SqlSugar 等特性实体可兼容
• 高级特性 — WITH、MERGE、多表 JOIN 实体、虚拟 SQL 列
• 强扩展 — 扩展方法集成权限过滤、业务规则等
• 可观测性 — 日志、错误、慢 SQL 等运维友好能力
• 类 MyBatis 体验 — SQL 碎片复用 + 实体映射，同时保留 C# 过程控制能力

• 需要类 SQL 语法的 C# 数据访问
• 遗留系统改造，团队以 SQL 为主
• 高性能简单查询，避免重 LINQ 翻译
• 企业级多数据库应用
• 复杂 SQL，需完全掌控语句
• DDD 项目，需要 Repository + UnitOfWork
• 细粒度数据权限系统
• 从 ADO.NET 平滑过渡
• 流程引擎 / 开发平台等需底层 ORM 可控性的场景
• 需要 SQL 碎片复用（较 MyBatis XML 更灵活）

dotnet add package mooSQL.Ext

包说明（NuGet 上的包名为程序集名；.Core.csproj 为仓库中的项目文件名）

若使用本地 NuGet 源，包目录一般为：C:\Users\用户名\.nuget\packages

var builder = new DBClientBuilder();
var cache = new MooCache();
var cash = builder
    .useCache(cache)
    .useEnityAnalyser(new SugarEnitiyParser())
    .doBuild();

cash.addConfig(connections);

1. SQLBuilder — 灵活强大，SQL 语义化

var kit = DBCash.useSQL(0);
var dt = kit.select("t.Id, t.Title, t.CreateTime")
    .from("Users t")
    .where("t.Status", 1)
    .whereLike("t.Title", "测试")
    .orderby("t.CreateTime desc")
    .setPage(10, 1)
    .query();

增删改示例：

kit.setTable("Users")
   .set("Name", "张三")
   .set("Email", "[email protected]")
   .doInsert();

kit.setTable("Users")
   .set("Email", "[email protected]")
   .where("Id", userId)
   .doUpdate();

kit.setTable("Users")
   .where("Id", userId)
   .doDelete();

2. SQLClip — 类型安全，少魔法字符串

var clip = DBCash.useClip(0);
var result = clip.from<User>(out var u)
    .join<Department>(out var d)
    .on(() => u.DepartmentId == d.Id)
    .where(() => u.Status == 1)
    .whereIn(() => u.Id, userIds)
    .select(() => new { u.Name, u.Email, d.DepartmentName })
    .queryList();

clip.select(() => new { v.ParentOID, v.UCMLClassOID });
clip.where(() => u.CreateTime >= DateTime.Now.AddDays(-7))
    .where(() => u.Status == UserStatus.Active);

3. Repository — 领域驱动

var repo = DBCash.useRepo<User>(0);
var users = repo.GetList(u => u.Status == 1);
var user = repo.GetFirst(u => u.Id == userId);
repo.Insert(newUser);
repo.Update(user);

语义化：select / from / where 对应 SELECT / FROM / WHERE；setPage 生成分页；orderby / groupBy / having；set + doInsert / doUpdate / doDelete。

扩展能力：可在函数/类中插入或改写构建逻辑，用扩展方法集成权限、业务规则等。

支持 SELECT/INSERT/UPDATE/DELETE/MERGE、WITH、子查询、UNION、JOIN、分页、分组、参数化、复杂 WHERE、JOIN 实体与虚拟列等。

var kit = DBCash.useSQL(0);
var result = kit
    .select("u.*, d.Name as DeptName")
    .from("Users u")
    .join("left join Department d on u.DeptId = d.Id")
    .where("u.Status", 1)
    .whereIn("u.Id", userIds)
    .whereExist((sub) => {
        sub.select("1")
          .from("UserRoles ur")
          .where("ur.UserId = u.Id");
    })
    .orderby("u.CreateTime desc")
    .setPage(20, 1)
    .query<User>();

扩展方法示例（权限过滤）：

var kit = DBCash.useSQL(0);
kit.select("a.*")
   .from("Orders a")
   .join("left join Users u on a.UserId = u.Id")
   .where("a.Status", 1)
   .useAuth((auth) => {
       auth.useUserFK("a.UserId")
           .useOrgLike("a.OrgCode")
           .useCustomRule(param);
   })
   .query();

public static class SQLBuilderExtensions
{
    public static SQLBuilder useAuth(this SQLBuilder kit, Action<AuthBuilder> config)
    {
        var auth = new AuthBuilder(kit);
        config(auth);
        return kit;
    }
}

public class AuthBuilder
{
    private readonly SQLBuilder _kit;
    public AuthBuilder(SQLBuilder kit) => _kit = kit;

    public AuthBuilder useUserFK(string userField)
    {
        var userIds = GetAuthorizedUserIds();
        _kit.whereIn(userField, userIds);
        return this;
    }

    public AuthBuilder useOrgLike(string orgField)
    {
        var orgCode = GetAuthorizedOrgCode();
        _kit.whereLikeLeft(orgField, orgCode);
        return this;
    }
}

var clip = DBCash.useClip(0);
var data = clip
    .from<Order>(out var o)
    .join<OrderItem>(out var item)
    .on(() => o.Id == item.OrderId)
    .join<Product>(out var p)
    .on(() => item.ProductId == p.Id)
    .where(() => o.Status == OrderStatus.Paid)
    .where(() => o.CreateTime >= startDate)
    .select(() => new {
        o.OrderNo,
        o.TotalAmount,
        item.Quantity,
        p.ProductName
    })
    .queryList();

public class UserService
{
    private readonly SooRepository<User> _userRepo;

    public UserService()
    {
        _userRepo = DBCash.useRepo<User>(0);
    }

    public List<User> GetActiveUsers()
    {
        return _userRepo.GetList(u => u.Status == UserStatus.Active);
    }

    public PageOutput<User> GetPagedUsers(int page, int pageSize)
    {
        return _userRepo.GetPageList(page, pageSize, (c, u) => {
            c.where(() => u.Status == UserStatus.Active)
             .orderByDesc(() => u.CreateTime);
        });
    }
}

在开启事务后，SQLBuilder 延伸出的仓储、批量、动态修改等与同一事务串联，实体操作与手写 SQL 可共享事务。

var work = DBCash.useWork(0);
try
{
    work.Insert(newUser);
    work.Update(user);
    work.InsertRange(roles);
    work.AddSQL(new SQLCmd("UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1"));
    work.Commit();
}
catch
{
    throw;
}

BulkCopy

var bulk = DBCash.newBulk("Users", 0);
foreach (var user in users)
{
    bulk.newRow()
        .add("Id", user.Id)
        .add("Name", user.Name)
        .add("Email", user.Email)
        .addRow();
}
var count = bulk.doInsert();

BatchSQL

var batch = DBCash.newBatchSQL(0);
foreach (var item in items)
{
    batch.newRow()
        .setTable("Orders")
        .set("Status", OrderStatus.Processed)
        .where("Id", item.Id)
        .addUpdate();
}
var count = batch.exeNonQuery();

var kit = DBCash.useSQL(0);
kit.select("*")
   .from("Orders o")
   .useDuty(userManager, (duty) => {
       duty.useMenu(menuId)
          .useLoginVisitBag(true)
          .useOrgIsField("o.OrgId")
          .useOrgLikeField("o.OrgCode")
          .useUseIsField("o.CreatedBy")
          .onEmpty((duty) => {
              kit.where("1=0");
              return "";
          })
          .doBuild();
   })
   .query();

• SQL 执行日志、错误与异常、慢 SQL、参数化日志、自定义监听器

public class OrderDetailView
{
    public string OrderNo { get; set; }
    public string UserName { get; set; }
    public decimal TotalAmount { get; set; }
    public int ItemCount { get; set; }
}

var kit = DBCash.useSQL(0);
var orders = kit
    .select("o.OrderNo, u.UserName, o.TotalAmount, COUNT(oi.Id) as ItemCount")
    .from("Orders o")
    .join("left join Users u on o.UserId = u.Id")
    .join("left join OrderItems oi on o.Id = oi.OrderId")
    .groupBy("o.OrderNo, u.UserName, o.TotalAmount")
    .query<OrderDetailView>();

┌─────────────────────────────────────────┐
│           业务应用层                      │
├─────────────────────────────────────────┤
│  Repository  │  UnitOfWork  │  SQLClip   │
├─────────────────────────────────────────┤
│            SQLBuilder (核心层)            │
├─────────────────────────────────────────┤
│  表达式层  │  SQL编织层  │  执行层        │
├─────────────────────────────────────────┤
│           数据库方言抽象层                 │
├─────────────────────────────────────────┤
│  SQL Server │ MySQL │ PostgreSQL │ ... │
└─────────────────────────────────────────┘

核心组件：SQLBuilder、SQLClip、Repository、UnitOfWork、AuthBuilder、Expression（LINQ 风格辅助）。

多级别抽象：执行层、SQL 编织层、仓库层、表达式层；方言抹平数据库差异，扩展成本低。

• 源码仓库：github.com/sailuosi/mooSQL

• 在线文档：sailuosi.github.io/moosql-doc

• SQLBuilder 完整教程

• 基础查询

• 新增数据

• 修改数据

• 删除数据

• 多表查询

• 翻页查询

• 子查询

• 复杂 where 条件

• 数据库优先 — 贴近 SQL，保持可控
• SQL 语义化 — 链式 API 读法接近 SQL
• 多数据库兼容 — 方言抽象差异
• 天生多库模式 — 主从与切换友好
• 兼具优势 — Dapper 式性能思路 + 便捷 API
• 零迁移成本 — 常见 ORM 实体习惯可延续
• 向前兼容 — 公共 API 尽量稳定
• 实用为王 — 以真实项目需求驱动演进

• 便捷性生态与开箱即用能力
• 实体动态查询增强
• 分库分表与导航查询
• 数据库迁移与种子数据
• 读写分离与主从写入
• 业务实体版本
• 更多内置数据库支持（自定义方言仍简单）

为何强调 SQL 语义化而非全盘 LINQ？
复杂 Join、子查询与部分 C# 方法到 SQL 的映射存在灰区；不熟悉表达式树的开发者容易把不可翻译逻辑塞进委托。mooSQL 选择 SQL 语义化为主，并用 SQLClip 提供类型安全补充。

• 框架：.NET Framework 4.5+、.NET 6、.NET 8、.NET 10
• 核心：SQL 语义化链式语法、参数化、类型安全、事务
• 扩展：事件、自定义方言、表达式函数、虚拟 SQL 列
• 高级：WITH、MERGE、BulkInsert、多表 JOIN 实体

MIT License

欢迎提交 Issue 与 Pull Request。