public void DapperExtensionsLambda()
{
//SELECT [Users].[UserId],
// [Users].[LoginName],
// [Users].[Password]
//FROM [Users]
//WHERE [Users].[LoginName] = @LoginName_1
var fromDemo = this.LambdaQuery()
.Select(p => new { p.UserId, p.LoginName, p.Password }) //不支持As
.Where(p => p.LoginName == "很好很强大");
//支持 返回 DataReader、DataSet、DataTable、 泛型集合
fromDemo.ToDataReader();
fromDemo.ToDataSet();
fromDemo.ToDataTable();
IEnumerable <UsersModel> list = fromDemo.ToList <UsersModel>();
var select = new Select <UsersEntity>();
select.AddSelect(p => p.Remark.As("Remark")); //Expression<Func<T, bool>>类型支持 as 语法
fromDemo = this.LambdaQuery().AddSelect(select);
// SELECT [Users].[UserId],
// [Users].[LoginName],
// [Users].[Password],
// [Users].[Status],
// [Users].[CreateTime],
// [Users].[UpdateTime],
// [Users].[Remark]
//FROM [Users]
//WHERE (
// (
// ([Users].[LoginName] LIKE @LoginName_1)
// AND ([Users].[Status] NOT IN (@Status_2, @Status_3, @Status_4))
// )
// AND ([Users].[CreateTime] >= @CreateTime_5)
// )
// AND ([Users].[UpdateTime] IS NOT NULL)
var fromDemo2 = this.LambdaQuery()
.Where(p => p.LoginName.Like("%王老五%") && //like
p.Status.NotIn <string>("1", "2", "3") && //in or not in
p.CreateTime >= Convert.ToDateTime("2016-01-21") && //时间比较
p.UpdateTime.IsNotNull() //is null
);
//SELECT [Users].[UserId],
// [Users].[LoginName],
// [Users].[Password],
// [Users].[Status],
// [Users].[CreateTime],
// [Users].[UpdateTime],
// [Users].[Remark]
//FROM [Users]
//WHERE (
// (
// ([Users].[LoginName] LIKE @LoginName_1)
// AND ([Users].[Status] = @Status_2)
// )
// OR ([Users].[UserId] = @UserId_3)
// )
var where = new Where <UsersEntity>();
where.And(p => p.LoginName.Like("%李二蛋%") && p.Status == 1);
where.Or(p => p.UserId == 1);
var fromDemo3 = this.LambdaQuery().Where(where);
//SELECT [Users].[UserId],
// [Users].[LoginName],
// [Users].[Password],
// [Users].[Status],
// [Users].[CreateTime],
// [Users].[UpdateTime],
// [Users].[Remark]
//FROM [Users]
//ORDER BY
// CreateTime ASC,
// UpdateTime ASC,
// UserId DESC
var fromDemo4 = this.LambdaQuery()
.OrderBy(p => new { p.CreateTime, p.UpdateTime })
.AddOrderByDescending(p => new { p.UserId });
//"SELECT * FROM [Users] INNER JOIN UserInfo ON ([Users].[UserId] = [UserInfo].[UserId]) "
var fromDemo5 = this.LambdaQuery()
.InnerJoin <UserInfoEntity>((u, ui) => u.UserId == ui.UserId);
//SELECT DISTINCT TOP 100 *
//FROM [Users] WITH (NOLOCK) INNER
// JOIN UserInfo WITH (NOLOCK)
// ON ([Users].[UserId] = [UserInfo].[UserId])
var fromDemo6 = this.LambdaQuery()
.InnerJoin <UserInfoEntity>((u, ui) => u.UserId == ui.UserId).
WithNoLock().
Top(100)
.Distinct();
//SELECT TOP(10) [_proj].*
//FROM (
// SELECT ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) AS [_row_number],
// *
// FROM [Users] WITH (NOLOCK) LEFT
// JOIN UserInfo WITH (NOLOCK)
// ON ([Users].[UserId] = [UserInfo].[UserId])
// WHERE ([Users].[UserId] > @UserId_1)
// AND ([UserInfo].[Sex] = @Sex_2)
// ) [_proj]
//WHERE [_proj].[_row_number] >= @_pageStartRow
//ORDER BY
// [_proj].[_row_number]
var fromDemo7 = this.LambdaQuery()
.LeftJoin <UserInfoEntity>((u, ui) => u.UserId == ui.UserId).WithNoLock()
.Where <UserInfoEntity>((u, ui) => u.UserId > 1 && ui.Sex == 1)
// .Page(2, 10);
.ToPageList(2, 10);
//DELETE
//FROM [Users]
//WHERE ([Users].[Status] = @Status_1)
// AND (
//[Users].[CreateTime] > @CreateTime_2
var deleteDemo = this.LambdaDelete()
.Where(p => p.Status == 1 && p.CreateTime > DateTime.Now);
//UPDATE [Users]
//SET [Users].[Remark] = @Remark_3
//WHERE [Users].[Status] = @Status_4
var updateDemo = this.LambdaUpdate()
.Set(p => p.Remark == "Remark")
.Where(p => p.Status == 1);
}