public void 字段别名和查询字段中直接拼接sql()
        {
            //1. 查询的实体类 字段别名 为匿名类型成员名
            //2. (Column和Order)中直接拼接sql 用SM.Sql 或者直接写入 字符串值
            //3. (2)的字段别名 也要写在字符串里 注意:这里匿名类型成员名只是为了符合语法,不会被解析成别名
            //4. Order倒序标记方法 SM.OrderDesc()
            string   umodelall = "b.*";
            LockPers lpmodel   = new LockPers()
            {
                IsDel = false
            };
            Users umodel = new Users()
            {
                UserName = "******"
            };

            Expression <Func <LockPers, Users, bool> > where = PredicateBuilder.WhereStart <LockPers, Users>();
            where = where.And((lpw, uw) => lpw.IsDel == lpmodel.IsDel);
            where = where.And((lpw, uw) => uw.UserName == umodel.UserName);

            DapperSqlMaker <LockPers, Users> query = LockDapperUtilsqlite <LockPers, Users>
                                                     .Selec()
                                                     .Column((lp, u) => new { a = "LENGTH(a.Prompt) as len", b = SM.Sql(umodelall), lpid = lp.Id, lp.Name, lp.Prompt })
                                                     .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId)
                                                     .Where(where)
                                                     .Order((lp, w) => new { a = SM.OrderDesc(lp.EditCount), lp.Id });

            var result = query.ExecuteQuery();

            WriteJson(result);                    //  查询结果
            WriteSqlParams(query.RawSqlParams()); //打印sql和参数
        }
        public void Or开头查询条件()
        {
            LockPers lpmodel = new LockPers();

            lpmodel.IsDel = false;
            Users umodel = new Users();

            umodel.UserName = "******";
            string serh = "%%";

            Expression <Func <LockPers, bool> > where = PredicateBuilder.WhereStart <LockPers>();
            //where = where.Or((lpw, uw) => lpw.IsDel == lpmodel.IsDel);
            //where = where.Or((lpw, uw) => uw.UserName == umodel.UserName);
            where = where.And(m => m.IsDel != true);
            where = where.And(m => (m.Name.Contains(serh) || m.Prompt.Contains(serh)));

            DapperSqlMaker <LockPers> query = LockDapperUtilsqlite <LockPers>
                                              .Selec()
                                              .Column()
                                              .From()
                                              .Where(where)
                                              .Order((lp) => new { a = SM.OrderDesc(lp.EditCount), lp.Id });

            var rawsqlparms = query.RawSqlParams();

            WriteSqlParams(rawsqlparms); //打印sql和参数

            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果
        }
        public void 单表分页查询测试MS()
        {
            LockPers lpmodel = new LockPers();

            lpmodel.Name  = "%蛋蛋%";
            lpmodel.IsDel = false;
            Expression <Func <LockPers, bool> > where = PredicateBuilder.WhereStart <LockPers>();
            where = where.And((lpw) => lpw.Name.Contains(lpmodel.Name));
            where = where.And((lpw) => lpw.IsDel == lpmodel.IsDel);

            DapperSqlMaker <LockPers> query = LockDapperUtilmssql <LockPers>
                                              .Selec()
                                              .RowRumberOrderBy(lp => new { lp.EditCount, lp.Name })
                                              .Column()      // lp => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel }) // null查询所有字段
                                              .From()
                                              .Where(where); //lp => lp.Name == lpmodel.Name && lp.IsDel == lpmodel.IsDel  )

            //.Order(lp => new { lp.EditCount, lp.Name }); // .ExecuteQuery();
            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果

            Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();

            Console.WriteLine(resultsqlparams.Item1.ToString()); // sql
            foreach (var name in resultsqlparams.Item2.ParameterNames)
            {
                WriteJson(name + " -- " + resultsqlparams.Item2.Get <object>(name)); // 参数 -- 值
            }

            int page = 1, rows = 3, records;
            var result2 = query.LoadPagems(page, rows, out records);

            WriteJson(result2); //  查询结果
        }
        public void 单表分页查询测试()
        {
            LockPers lpmodel = new LockPers()
            {
                Name = "%蛋蛋%", IsDel = false
            };

            Expression <Func <LockPers, bool> > where = PredicateBuilder.WhereStart <LockPers>();
            where = where.And((lpw) => lpw.Name.Contains(lpmodel.Name));
            where = where.And((lpw) => lpw.IsDel == lpmodel.IsDel);

            DapperSqlMaker <LockPers> query = LockDapperUtilsqlite <LockPers>
                                              .Selec()
                                              .Column()                                    // lp => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel }) // null查询所有字段
                                              .From()
                                              .Where(where)                                //lp => lp.Name == lpmodel.Name && lp.IsDel == lpmodel.IsDel  )
                                              .Order(lp => new { lp.EditCount, lp.Name }); // .ExecuteQuery();

            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果

            Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();

            WriteSqlParams(resultsqlparams);

            int page = 1, rows = 3, records;
            var result2 = query.LoadPagelt(page, rows, out records);

            WriteJson(result2); //  查询结果
        }
        public void  表联表分页测试()
        {
            var arruser = new int[2] {
                1, 2
            };                                 //
            string   uall = "b.*", pn1 = "%蛋蛋%", pn2 = "%m%";
            LockPers lpmodel = new LockPers()
            {
                IsDel = false
            };
            Users umodel = new Users()
            {
                UserName = "******"
            };
            SynNote snmodel = new SynNote()
            {
                Name = "木头"
            };

            Expression <Func <LockPers, Users, SynNote, bool> > where = PredicateBuilder.WhereStart <LockPers, Users, SynNote>();
            where = where.And((l, u, s) => (l.Name.Contains(pn1) || l.Name.Contains(pn2)));
            where = where.And((lpw, uw, sn) => lpw.IsDel == lpmodel.IsDel);
            where = where.And((l, u, s) => u.UserName == umodel.UserName);
            where = where.And((l, u, s) => s.Name == snmodel.Name);
            where = where.And((l, u, s) => SM.In(u.Id, arruser));

            DapperSqlMaker <LockPers, Users, SynNote> query = LockDapperUtilsqlite <LockPers, Users, SynNote>
                                                              .Selec()
                                                              .Column((lp, u, s) => //null)  //查询所有字段
                                                                      new { lp.Name, lpid = lp.Id, x = "LENGTH(a.Prompt) as len", b = SM.Sql(uall), scontent = s.Content, sname = s.Name })
                                                              .FromJoin(JoinType.Left, (lpp, uu, snn) => uu.Id == lpp.UserId
                                                                        , JoinType.Inner, (lpp, uu, snn) => uu.Id == snn.UserId)
                                                              .Where(where)
                                                              .Order((lp, w, sn) => new { lp.EditCount, x = SM.OrderDesc(lp.Name), sn.Content });

            Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();

            WriteSqlParams(resultsqlparams); // 打印sql和参数

            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果

            int page = 2, rows = 3, records;
            var result2 = query.LoadPagelt(page, rows, out records);

            WriteJson(result2); //  查询结果
        }
        public void 四表联表分页测试MS()
        {
            LockPers lpmodel = new LockPers()
            {
                Name = "%蛋蛋%", IsDel = false
            };
            Users umodel = new Users()
            {
                UserName = "******"
            };
            SynNote snmodel = new SynNote()
            {
                Name = "%木头%"
            };

            Expression <Func <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms, bool> > where = PredicateBuilder.WhereStart <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms>();
            where = where.And((lpw, uw, sn, snn) => lpw.Name.Contains(lpmodel.Name));
            where = where.And((lpw, uw, sn, snn) => lpw.IsDel == lpmodel.IsDel);
            where = where.And((lpw, uw, sn, snn) => uw.UserName == umodel.UserName);
            where = where.And((lpw, uw, sn, snn) => sn.Name.Contains(snmodel.Name));
            //  SM.LimitCount,
            DapperSqlMaker <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms> query =
                LockDapperUtilmssql <LockPers_ms, Users_ms, SynNote_ms, SynNote_ms>
                .Selec()
                .RowRumberOrderBy((lp, u, s, sn) => new { lp.Id, a = u.Id })
                .Column((lp, u, s, sn) =>
                        new { SM.LimitCount, lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName, s.Content, s.Name })//null查询所有字段
                .FromJoin(JoinType.Left, (lpp, uu, snn, snnn) => uu.Id == lpp.UserId
                          , JoinType.Inner, (lpp, uu, snn, snnn) => uu.Id == snn.UserId && snn.Id == snn.UserId
                          , JoinType.Inner, (lpp, uu, snn, snnn) => snnn.Id == snn.UserId)
                .Where(where); //(lpp, uu, snn, snnn) => uu.Id == snn.UserId && snnn.Id == snn.UserId)//)

            // .Order((lp, w, sn, snn) => new { lp.EditCount, lp.Name, sn.Content });

            Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();

            WriteSqlParams(resultsqlparams);

            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果

            int page = 2, rows = 2, records;
            var result2 = query.LoadPagems(page, rows, out records);

            WriteJson(result2); //  查询结果
        }
        public void 双表联表分页测试MS()
        {
            LockPers lpmodel = new LockPers()
            {
                Name = "%蛋蛋%", IsDel = false
            };
            Users umodel = new Users()
            {
                UserName = "******"
            };

            Expression <Func <LockPers, Users, bool> > where = PredicateBuilder.WhereStart <LockPers, Users>();
            where = where.And((lpw, uw) => lpw.Name.Contains(lpmodel.Name));
            where = where.And((lpw, uw) => lpw.IsDel == lpmodel.IsDel);
            where = where.And((lpw, uw) => uw.UserName == umodel.UserName);

            DapperSqlMaker <LockPers, Users> query = LockDapperUtilmssql <LockPers, Users>
                                                     .Selec()
                                                     .RowRumberOrderBy((lp, u) => new { lp.EditCount, lp.Name })
                                                     .Column((lp, u) => new { lp.Id, lp.InsertTime, lp.EditCount, lp.IsDel, u.UserName }) //null查询所有字段
                                                     .FromJoin(JoinType.Left, (lpp, uu) => uu.Id == lpp.UserId)
                                                     .Where(where);                                                                       //(lp, u) => lp.Name == lpmodel.Name && lp.IsDel == lpmodel.IsDel || u.UserName == umodel.UserName )

            //.Order((lp, w) => new { lp.EditCount, lp.Name }); // .ExecuteQuery();
            var result = query.ExecuteQuery();

            WriteJson(result); //  查询结果

            Tuple <StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams();

            WriteSqlParams(resultsqlparams);

            int page = 1, rows = 3, records;
            var result2 = query.LoadPagems(page, rows, out records);

            WriteJson(result2); //  查询结果
        }