Esempio n. 1
0
        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);
        }