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.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)
- 表达式删除
var delete = new TableSqlQuery<Student>("Students")
.Where(s => s.Score < 60)
.ToDelete();
// DELETE FROM [Students] WHERE [Score]<60
- 表达式更新
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
