ShadowSql 的主要思想通过表和字段的影子来拼写 sql
.net 中的表达式树是作为模型类和查询逻辑的影子,非常契合 ShadowSql
拿表达式树来拼写 sql 就和 EF 类似
一、nuget 包
nuget 安装 ShadowSql.Expressions
引用命名空间: ShadowSql.Expressions
二、简单用法
表达式查询 1.1 按常量查询
var query = new TableSqlQuery<User>("Users") .Where(u => u.Name == "张三");// SELECT * FROM [Users] WHERE [Name]='张三'
复制代码
1.2 按参数查询
var query = new TableSqlQuery<User>() .Where<UserParameter>((u, p) => u.Age > p.Age2);// SELECT * FROM [User] WHERE [Age]>@Age2
复制代码
表达式排序 2.1 对单个字段排序
var cursor = new Table("Users") .Take<User>(10) .Asc(u => u.Id);// SELECT TOP 10 * FROM [Users] ORDER BY [Id]
复制代码
2.2 对多个字段排序
var cursor = new Table("Users") .Take<User>(10) .Desc(u => new { u.Age, u.Id });// SELECT TOP 10 * FROM [Users] ORDER BY [Age] DESC,[Id] DESC
复制代码
3.1 主外键联表
var query = new Table("Users") .SqlJoin<User, UserRole>(new Table("UserRoles")) .On(u => u.Id, r => r.UserId);// SELECT * FROM [Users] AS t1 INNER JOIN [UserRoles] AS t2 ON t1.[Id]=t2.[UserId]
复制代码
3.2 逻辑表达式联表
var query = new Table("Users") .SqlJoin<User, UserRole>(new Table("UserRoles")) .On((u, r) => u.Id == r.UserId);// SELECT * FROM [Users] AS t1 INNER JOIN [UserRoles] AS t2 ON t1.[Id]=t2.[UserId]
复制代码
4. 插入
4.1 插入常量值
var insert = new Table("Users") .ToInsert(() => new User { Name = "张三", Age = 18 });// INSERT INTO [Users]([Name],[Age])VALUES('张三',18)
复制代码
4.2 插入参数
var insert = new Table("Users") .ToInsert<UserParameter, User>(p => new User { Name = p.Name2, Age = p.Age2 });// INSERT INTO [Users]([Name],[Age])VALUES(@Name2,@Age2)
复制代码
5. 表达式删除
var delete = new TableSqlQuery<Student>("Students") .Where(s => s.Score < 60) .ToDelete();// DELETE FROM [Students] WHERE [Score]<60
复制代码
6. 表达式更新
6.1 常量更新
var update = new Table("Users") .ToUpdate<User>(u => u.Id == 1) .Set(u => new User { Age = 18 });// UPDATE [Users] SET [Age]=18 WHERE [Id]=1
复制代码
6.2 参数化更新
var user = new User { Id =1, Age = 18 };var update = EmptyTable.Use("Users") .ToUpdate<User>(u => u.Id == user.Id) .Set(u => new User { Age = user.Age });// UPDATE [Users] SET [Age]=@Age WHERE [Id]=@Id
复制代码
6.3 原值叠加更新
var update = new Table("Students") .ToUpdate<Student>(u => u.Score < 60 && u.Score > 55) .Set(u => new Student { Score = u.Score + 5 });// UPDATE [Students] SET [Score]=([Score]+5) WHERE [Score]<60 AND [Score]>55
复制代码
7、表达式获取数据
7.1 直接获取全表
var select = new Table("Users") .ToSelect<User>() .Select(u => new { u.Id, u.Name });// SELECT [Id],[Name] FROM [Users]
复制代码
7.2 从表达式获取
var select = new Table("Users") .ToSelect<User>(u => u.Status) .Select(u => u.Id);// SELECT [Id] FROM [Users] WHERE [Status]=1
复制代码
7.3 从表查询获取
var select = new Table("Users") .ToSqlQuery<User>() .Where(u => u.Status) .ToSelect() .Select(u => new { u.Id, u.Name });// SELECT [Id],[Name] FROM [Users] WHERE [Status]=1
复制代码
7.4 分页获取
var select = new Table("Users") .ToSqlQuery<User>() .Where(u => u.Status) .Take(10, 20) .Desc(u => u.Id) .ToSelect();// SELECT * FROM [Users] WHERE [Status]=1 ORDER BY [Id] OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
复制代码
三、参数化 sql
ShadowSql 对参数化查询支持的很好,本人也是非常推荐使用参数化查询
其一、参数化查询可以防 sql 注入
其二、参数化查询 sql 可以复用,有条件的化把参数化查询的 sql 缓存起来,用 Dapper 调用 sql 和参数对象直接执行
文章转载自：xiangji
原文链接：https://www.cnblogs.com/xiangji/p/18901576
体验地址：http://www.jnpfsoft.com/?from=001YH
评论