开源ORM框架 SqlSugar 3.0的巨大变化 - 孙凯旋_H5之家

SqlSugar从1.0版本一直更新到3.0 ，使用该 ORM的人也越来越多，需求也越来越多，提出的需求和大家给我的建议的也都尽力满足。

并且重构了EMIT和拉姆达解析，添加了无数的新功能，重新整了的GIT HUB上的DEMO，重新整理了所有注释。

完全基于SqlSugar的开源CMS 作者已经完成了后台并且将会开源。

SqlSugar 3.0 即将发布现在版本 2.99

优点：

SqlSugar 是一款小巧，并且功能齐全的ORM，并不需要像Dapper一样依赖第三方扩展

SqlSugar 语法易用简单，有漂亮的拉姆达语法，也支持Dapper SQL和ADO.NET的所有功能

SqlSugar 性能达到原生水准，远超 Dapper和EF CORE。

SqlSugar 支持.NET CORE , 多个数据库

SqlSugar 体积小巧只有150K是EF的30分之1 ，NUGET直接可以下载

特色功能

SqlbulkCopy和SqlbulkReplace 这两个函数能够处理海量数据插入和更新，也是众多ORM没有集成的功能之一

SQL日志功能可以方便的对SQL进行监控和写入日志

全局过滤器例如我都有IsDeleted来标识是否删除，这样我每查一个表就要加一个WHERE条件，有了全局过滤器就变成非常方便

T4+实体生成可以自定义实体格式并且可以能过T4模版快速生成实体文件

POCO设计实体类没有任何属性完全原生态

1、使用拉姆达进行查询

针对单表或者视图查询 student = db.Queryable<Student>().ToList(); var studentDynamic = db.Queryable<Student>().ToDynamic(); var studentJson = db.Queryable<Student>().ToJson(); single = db.Queryable<Student>().Single(c => c.id == 1); singleOrDefault = db.Queryable<Student>().SingleOrDefault(c => c.id == 11111111); single2 = db.Queryable<Student>().Where(c => c.id == 1).SingleOrDefault(); first = db.Queryable<Student>().Where(c => c.id == 1).First(); var first2 = db.Queryable<Student>().Where(c => c.id == 1).FirstOrDefault(); page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(10).Take(10).ToList(); page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).ToPageList(2, 10); count = db.Queryable<Student>().Where(c => c.id > 10).Count(); skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(2).ToList(); take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Take(2).ToList(); conval = ; var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList(); //Like conval = ; var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList(); student12 = db.Queryable<Student>().Where(c => == ).Where(, new { id = 1 }).ToList(); == ).Where().ToList(); isAny100 = db.Queryable<Student>().Any(c => c.id == 100); bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1); maxId = db.Queryable<Student>().Max(it => it.id); maxId2 = db.Queryable<Student>().Max<); //字符串写法 minId1 = db.Queryable<Student>().Where(c => c.id > minId2 = db.Queryable<Student>().Where(c => c.id > );//字符串写法 orderList = db.Queryable<Student>().OrderBy().ToList();//字符串支持多个排序 order2List = db.Queryable<Student>().OrderBy(it => it.name).OrderBy(it => it.id, OrderByType.desc).ToList(); // order by name as ,order by id desc intArray = , , };
var listnew = db.Queryable<Student>().Where(it => intArray.Contains(it.name)).ToList(); var intList = intArray.ToList(); var list0 = db.Queryable<Student>().In(it => it.id, 1, 2, 3).ToList(); var list1 = db.Queryable<Student>().In(it => it.id, intArray).ToList(); , intArray).ToList(); var list3 = db.Queryable<Student>().In(it => it.id, intList).ToList(); , intList).ToList(); list7 = db.Queryable<Student>().Where(c => c.id < ).ToDynamic(); ).ToDynamic(); List<StudentGroup> list9 = db.Queryable<Student>().Where(c => c.id < ).ToList(); List<StudentGroup> list10 = db.Queryable<Student>().Where(c => c.id < ).Select<StudentGroup>().ToList(); //SELECT Sex,Count=count(*) FROM Student WHERE 1=1 AND (id < 20) GROUP BY Sex --生成结果 jList = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join .Where<School>((s1, s2) => s1.id == 1) .Select() .ToDynamic(); /*等于同于 SELECT s1.*,s2.name as schName FROM [Student] s1 LEFT JOIN [School] s2 ON s1.sch_id = s2.id WHERE s1.id = 1 jList2 = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join //如果要用inner join这么写 //.JoinTable<School>((s1, s2) => s1.sch_id == s2.id ,JoinType.INNER) .Where<School>((s1, s2) => s1.id > 1) .OrderBy(s1 => s1.name) .Skip(10) .Take(20) .Select() .ToDynamic(); jList3 = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id .JoinTable<School>((s1, s3) => s1.sch_id == s3.id) // left join School s3 on s1.id=s3.id .Where<School>((s1, s2) => s1.id > 1) // where s1.id>1 .Where(s1 => s1.id > 0) .OrderBy<School>((s1, s2) => s1.id) //order by s1.id 多个order可以 .oderBy().orderby 叠加 .Skip(10) .Take(20) .Select()//select目前只支持这种写法 .ToDynamic(); //上面的方式都是与第一张表join，第三张表想与第二张表join写法如下 List<V_Student> jList4 = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) // left join School s2 on s1.id=s2.id .JoinTable<School, Area>((s1, s2, a1) => a1.id == s2.AreaId)// left join Area a1 on a1.id=s2.AreaId 第三张表与第二张表关联 .JoinTable<Area, School>((s1, a1, s3) => a1.id == s3.AreaId)// left join School s3 on a1.id=s3.AreaId 第四第表第三张表关联 .JoinTable<School>((s1, s4) => s1.sch_id == s4.id) // left join School s2 on s1.id=s4.id .Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name }).ToList(); //等同于 //SELECT id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name //FROM [Student] s1 //LEFT JOIN School s2 ON ( s1.sch_id = s2.id ) 第三张表与第二张表关联第四张表与第三张表关联 //LEFT JOIN School s4 ON ( s1.sch_id = s4.id ) //WHERE 1=1 childQuery = db.Queryable<Area>().Where().Select(it => childTableName = , childQuery.Key);queryable = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //LEFT JOIN School s2 ON ( s1.sch_id = s2.id ) .JoinTable(childTableName, , , new { id = 1 }, JoinType.INNER) //INNER JOIN (SELECT * FROM [Area] WHERE 1=1 AND id=@id ) a1 ON a1.id=s2.areaid .OrderBy(s1 => s1.id); var list = queryable.Select<School, Area, V_Student>((s1, s2, a1) => new V_Student { id = s1.id, name = s1.name, SchoolName = s2.name, AreaName = a1.name }) .ToPageList(0, 200); var count2 = list.Count; queryable2 = db.Queryable<Student>().Where(it => true); if (maxId.ObjToInt() == 1) { queryable2.Where(it => it.id == 1); } else { queryable2.Where(it => it.id == 2); } var listJoin = queryable2.ToList(); par = new Queryable<Student>().Where(it => it.id == 1);//声名没有connection对象的Queryable par.DB = db; var listPar = par.ToList(); id = 1; var sqlAndPars = db.Queryable<Student>().Where(it => it.id == id).OrderBy(it => it.id).ToSql(); par1 = ; ; r2 = db.Queryable<InsertTest>().Where(it => it.d1 == par1.ObjToDate()).ToSql(); r4 = db.Queryable<InsertTest>().Where(it => it.id == 2.ObjToDecimal()).ToSql(); var r5 = db.Queryable<InsertTest>().Where(it => it.id == 3.ObjToMoney()).ToSql(); var r6 = db.Queryable<InsertTest>().Where(it => it.v1 == par2.Trim()).ToSql(); .ToString()).ToList(); )).ToList();)).ToList(); )).ToList(); )).ToList(); )).ToList(); var c4 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name)).ToList();

2、使用原生SQL查询