Dapper 链式查询扩展 _查询

一万年来谁著史，三千里外欲封侯。这篇文章主要讲述Dapper 链式查询扩展相关的知识，希望能为你提供帮助。
Dapper 链式查询扩展 DapperSqlMaker
Github地址：https://github.com/mumumutou/DapperSqlMaker 欢迎大佬加入
Demo:

查询 TestsDapperSqlMaker\\DapperSqlMaker.Test\\ SelectDapperSqlMakerTest.cs
添加 TestsDapperSqlMaker\\DapperSqlMaker.Test\\ InsertDapperSqlMakerTest.cs
更新 TestsDapperSqlMaker\\DapperSqlMaker.Test\\ UpdateDapperSqlMakerTest.cs
删除 TestsDapperSqlMaker\\DapperSqlMaker.Test\\ DeleteDapperSqlMakerTest.cs
上下文类 TestsDapperSqlMaker\\DbDapperSqlMaker\\ LockDapperUtilsqlite.cs

简单栗子： 1.查询-联表查询,分页

1 public void 三表联表分页测试() 2 { 3var arruser = new int[2] { 1,2 }; // 4string uall = "b.*", pn1 = "%蛋蛋%", pn2 = "%m%"; 5LockPers lpmodel = new LockPers() { IsDel = false}; 6Users umodel = new Users() { UserName = "jiaojiao" }; 7SynNote snmodel = new SynNote() { Name = "木头" }; 8Expression< Func< LockPers, Users, SynNote, bool> > where = PredicateBuilder.WhereStart< LockPers, Users, SynNote> (); 9where = where.And((l, u, s) => ( l.Name.Contains(pn1) || l.Name.Contains(pn2) )); 10where = where.And((lpw, uw, sn) => lpw.IsDel == lpmodel.IsDel); 11where = where.And((l, u, s) => u.UserName == umodel.UserName); 12where = where.And((l, u, s) => s.Name == snmodel.Name ); 13where = where.And((l, u, s) => SM.In(u.Id, arruser)); 14 15DapperSqlMaker< LockPers, Users, SynNote> query = LockDapperUtilsqlite< LockPers, Users, SynNote> 16.Selec() 17.Column((lp, u, s) => //null)//查询所有字段 18new { lp.Name, lpid = lp.Id, x = "LENGTH(a.Prompt) as len", b = SM.Sql(uall)
, scontent = s.Content, sname = s.Name }) 19.FromJoin(JoinType.Left, (lpp, uu, snn) => uu.Id == lpp.UserId 20, JoinType.Inner, (lpp, uu, snn) => uu.Id == snn.UserId) 21.Where(where) 22.Order((lp, w, sn) => new { lp.EditCount, x = SM.OrderDesc(lp.Name), sn.Content }); 23 24var result = query.ExcuteSelect(); 25WriteJson(result); //查询结果 26 27Tuple< StringBuilder, DynamicParameters> resultsqlparams = query.RawSqlParams(); 28WriteSqlParams(resultsqlparams); // 打印sql和参数 29 30int page = 2, rows = 3, records; 31var result2 = query.LoadPagelt(page, rows, out records); 32WriteJson(result2); //查询结果 33 }

生成的sql ：

selecta.Name as Name, a.Id as lpid , LENGTH(a.Prompt) as len, b.* , c.Content as scontent, c.Name as sname from LockPers a left joinUsers b onb.Id = a.UserId inner joinSynNote c onb.Id = c.UserId where(a.Name like @Name0ora.Name like @Name1) anda.IsDel = @IsDel2andb.UserName = @UserName3 andc.Name = @Name4andb.Id in @Id order bya.EditCount, a.Name desc , c.Content

【Dapper 链式查询扩展】
2.更新-更新部分字段

1 [Test] 2 public void 更新部分字段测试lt() 3 { 4var issucs = LockDapperUtilsqlite< LockPers> .Cud.Update( 5s => 6{ 7s.Name = "测试bool修改1"; 8s.Content = "update方法内赋值修改字段"; 9s.IsDel = true; 10}, 11w => w.Name == "测试bool修改1" & & w.IsDel == true 12); 13Console.WriteLine(issucs); 14 }

3.七联表以上待扩展 copy六联表修改3个文件