文档 SqlSugar .Net ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网
一、基本配置
1、nuget 包 SqlSugarCore
Microsoft.Extensions.DependencyInjection
2、新建类用于配置sqlsugar
using SqlSugar;
namespace SqlSugar1_lianxi.DbConfig
{
public class SqlSugarConfig
{
public SqlSugarClient Db { get; }
public SqlSugarConfig(IConfiguration configuration)
{
Db = new SqlSugarClient(
new List<ConnectionConfig>()
{
//多租户
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<SqlSugarConfig>();
二、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<Student> students = dbcontext.Db.Queryable<Student>().Where(o => o.Id == 1).ToList();
//2、动态OR或AND查询
var exp = Expressionable.Create<Student>();
exp.OrIF(条件, it => it.Id == 1);//.OrIf 是条件成立才会拼接OR
exp.Or(it => it.Name.Contains("jack"));//拼接OR
var list = db.Queryable<Student>().Where(exp.ToExpression()).ToList();
//3、根据主键查询,select * from Student where id=2
db.Queryable<Student>().InSingle(2) //通过主键查询 SingleById
db.Queryable<Student>().Single(it => it.Id == 2) //根据ID查询,如果结果大于1条会抛出错误
//4、查询前几条
db.Queryable<Student>().First(it => it.Id == 1); //查询第一条,没有返回Null
db.Queryable<Student>().Take(10).ToList();//查前几条
//5、是否存在记录,返回bool值
db.Queryable<Student>().Where(it => it.Id > 11).Any();
db.Queryable<Student>().Any(it => it.Id > 11); //上面语法的简化
//6、获取最大值
db.Queryable<Order>().Max(it => it.Id);//同步
db.Queryable<Order>().MaxAsync(it => it.Id);//异步
//7、联表查询
//是主表
var leftQuery = db.Queryable<Order>().IgnoreColumns(it => it.Files);
var list = db.Queryable(leftQuery).LeftJoin<OrderDetails>((o, d) => o.id == d.orderid).Select(o => o).ToList();
//是Join的表
var rightQuery = db.Queryable<OrderItem>().IgnoreColumns(it => it.Files);
var list = db.Queryable<Order>().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<Student>().ToPageList(pagenumber, pageSize, ref totalCount);
//多表分页
var list = db.Queryable<Student>().LeftJoin<School>((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<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
Db.Queryable<Order>().ToPageListAsync(pagenumber, pageSize, total);//ToPageAsync
3、分组、去重、开窗
//1、分组基本用法
var a = dbcontext.Db.GetConnection("1").Queryable<Student>()
.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<Student>()
.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<Student>()
.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<Student>()
.GroupBy(o => o.Sex)
.Select(o => new
{
sex = o.Sex,
list = SqlFunc.Subqueryable<Student>().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<Order>()
.LeftJoin<Custom>((o, cus) => o.CustomId == cus.Id)//多个条件用&&,内连接:InnerJoin
.LeftJoin<OrderDetail>((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<Student1>()
.Select(o => new
{
id = o.Id,
name = o.Name,
list = SqlFunc.Subqueryable<Student1>().Where(a => a.Id == o.Id).ToList()//会在嵌套一层查询,也可以查其他表
}).ToList();
return Ok(a);
//5、多个Queryable JOIN
var q11 = db.Queryable<Order>().Where(it => it.Id>1);
var q22 = db.Queryable<Order>().Where(it => it.Id > 2);
var q33 = db.Queryable<Order>().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<Student>()
.Includes(o => o.School)//填充子对象
.Where(o => o.School.SchoolName == "鹿邑县高级中学2")
.ToList();
return Ok(a);
//2、只查School中的一个字段
var a = dbcontext.Db.GetConnection("1").Queryable<Student>()
.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
数据库表Book
model配置类
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<Book> Books { get; set; }
//用例2:反向导航:Student没有主键或者指定关系
//[Navigate(NavigateType.OneToOne, nameof(Book.StudentId), nameof(Id))]//与一对一相反,第一个从表字段,第二主表字段
//public List<Book> 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<Student1>()
//.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<Student>().Select(it=>new { name=it.Name }); //select结构要一样
var q2 = db.Queryable<School>().Select(it => new { name = it.Name });
var list = db.UnionAll(q1, q2).ToList();