Skip to content

MarvelTiter/LightORM

BranchesTags

Repository files navigation

简介

无任何依赖项的轻量级的Orm工具，只负责解析Expression，然后拼接成Sql语句。使用Expression动态构建类型映射。

主体

dotnet add package MT.LightORM --version *

Provider ( Sqlite | MySql | Oracle | SqlServer | PostgreSQL)

dotnet add package LightORM.Providers.Sqlite --version *

注册和配置

// IServiceCollection
// 注入IExpressionContext对象使用
services.AddLightOrm(option => {
    option.UseSqlite("DataSource=" + path);
})

// 直接使用
var path = Path.GetFullPath("../../../test.db");
ExpSqlFactory.Configuration(option =>
{
    option.UseSqlite("DataSource=" + path);
    option.SetTableContext(new TestTableContext());
    option.SetWatcher(aop =>
    {
        aop.DbLog = (sql, p) =>
        {
            Console.WriteLine(sql);
        };
    });//.InitializedContext<TestInitContext>();
});
IExpressionContext Db = ExpSqlFactory.GetContext();

使用生成器(可选)

用途

用于收集实体类型信息，以及创建读写值的方法

使用

// 创建一个partial class, 例如 TestTableContext
// 标注`LightORMTableContext`Attribute
[LightORMTableContext]
public partial class TestTableContext
{

}
// 在配置的时候应用
option.SetTableContext(new TestTableContext());

查询(示例代码中使用的Db均为IExpressionContext对象)

基础查询

Db.Select<Product>()
    .Where(p => p.ModifyTime > DateTime.Now)
    .ToSql(p => new { p.ProductId, p.ProductName });
SELECT "a"."PRODUCT_ID" AS "ProductId", "a"."PRODUCT_NAME" AS "ProductName"
FROM "PRODUCTS" "a"
WHERE ("a"."MODIFY_TIME" > @Now_0_0)

Join查询

Db.Select<User>()
    .InnerJoin<UserRole>(w => w.Tb1.UserId == w.Tb2.UserId)
    .InnerJoin<Role>(w => w.Tb2.RoleId == w.Tb3.RoleId)
    .Where(u => u.UserId == "admin")
    .ToSql(w => w.Tb1);
SELECT "a".*
FROM "USER" "a"
INNER JOIN "USER_ROLE" "b" ON ("a"."USER_ID" = "b"."USER_ID")
INNER JOIN "ROLE" "c" ON ("b"."ROLE_ID" = "c"."ROLE_ID")
WHERE ("a"."USER_ID" = 'admin')

多表查询

Db.Select<Permission, RolePermission, Role>()
   .Distinct()
   .Where(w => w.Tb1.PermissionId == w.Tb2.PermissionId && w.Tb2.RoleId == w.Tb3.RoleId)
   .ToSql(w => new { w.Tb1 });
SELECT DISTINCT "a".*
FROM "PERMISSIONS" "a", "ROLE_PERMISSION" "b", "ROLE" "c"
WHERE (("a"."PERMISSION_ID" = "b"."POWER_ID") AND ("b"."ROLE_ID" = "c"."ROLE_ID"))

子查询

Db.Select<User>()
    .Where(u => u.Age > 10)
    .GroupBy(u => u.UserId )
    .AsTable(u => new
    {
        u.Group.UserId,
        Total = u.Count()
    }).AsSubQuery()
    .Where(t => t.UserId.Contains("admin"))
    .ToSql();
SELECT *
FROM (
    SELECT "a"."USER_ID" AS "UserId", COUNT(*) AS "Total"
    FROM "USER" "a"
    WHERE ("a"."AGE" > 10)
    GROUP BY "a"."USER_ID"
) "a"
WHERE "a"."UserId" LIKE '%' || 'admin'|| '%'

Join 子查询

Db.Select<User>()
    .LeftJoin(Db.Select<Product>().GroupBy(p => new { p.ProductId })ToSelect(g => new
    {
        g.Group.ProductId,
        Total = g.Count()
    }), (u, j) => u.Age == j.ProductId)
    .Where(w => w.Tb2.Total > 10)
    .ToSql();
SELECT *
FROM "USER" "a"
LEFT JOIN (
    SELECT "a"."PRODUCT_ID" AS "ProductId", COUNT(*) AS "Total"
    FROM "PRODUCTS" "a"
    GROUP BY "a"."PRODUCT_ID"
) "b" ON ("a"."AGE" = "b"."ProductId")
WHERE ("b"."Total" > 10)

With (tempName) AS (...) 查询

var tempU = Db.Select<User>().GroupBy(u => new { u.UserId }).AsTemp("us", g => new
    {
        g.Group.UserId,
        Total = g.Count()
    });
var tempR = Db.Select<Role>().WithTempQuery(tempU)
    .Where((r, u) => r.RoleId == u.UserId)
    .Where(w => w.Tb2.UserId.StartsWith("ad"))
    .AsTemp("temp", w => new
    {
        w.Tb1.RoleId,
        w.Tb2.UserId,
    });

var tempT = Db.FromTemp(tempR).Where(a => a.RoleId.StartsWith("h")).AsTemp("hh");

var sql = Db.Select<Permission>().WithTempQuery(tempT)
    .Where(w => w.Tb2.UserId.Contains("admin"))
    .ToSql();
WITH us AS (
    SELECT "a"."USER_ID" AS "UserId", COUNT(*) AS "Total"
    FROM "USER" "a"
    GROUP BY "a"."USER_ID"
)
, temp AS (
    SELECT "a"."ROLE_ID" AS "RoleId", "b"."UserId"
    FROM "ROLE" "a", us "b"
    WHERE ("a"."ROLE_ID" = "b"."UserId") AND "b"."UserId" LIKE 'ad'||'%'
)
, hh AS (
    SELECT *
    FROM temp "a"
    WHERE "a"."RoleId" LIKE 'h'||'%'
)
SELECT *
FROM "PERMISSIONS" "a", hh "b"
WHERE "b"."UserId" LIKE '%' || 'admin'|| '%'

Include查询

需要配置导航关系

// 写法1，使用自定义扩展方法WhereIf
var sql = Db.Select<User>()
    .Where(u => u.UserRoles.WhereIf(r => r.RoleId.Contains("admin")))
    .ToSql();

// 写法2，使用Any()
var sql = Db.Select<User>()
    .Where(u => u.UserRoles.Where(r => r.RoleId.Contains("admin")).Any())
    .ToSql();
SELECT DISTINCT *
FROM "USER" "a"
INNER JOIN "USER_ROLE" "b" ON ( "a"."USER_ID" = "b"."USER_ID" )
INNER JOIN "ROLE" "c" ON ( "c"."ROLE_ID" = "b"."ROLE_ID" )
WHERE "c"."ROLE_ID" LIKE '%' || 'admin'|| '%'

Union 查询

已有查询Union新的查询

Db.Select<User>()
    .Where(u => u.Age > 10)
    .Union(Db.Select<User>().Where(u => u.Age > 15))
    .ToSql();
SELECT *
FROM "USER" "a"
WHERE ("a"."AGE" > 10)
UNION
SELECT *
FROM "USER" "a"
WHERE ("a"."AGE" > 15)

使用IExpressionContext.Union

Db.Union(Db.Select<User>(), Db.Select<User>())
    .Where(u => u.Age > 10)
    .ToSql();
SELECT *
FROM (
    SELECT *
    FROM "USER" "a"
    UNION
    SELECT *
    FROM "USER" "a"
) "a"
WHERE ("a"."AGE" > 10)

数据库函数

聚合函数(使用SqlFn的静态方法调用，或者GroupBy后的方法调用中的参数IExpSelectGrouping<TGroup, TTables>中调用)

COUNT(*)

COUNT(T column), 当 column 是(一个二元表达式，并且T的类型是bool) 或者 是一个三元表达式, 会解析成 CASE WHEN 语句

MAX、MIN、AVG、SUM

Join(在分组数据中拼接字符串，不同数据库，调用的函数不同，例如，mysql是group_concat， oracle是listagg等)

开窗函数(窗口函数)

RowNumber、Lag、Rank

支持对导航属性的解析（仅支持1对1的导航属性

Db.Select<User>()
    .ToSql(u => new
    {
        RowNo = WinFn.RowNumber().PartitionBy(u.City.Name).OrderBy(u.Id).Value(),
        u.UserId,
        u.UserName,
        u.Password
    });
SELECT ROW_NUMBER() OVER( PARTITION BY "b"."Name" ORDER BY "a"."ID" ASC ) AS "RowNo", "a"."USER_ID" AS"UserId", "a"."USER_NAME" AS "UserName", "a"."PASSWORD" AS "Password"
FROM "USER" "a"
INNER JOIN "City" "b" ON ( "a"."ID" = "b"."Id" )

更新

实体更新

根据配置的主键更新实体，并且忽略null值

Db.Update(p).ToSql();
UPDATE `Product` SET
`CategoryId` = @CategoryId,
`ProductCode` = @ProductCode,
`ProductName` = @ProductName,
`DeleteMark` = @DeleteMark,
`CreateTime` = @CreateTime,
`Last` = @Last
WHERE `ProductId` = @ProductId

指定列更新

Db.Update<Product>()
    .UpdateColumns(() => new { p.ProductName, p.CategoryId })
    .Where(p => p.ProductId > 10)
    .ToSql()
UPDATE `Product` SET
`CategoryId` = @CategoryId,
`ProductName` = @ProductName
WHERE ( `ProductId` > 10 )

忽略列更新

Db.Update(p)
    .IgnoreColumns(p => new { p.ProductName, p.CategoryId })
    .ToSql();
UPDATE `Product` SET
`ProductCode` = @ProductCode,
`DeleteMark` = @DeleteMark,
`CreateTime` = @CreateTime,
`Last` = @Last
WHERE `ProductId` = @ProductId

批量更新

插入

实体插入

Db.Insert(p).ToSql();
INSERT INTO `Product` 
(`ProductId`, `CategoryId`, `ProductCode`, `ProductName`, `DeleteMark`, `CreateTime`, `Last`) 
VALUES 
(@ProductId, @CategoryId, @ProductCode, @ProductName, @DeleteMark, @CreateTime, @Last)

批量插入

删除

Ado对象

直接执行sql语句, 可返回IEnumerable<T>,IAsyncEnumerable<T>,DataTable,DataReader等等

事务

使用IExpressionContext.CreateScoped创建IScopedExpressionContext或者ISingleScopedExpressionContext对象，提供了BeginTransaction、CommitTransaction、RollbackTransaction以及对应的异步版本，其中IScopedExpressionContext支持切换数据库，CommitTransaction+RollbackTransaction的次数必须等于BeginTransaction的次数

using var scoped = Db.CreateScoped("MainDb");
var role = new Role()
{
    RoleId = "TT001",
    RoleName = "测试",
    Powers = pp.Select(p => new Power() { PowerId = p })
};
try
{
    await scoped.BeginTransactionAsync();
    var n = await scoped.Update(role)
        .Where(r => r.RoleId == role.RoleId)
        .ExecuteAsync();
    var roleId = role.RoleId;
    string[] powers = [.. role.Powers.Select(p => p.PowerId).Distinct()];
    var d = await scoped.Delete<RolePower>().Where(r => r.RoleId == roleId).ExecuteAsync();
    var i = 0;
    
    var ef = await scoped.Insert<RolePower>(powers.Select(p => new RolePower()
    {
        RoleId = roleId,
        PowerId = p
    })).ExecuteAsync();
    i += ef;
    if (powers.Length == 3)
    {
        await scoped.CommitTransactionAsync();
    }
    else
    {
        await scoped.RollbackTransactionAsync();
    }
}
catch
{
    await scoped.RollbackTransactionAsync();
}
using var scoped = Db.CreateScoped();
await scoped.BeginTransactionAsync("v");
await scoped.Select<UserRole>().ToListAsync();
await scoped.SwitchDatabase("v").Select<UserRole>().ToListAsync();
await scoped.CommitTransactionAsync("v");

仓储模式和Linq

接口ILightOrmRepository<User>

提供对单个表的增删改查的直接操作

Linq支持

var usrRepo = Services.GetRequiredService<ILightOrmRepository<User>>();
var proRepo = Services.GetRequiredService<ILightOrmRepository<Product>>();
var list = from u in usrRepo.Table
           join p in proRepo.Table on u.UserId equals p.ProductCode
           group u by u.Age into g
           select new { g.Key, Count = g.Average(u => u.Age) };

复杂查询示例(Oracle)

示例数据库表

class Jobs
{
    public string? Plate { get; set; }
    public string? StnId { get; set; }
}

C#代码

// 从Jobs表中，选择Plate的第一个字符作为Fzjg字段，选择Fzjg和StnId，作为temp表，并命名为info
// With info as (...)
var info = Db.Select<Jobs>().AsTemp("info", j => new
{
    Fzjg = j.Plate!.Substring(1, 2),
    j.StnId
});
// 从info表中，按StnId和Fzjg分组并且按Count(*)排序后，选择StnId，Fzjg，Count(*)，RowNumer，作为temp表，并命名stn_fzjg，表数据为每个StnId中，按Fzjg数据量进行排序并标记为Index
var stnFzjg = Db.FromTemp(info)
    .GroupBy(a => new { a.StnId, a.Fzjg })
    .OrderByDesc(a => new { a.Group.StnId, i = a.Count() })
    .AsTemp("stn_fzjg", g => new
    {
        g.Group.StnId,
        g.Group.Fzjg,
        Count = g.Count(),
        Index = WinFn.RowNumber().PartitionBy(g.Tables.StnId).OrderByDesc(g.Count()).Value()
    });
// 从info表中，按Fzjg分组并且按Count(*)排序后，选择StnId，Fzjg，Count(*)，RowNumer，作为temp表，并命名all_fzjg，表数据为所有Fzjg中，按每个Fzjg的数据量进行排序并标记为Index
var allFzjg = Db.FromTemp(info).GroupBy(a => new { a.Fzjg })
    .OrderByDesc(a => a.Count())
    .AsTemp("all_fzjg", g => new
    {
        StnId = "合计",
        g.Group.Fzjg,
        Count = g.Count(),
        Index = WinFn.RowNumber().OrderByDesc(g.Count()).Value()
    });
// 从info表中，按StnId进行Group By Rollup ，选择StnId和分组数据量，作为temp表，并命名为all_station
var allStation = Db.FromTemp(info).GroupBy(t => new { t.StnId })
    .Rollup()
    .AsTemp("all_station", g => new
    {
        StnId = SqlFn.NullThen(g.Group.StnId, "合计"),
        Total = SqlFn.Count()
    });
/*
 * 1. 从stn_fzjg中，筛选出所有前3的Fzjg数量，然后按StnId分组，选择StnId，组内第一Fzjg作为FirstFzjg，组内第一Count作为FirstCount
 * 2. 从all_fzjg中，筛选出所有前3的Fzjg数量，选择'合计'作为StnId，第一Fzjg作为FirstFzjg，第一的Count作FirstCount
 * 3. 将1和2的结果Union ALl
 * 4. 转为子查询，inner join all_station
 * 5. select结果列
 */
var sql = Db.FromTemp(stnFzjg).Where(t => t.Index < 4)
    .GroupBy(t => new { t.StnId })
    .AsTable(g => new
    {
        StnId = g.Group.StnId!,
        FirstFzjg = g.Join(g.Tables.Index == 1 ? g.Tables.Fzjg.ToString() : "").Separator("").OrderB(g.Tables.StnId).Value(),
        FirstCount = g.Join(g.Tables.Index == 1 ? g.Tables.Count.ToString() : "").Separator("").OrderB(g.Tables.StnId).Value()
    }).UnionAll(Db.FromTemp(allFzjg).Where(t => t.Index < 4).AsTable(g => new
    {
        StnId = "合计",
        FirstFzjg = SqlFn.Join(g.Index == 1 ? g.Fzjg.ToString() : "").Separator("").OrderB(g.StnId).Value(),
        FirstCount = SqlFn.Join(g.Index == 1 ? g.Count.ToString() : "").Separator("").OrderB(g.StnId).Value()
    })).AsSubQuery()
    .InnerJoin(allStation, (t, a) => t.StnId == a.StnId)
    .ToSql((t, a) => new
    {
        Jczmc = SqlFn.NullThen(t.StnId, "TT"),
        a.Total,
        t
    });
Console.WriteLine(sql);
    WITH info AS (
    SELECT SUBSTR("a"."Plate",1,2) AS "Fzjg", "a"."StnId"
    FROM "Jobs" "a"
)
, stn_fzjg AS (
    SELECT "a"."StnId", "a"."Fzjg", COUNT(*) AS "Count", ROW_NUMBER() OVER( PARTITION BY "a"."StnId" ORDER BY COUNT(*) DESC ) AS "Index"
    FROM info "a"
    GROUP BY "a"."StnId", "a"."Fzjg"
    ORDER BY "a"."StnId", COUNT(*) DESC
)
, all_fzjg AS (
    SELECT '合计' AS "StnId", "a"."Fzjg", COUNT(*) AS "Count", ROW_NUMBER() OVER( ORDER BY COUNT(*) DESC ) AS "Index"
    FROM info "a"
    GROUP BY "a"."Fzjg"
    ORDER BY COUNT(*) DESC
)
, all_station AS (
    SELECT NVL("a"."StnId",'合计') AS "StnId", COUNT(*) AS "Total"
    FROM info "a"
    GROUP BY ROLLUP ("a"."StnId")
)
SELECT NVL("a"."StnId",'TT') AS "Jczmc", "b"."Total", "a".*
FROM (
    SELECT "a"."StnId", LISTAGG( CASE WHEN ("a"."Index" = 1) THEN TO_CHAR("a"."Fzjg") ELSE '' END, '') WITHIN GROUP (ORDER BY "a"."StnId" ASC) AS "FirstFzjg", LISTAGG( CASE WHEN ("a"."Index" = 1) THEN TO_CHAR("a"."Count") ELSE '' END, '') WITHIN GROUP (ORDER BY "a"."StnId" ASC) AS "FirstCount"
    FROM stn_fzjg "a"
    WHERE ("a"."Index" < 4)
    GROUP BY "a"."StnId"
    UNION ALL
    SELECT '合计' AS "StnId", LISTAGG( CASE WHEN ("a"."Index" = 1) THEN TO_CHAR("a"."Fzjg") ELSE '' END, '') WITHIN GROUP (ORDER BY "a"."StnId" ASC) AS "FirstFzjg", LISTAGG( CASE WHEN ("a"."Index" = 1) THEN TO_CHAR("a"."Count") ELSE '' END, '') WITHIN GROUP (ORDER BY "a"."StnId" ASC) AS "FirstCount"
    FROM all_fzjg "a"
    WHERE ("a"."Index" < 4)
) "a"
INNER JOIN all_station "b" ON ("a"."StnId" = "b"."StnId")

About

a simple, lightweight, and easy-to-use Object-Relational Mapping/ORM toolkit

Resources

Readme

License

MIT license
Activity

Stars

2 stars

Watchers

2 watching

Forks

0 forks
Report repository

Releases 1

DatabaseUtil Latest
Aug 9, 2025

Packages

No packages published

Languages