不同于其它ORM，需要外键配合实现级联删除，SqlSugar中仅需要在实体中配置导航字段即可方便实现级联删除
1、导航删除语法（多层级）
设计参考于EF Core查询，只要配置好实体就可以随意使用导航进行导航删除操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
db.DeleteNav<Student>(it=>it.Id==1)//删除主表 Student(id=1) .Include(z1 => z1.SchoolA) .ThenInclude(z1 => z1.RoomList) //删除2层 Root->ShoolA->RoomList .Include(z1 => z1.Books)// 删除1层 Root->Books .ExecuteCommand(); //也可能根据集合删除，不需有子对象 List<Student> list=db.Queryable<Student>().Where(it=>it.id==1).ToList(); db.DeleteNav<Student>()//删除主表 Student(id=1) .Include(z1 => z1.SchoolA) .ThenInclude(z1 => z1.RoomList) //删除2层 Root->ShoolA->RoomList .Include(z1 => z1.Books)// 删除1层 Root->Books .ExecuteCommand() //Include表式从第一层开始，ThenInclude表式在上一层继续向下 Include(z1 => z1.A) .ThenInclude(z1 => z1.B).ThenInclude(z1 => z1.C) Include(z1 => z1.A) .ThenInclude(z1 => z1.B).ThenInclude(z1 => z1.D) |
2、一对一
2.1 逻辑
先删子表，在删主表
2.2 用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//实体 public class StudentA { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int StudentId { get; set; } public string Name { get; set; } public int SchoolId { get; set; } //标准配置 推荐 [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是StudentA类里面的 public SchoolA SchoolA { get; set; } //不能赋值只能是null } public class SchoolA { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id{ get; set; } public string SchoolName { get; set; } } //代码 ：先删SchoolA在删StudentA db.DeleteNav<StudentA>(it=>it.Id==1) .Include(z1 => z1.SchoolA ) .ExecuteCommand(); |
3、一对多
3.1 逻辑
先删子表，在删主表
3.2 用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//实体 public class StudentA { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id{ get; set; } public string Name { get; set; } public int SchoolId { get; set; } [Navigate(NavigateType.OneToMany, nameof(BookA.studenId))]//BookA表中的studenId public List<BookA> Books { get; set; }//注意禁止给books手动赋值 } public class BookA { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int BookId { get; set; } public string Name { get; set; } public int studenId { get; set; } } //代码 db.DeleteNav<StudentA>(it=>it.Id==1) .Include(z1 => z1.Books) .ExecuteCommand(); |
4、多对多
4.1 实体配置
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//中间表 public class ABMapping1 { [SugarColumn(IsPrimaryKey = true)]//中间表可以不是主键 public int AId { get; set; } [SugarColumn(IsPrimaryKey = true)]//中间表可以不是主键 public int BId { get; set; } } public class A1 { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [Navigate(typeof(ABMapping1), nameof(ABMapping1.AId), nameof(ABMapping1.BId))]//注意顺序 public List<B1> BList { get; set; }//只能是null不能赋默认值 } public class B1 { [SugarColumn(IsPrimaryKey = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [Navigat(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]//注意顺序 public List<A1> AList { get; set; }//只能是null不能赋默认值 } |
A表代表主表，B表代表从表， 中间表 是A和B的关系表
4.2 只删中间表（1张表）
|
1 2 3 4 |
//只删除关系 db.DeleteNav<UserInfo>(x=>x.id==1) .Include(x => x.Roles) // Roles我们称为B表 .ExecuteCommand(); |
4.3 删A表和中间表（2张表）
|
1 2 3 4 5 6 |
//比如用户、角色、和中间表 这个操作一般是删除用户（因为删用户不删关系存在脏数据） db.DeleteNav<UserInfo>(x=>x.id==1) .Include(x => x.Roles,new DeleteNavOptions() { ManyToManyIsDeleteA=true }) .ExecuteCommand(); |
4.4 删B表和中间表（2张表）
|
1 2 3 4 5 |
db.DeleteNav<UserInfo>(x=>x.id== list4.First().id) .Include(x => x.Roles,new DeleteNavOptions() { ManyToManyIsDeleteB=true }) .ExecuteCommand(); |
4.5 删A、B和中间表（3张表）
|
1 2 3 4 5 6 |
db.DeleteNav<UserInfo>(x=>x.id== 1) .Include(x => x.Roles,new DeleteNavOptions() { ManyToManyIsDeleteB=true, ManyToManyIsDeleteA=true }) .ExecuteCommand(); |
5、自动导航删除
新功能：5.1.4.108
支持第2层级的所有导航自动Includes (超过2层的需要用手动导航删除)
|
1 2 3 4 5 6 7 8 |
db.DeleteNav(list).IncludesAllFirstLayer().ExecuteCommand(); db.DeleteNav(list).IncludesAllFirstLayer(nameof(类.导航),nameof(类.导航2)).ExecuteCommand();//排除不需要的导航 //3级+自动 db.DeleteNav(list) .IncludesAllFirstLayer()//自动2级 .IncludeByNameString(nameof(类.导航)).ThenIncludeByNameString(nameof(类.导航2))//3级 .ExecuteCommand(); |
自动导航 这个看不懂？可以看导航插入 有详细的例子原理一样
