文档SqlSugar .Net ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网### 一、基本配置1、nuget 包 SqlSugarCoreMicrosoft.Extensions.DependencyInjection2、新建类用于配置sqlsugar using SqlSugar; namespace SqlSugar1_lianxi.DbConfig { public class SqlSugarConfig { public SqlSugarClient Db { get; } public SqlSugarConfig(IConfiguration configuration) { Db = new SqlSugarClient( new List() { //多租户 new ConnectionConfig(){ ConfigId = “1”, ConnectionString = configuration.GetConnectionString(“SqlServer”), DbType = DbType.SqlServer, // 根据你的数据库类型选择 IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute, // 初始化键类型 LanguageType = LanguageType.Chinese // 其他配置… }, new ConnectionConfig(){ ConfigId = “2”, ConnectionString = new ConfigurationBuilder().Build().GetConnectionString(“SqlServer1”), DbType = DbType.SqlServer, // 根据你的数据库类型选择 IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute, // 初始化键类型 LanguageType = LanguageType.Chinese // 其他配置… } }, db => { //里面可以循环 //SQL执行前 db.GetConnection(“1”).Aop.OnLogExecuting = (sql, p) => { Console.WriteLine(sql); }; //SQL执行前 db.GetConnection(“2”).Aop.OnLogExecuting = (sql, p) => { Console.WriteLine(sql); }; //如果是 日志库 不要加AOP防止AOP循环引用 //SQL执行完 db.GetConnection(“1”).Aop.OnLogExecuted = (sql, pars) => { if (db.Ado.SqlExecutionTime.TotalSeconds > 1)//如果执行时间大于1秒 { //代码CS文件名 //var fileName = db.Ado.SqlStackTrace.FirstFileName; //代码行数 // fileLine = db.Ado.SqlStackTrace.FirstLine; //方法名 //var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName; //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息 } //执行完了可以输出SQL执行时间(OnLogExecutedDelegate) Console.Write(“SQL执行time:” + db.Ado.SqlExecutionTime.ToString()); }; db.GetConnection(“2”).Aop.OnLogExecuted = (sql, pars) => { if (db.Ado.SqlExecutionTime.TotalSeconds > 1) { //代码CS文件名 var fileName = db.Ado.SqlStackTrace.FirstFileName; //代码行数 var fileLine = db.Ado.SqlStackTrace.FirstLine; //方法名 var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName; //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息 } //执行完了可以输出SQL执行时间 (OnLogExecutedDelegate) //Console.Write(“time:” + db.Ado.SqlExecutionTime.ToString()); }; } ); } } } 并在program.cs添加服务 builder.Services.AddScoped();### 二、model实体类配置##### 1、单表配置 [SugarTable(“Student”)]//当和数据库名称不一样设置真实表名 public class student { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//主键且自增，一个属性只能有一个SugarColumn，多个配置可以写在一个SugarColumn里 public int Id { get; set; } public string Name { get; set; } public string Sex { get; set; } //[SugarColumn(ColumnName = “C_time”)]//数据库与实体不一样设置真实的表的列名 //[SugarColumn(IsIgnore = true)]// IsIgnore=true表示 ORM 所有操作不处理这列 //[SugarColumn(IsOnlyIgnoreInsert = true)]//插入操作时不处理该列 //[SugarColumn(IsOnlyIgnoreUpdate = true)]//更新操作不处理该列 //[SugarColumn(InsertServerTime = true)]//插入操作：true数据库时间 [SugarColumn(UpdateServerTime = true)]//更新操作：true数据库时间 public DateTime CreatedDate { get; set; } //public string Name { get; set; } = null!;//必需的不要使用 required，使用null! }### 三、查询##### 1、基本查询 //1、select * from Student where id = 1 List students = dbcontext.Db.Queryable().Where(o => o.Id == 1).ToList(); //2、动态OR或AND查询 var exp = Expressionable.Create(); exp.OrIF(条件, it => it.Id == 1);//.OrIf 是条件成立才会拼接OR exp.Or(it => it.Name.Contains(“jack”));//拼接OR var list = db.Queryable().Where(exp.ToExpression()).ToList(); //3、根据主键查询，select * from Student where id=2 db.Queryable().InSingle(2) //通过主键查询 SingleById db.Queryable().Single(it => it.Id == 2) //根据ID查询，如果结果大于1条会抛出错误 //4、查询前几条 db.Queryable().First(it => it.Id == 1); //查询第一条，没有返回Null db.Queryable().Take(10).ToList();//查前几条 //5、是否存在记录，返回bool值 db.Queryable().Where(it => it.Id > 11).Any(); db.Queryable().Any(it => it.Id > 11); //上面语法的简化 //6、获取最大值 db.Queryable().Max(it => it.Id);//同步 db.Queryable().MaxAsync(it => it.Id);//异步 //7、联表查询 //是主表 var leftQuery = db.Queryable().IgnoreColumns(it => it.Files); var list = db.Queryable(leftQuery).LeftJoin((o, d) => o.id == d.orderid).Select(o => o).ToList(); //是Join的表 var rightQuery = db.Queryable().IgnoreColumns(it => it.Files); var list = db.Queryable().LeftJoin(rightQuery, (o, d) => o.Id == d.OrderId).Select(o => o).ToList(); ##### 2、分页查询 //1、如果数据库版本较新可以用 ToOffsetPage 取代 ToPageList .ToPageList(pagenumber, pageSize)// 不返回Count .ToPageList(pagenumber, pageSize, ref totalCount)//返回Count //2、同步分页 int pagenumber = 1; // pagenumber是从1开始的不是从零开始的 int pageSize = 20; int totalCount = 0; //单表分页 var page = dbcontext.Db.Queryable().ToPageList(pagenumber, pageSize, ref totalCount); //多表分页 var list = db.Queryable().LeftJoin((st, sc) => st.SchoolId == sc.Id) .Select((st, sc) => new { Id = st.Id, Name = st.Name, SchoolName = sc.Name }) .ToPageList(pageIndex, pageSize, ref totalCount); //3、异步分页 RefAsync total = 0;//REF和OUT不支持异步,想要真的异步这是最优解 Db.Queryable().ToPageListAsync(pagenumber, pageSize, total);//ToPageAsync##### 3、分组、去重、开窗 //1、分组基本用法 var a = dbcontext.Db.GetConnection(“1”).Queryable() .GroupBy(o => o.Sex) .Where(o => o.Sex != “8”) //.Having(o => SqlFunc.AggregateCount(o.Id) >= 2) .Select(o => new { sex = o.Sex, avg = SqlFunc.AggregateAvg(o.Id), count = SqlFunc.AggregateCount(o.Id) }).OrderBy(o => o.sex, OrderByType.Desc).ToList(); //2、排序统计列 var a = dbcontext.Db.GetConnection(“1”).Queryable() .GroupBy(o => o.Sex) .Where(o => o.Sex != “8”) //.Having(o => SqlFunc.AggregateCount(o.Id) >= 2) .Select(o => new { sex = o.Sex, avg = SqlFunc.AggregateAvg(o.Id), count = SqlFunc.AggregateCount(o.Id) }) .MergeTable()//官网说需要加MergeTable才能排序统计过的列，但上面那个我没加这个直接排序也可以，不清楚原因 .OrderBy(o => o.sex, OrderByType.Desc).ToList(); //3、开窗函数语法实现 var a = dbcontext.Db.GetConnection(“1”) .Queryable() .Select(o => new { index = SqlFunc.RowNumber($“{o.Name} asc, {o.Id} asc”, $“{o.Sex}”),//partition by Sex order by Name. Id id = o.Id, name = o.Name, sex = o.Sex }) .MergeTable()//将结果合并成一个表 //mergetable之前要有Select，但不能有OrderBy .Where(o => o.index > 1) .OrderBy(o => o.sex) .OrderBy(o => o.index) .ToList(); //其他开窗函数 count = SqlFunc.RowCount(),// count (1) over() max = SqlFunc.RowMax(it.num ?? 0),// max(isnull(num,0)) over() min = SqlFunc.RowMin(it.num ?? 0),// min(isnull(num,0)) over() avg = SqlFunc.RowAvg(it.num ?? 0),// avg(isnull(num,0)) over() index = SqlFunc.RowNumber(it.Id), // row_number() over( order by a.Id) index = SqlFunc.RowNumber(it.Id, it.Name)// row_number() over( partition by name order by a.Id) //4、将分组后的数据，组合成list var a = dbcontext.Db.GetConnection(“1”) .Queryable() .GroupBy(o => o.Sex) .Select(o => new { sex = o.Sex, list = SqlFunc.Subqueryable().Where(it => it.Sex == o.Sex).ToList() }).ToList(); 注：Select位置：正常情况后面一般是 .Where(…).OrderBy(…).Select(…).ToList()如果Where、groupby、orderby等要写在Select后面应该 用Select(…).MergeTable().Where##### 4、连表查询4.1、直接表连接 //联表查询 //1、5个表以内的联表非常爽 var query5 = db.Queryable() .LeftJoin((o, cus) => o.CustomId == cus.Id)//多个条件用&&，内连接：InnerJoin .LeftJoin((o, cus, oritem) => o.Id == oritem.OrderId) .Where(o => o.Id == 1) .Select((o, cus, oritem) => new { Id = o.Id, CustomName = cus.Name }) .ToList(); //2、5个以上的表 db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos( JoinType.Left, o.Id == i.OrderId, //左连接 左链接 左联 JoinType.Left, o.CustomId == c.Id )) .Select((o, i, c) => new { name = o.Name }) .ToList() //3、如果全是Inner Join可以用这种方式直接联表 var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId && c.Id == o.CustomId) .Select((o, i, c) => new { Id = o.Id, Name = o.Name, CustomName = c.Name }) .ToList(); //4、子查询 var a = dbcontext.Db.Queryable() .Select(o => new { id = o.Id, name = o.Name, list = SqlFunc.Subqueryable().Where(a => a.Id == o.Id).ToList()//会在嵌套一层查询，也可以查其他表 }).ToList(); return Ok(a); //5、多个Queryable JOIN var q11 = db.Queryable().Where(it => it.Id>1); var q22 = db.Queryable().Where(it => it.Id > 2); var q33 = db.Queryable().Where(it => it.Id > 3); var list= q11.LeftJoin(q22, (x, y) => x.Id == y.Id) .LeftJoin(q33, (x, y, z) => x.Id == z.Id) .ToList();##### 5、导航查询5.1、一对一数据库表Student数据库表School实体类 public class Student { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } = null!; public string Sex { get; set; } = null!; [SugarColumn(ColumnName = “C_time”)] public DateTime CreatedDate { get; set; } public int SchoolId { get; set; } //用例1：主键模式 Student（主表）表中的 SchoolId 和School（子表）中的主键关联 [Navigate(NavigateType.OneToOne, nameof(SchoolId))] public School School { get; set; } //用例2：反向导航，2个字段匹配关系 [Navigate(NavigateType.OneToOne, nameof(SchoolId), nameof(School.SchoolId))]//第一个主表字段，第二从表字段 public School Schools { get; set; }变量名不要和类名相同 } public class School { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int SchoolId { get; set; } public string SchoolName{ get; set; } public DateTime C_time { get; set; } }测试查询 //1、基础查询 var a = dbcontext.Db.GetConnection(“1”).Queryable() .Includes(o => o.School)//填充子对象 .Where(o => o.School.SchoolName == “鹿邑县高级中学2”) .ToList(); return Ok(a); //2、只查School中的一个字段 var a = dbcontext.Db.GetConnection(“1”).Queryable() .Includes(o => o.School)//填充子对象 .Where(o => o.School.SchoolName == “鹿邑县高级中学2”) .Select(o => new { o=o, schoolname = o.School.SchoolName }) .ToList(); return Ok(a);5.2、一对多数据库表Student1数据库表Bookmodel配置类 public class Student1 { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } = null!; public string Sex { get; set; } = null!; [SugarColumn(ColumnName = “C_time”)] public DateTime CreatedDate { get; set; } public int SchoolId { get; set; } //用例1：主键模式 [Navigate(NavigateType.OneToMany, nameof(Book.StudentId))]//Book表中的StudenId public List Books { get; set; } //用例2：反向导航：Student没有主键或者指定关系 //[Navigate(NavigateType.OneToOne, nameof(Book.StudentId), nameof(Id))]//与一对一相反，第一个从表字段，第二主表字段 //public List Books { get; set; } } public class Book { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int BookId { get; set; } public string BookName { get; set; } public DateTime C_time { get; set; } public int StudentId { get; set; } }测试 //导航查询 一对多 [HttpGet] public IActionResult G_Method1() { var a = dbcontext.Db.Queryable() //.Includes(o => o.Books) //.Includes(x => x.Books.Where(y => y.BookId > 1).OrderBy(y => y.BookId).ToList())//子对象进行排序和过滤 .Includes(o=>o.Books.Select(o=>new Book { BookId = o.BookId, BookName = o.BookName }).ToList())//Select指定子表字段 //.Where(x => x.Books.Any(z=>z.Name==“jack”)))//对主表进行过滤 .ToList(); return Ok(a); }多对多以及转DTO直接看官网2.3/2.5/2.7多级查询、导航查询 - SqlSugar 5x - .NET果糖网##### 6、并集查询 var q1 = db.Queryable().Select(it=>new { name=it.Name }); //select结构要一样 var q2 = db.Queryable().Select(it => new { name = it.Name }); var list = db.UnionAll(q1, q2).ToList();