简介
无任何依赖项的轻量级的Orm工具，只负责解析Expression，然后拼接成Sql语句。除去使用生成器生成的多个泛型的扩展类型，代码行数约为5000。使用Expression动态构建类型映射。
安装
dotnet add package MT.LightORM --version 2.1.5-pre
注册和配置
// IServiceCollection
services.AddLightOrm(option => {
option.SetDatabase(DbBaseType, ConnectionString, DbProviderFactory);
})
// 直接使用
var path = Path.GetFullPath("../../../test.db");
ExpSqlFactory.Configuration(option =>
{
option.SetDatabase(DbBaseType.Sqlite, "DataSource=" + path, SQLiteFactory.Instance);
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 `a1`.`ProductId`, `a1`.`ProductName`
FROM `Product` `a1`
WHERE ( `a1`.`ModifyTime` > @Now_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 `a5`.*
FROM `USER` `a5`
INNER JOIN `USER_ROLE` `a6` ON ( `a5`.`USER_ID` = `a6`.`USER_ID` )
INNER JOIN `ROLE` `a2` ON ( `a6`.`ROLE_ID` = `a2`.`ROLE_ID` )
WHERE ( `a5`.`USER_ID` = @Const_0 )
多表查询
Db.Select<Power, RolePower, Role>()
.Distinct()
.Where(w => w.Tb1.PowerId == w.Tb2.PowerId && w.Tb2.RoleId == w.Tb3RoleId)
.ToSql(w => new { w.Tb1 });
SELECT DISTINCT `a0`.*
FROM `POWERS` `a0`, `ROLE_POWER` `a3`, `ROLE` `a2`
WHERE ( ( `a0`.`POWER_ID` = `a3`.`POWER_ID` ) AND ( `a3`.`ROLE_ID` = `a2`.`ROLE_ID` ) )
子查询
Db.Select<User>().Where(u => u.Age > 10).GroupBy(u => new
{
u.UserId
}).AsTempQuery(u => new
{
u.Group.UserId,
Total = u.Count()
})
.Where(t => t.UserId.Contains("admin"))
.ToSql();
SELECT *
FROM (
SELECT `a5`.`USER_ID` AS `UserId`, COUNT(*) AS `Total`
FROM `USER` `a5`
WHERE ( `a5`.`AGE` > 10 )
GROUP BY `a5`.`USER_ID`
) `temp0`
WHERE `temp0`.`UserId` LIKE '%'||@Const_0||'%'
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` `a5`
LEFT JOIN (
SELECT `a1`.`ProductId`, COUNT(*) AS `Total`
FROM `Product` `a1`
GROUP BY `a1`.`ProductId`
) `temp0` ON ( `a5`.`AGE` = `temp0`.`ProductId` )
WHERE ( `temp0`.`Total` > 10 )
With (tempName) AS (...) 查询
var tempU = Db.Select<User>().GroupBy(u => new { u.UserId }).ToSelect(g => new
{
g.Group.UserId,
Total = g.Count()
}).AsTemp("us");
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 sql = Db.Select<Power>().WithTempQuery(tempU, tempR)
.Where(w => w.Tb2.Total > 10 || w.Tb3.UserId.Contains("admin"))
.ToSql();
WITH us AS (
SELECT `a5`.`USER_ID` AS `UserId`, COUNT(*) AS `Total`
FROM `USER` `a5`
GROUP BY `a5`.`USER_ID`
)
,temp AS (
SELECT `a2`.`ROLE_ID` AS `RoleId`, `temp0`.`UserId`
FROM `ROLE` `a2`, `us` `temp0`
WHERE ( `a2`.`ROLE_ID` = `temp0`.`UserId` ) AND `temp0`.`UserId` LIKE @temp_Const_0||'%'
)
SELECT *
FROM `POWERS` `a0`, `us` `temp0`, `temp` `temp1`
WHERE ( ( `temp0`.`Total` > 10 ) OR `temp1`.`UserId` LIKE '%'||@Const_0||'%' )
Include查询
需要配置导航关系
Db.Select<User>()
.Where(u => u.UserRoles.When(r => r.RoleId.StartsWith("ad")))
.ToSql();
SELECT DISTINCT *
FROM `USER` `a5`
LEFT JOIN `USER_ROLE` `a6` ON ( `a5`.`USER_ID` = `a6`.`USER_ID` )
LEFT JOIN `ROLE` `a2` ON ( `a2`.`ROLE_ID` = `a6`.`ROLE_ID` )
WHERE `a2`.`ROLE_ID` LIKE @Const_0||'%'
Union 查询
已有查询Union新的查询
Db.Select<User>().Union(Db.Select<User>())
.Where(u => u.Age > 10)
.ToSql();
SELECT *
FROM (
SELECT *
FROM `USER` `a5`
UNION
SELECT *
FROM `USER` `a5`
) `a5`
WHERE ( `a5`.`AGE` > 10 )
使用IExpressionContext.Union
Db.Union(Db.Select<User>(), Db.Select<User>())
.Where(u => u.Age > 10)
.ToSql();
SELECT *
FROM (
SELECT *
FROM `USER` `a5`
UNION
SELECT *
FROM `USER` `a5`
) `a5`
WHERE ( `a5`.`AGE` > 10 )
更新
实体更新
根据配置的主键更新实体，并且忽略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>,DataTable,DataReader等等