//實(shí)體
public class StudentA
{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int Id{ get; set; }public string Name { get; set; }public int SchoolId { get; set; }//用例1：正常一對(duì)多[Navigate(NavigateType.OneToMany, nameof(BookA.studenId))]//BookA表中的studenIdpublic List<BookA> Books { get; set; }//注意禁止給books手動(dòng)賦值//用例2:反向?qū)Ш街С?#xff1a;StudentA沒(méi)有主鍵或者指定關(guān)系[Navigate(NavigateType.OneToMany, nameof(BookA.studenId),nameof(Id))] public List<BookA> Books { get; set; }//注意禁止給books手動(dòng)賦值//與一對(duì)一相反 第一個(gè) 從表字段，第二個(gè)主表字段}
public class BookA
{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int BookId { get; set; }public string Name { get; set; }public int studenId { get; set; }
}//例1：簡(jiǎn)單用法
var list = db.Queryable<StudentA>()
.Includes(x => x.Books)
.ToList();//例2：支持Any和Count 對(duì)主表進(jìn)行過(guò)濾 (子對(duì)象過(guò)濾看下面)
var list = db.Queryable<StudentA>()
.Includes(x => x.Books)
.Where(x => x.Books.Any())
//帶條件的
//.Where(x => x.Books.Any(z=>z.Name=="jack")))
.ToList();//例3: 沒(méi)有Includes也可以使用過(guò)濾
var list2 = db.Queryable<StudentA>()
.Where(x => x.Books.Any())//Any中可以加條件 Any(z=>z.BookId==1)
.ToList();                         //例4 Where子對(duì)象進(jìn)行排序和過(guò)濾 (支持WhereIF)
var list = db.Queryable<StudentA>().Includes(x => x.Books.Where(y => y.BookId > 0).OrderBy(y => y.BookId).ToList()).ToList();//例5 主表+子表都過(guò)濾
var list = db.Queryable<StudentA>()
.Includes(x => x.Books.Where(it=>it.Name=="jack").ToList())//只過(guò)濾子表
.Where(x => x.Books.Any(z=>z.Name=="jack")))//通過(guò)子表過(guò)濾主表
.ToList();//例6：Select指定字段
var list= db.Queryable<StudentA>().Includes(x => x.Books.Select(z=>new BookA() { Names = z.Names }).ToList()) //例7：Select多層級(jí)  (結(jié)構(gòu)：StudentAt->books->BookItems)      
var list= db.Queryable<StudentA>().Includes(x => x.Books.Select(z=>new BookA(){Names=z.Name}.ToList(),it=>BookItems)) .ToList();、//Includes中的Select只能是字段 ，不能導(dǎo)航對(duì)象           //例8：OrderBy指定字段 （Skip Take可以分頁(yè)）
var list= db.Queryable<StudentA>().Includes(x => x.Books.OrderBy(z=>z.Id).ToList()) .ToList();//例9：Take取前幾條
var list= db.Queryable<StudentA>().Includes(x => x.Books.Take(10).ToList()) .ToList();          //例10：DTO支持進(jìn)行了強(qiáng)化   
看標(biāo)題2.7        //例11：一對(duì)多后還可用追加字段映射MappingField 如果以前是1個(gè)字關(guān)聯(lián)，現(xiàn)在追加后就成了1+1       
db.Queryable<StudentA>().Includes(x => x.Books.MappingField(z=>z.字段,()=>x.字段).ToList() ).ToList();
//MappingField 和 Where區(qū)別
//MappingField MappingField用來(lái)指定2個(gè)對(duì)象的關(guān)系，Where只能當(dāng)前表過(guò)濾不能和主表進(jìn)行關(guān)聯(lián) 
//MappingField 可以多個(gè)也可以和Where一起始用

//實(shí)體
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不能賦默認(rèn)值
}
public class B1
{[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]public int Id { get; set; }public string Name { get; set; }[Navigate(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]//注意順序public List<A1> AList { get; set; }//只能是null不能賦默認(rèn)值
}  
//例1:簡(jiǎn)單用法 直接填充B的集合，只要配置好特性非常簡(jiǎn)單
var list3= db.Queryable<A1>().Includes(x => x.BList).ToList(); //例2:支持子對(duì)象排序和過(guò)濾 (支持WhereIF)
var list3= db.Queryable<A1>().Includes(x => x.BList.Where(z=>z.Id>0).ToList()).ToList(); //例3:支持主表過(guò)濾  Any和Count
var list3= db.Queryable<A1>().Includes(x => x.BList).Where(x=>x.BList.Any())//Any里面可以加條件 Any(z=>z.xxxx>0).ToList();//例4主表+子表都過(guò)濾
var list = db.Queryable<StudentA>().Includes(x => x.BList.Where(it=>it.Name=="jack").ToList())//只過(guò)濾子表.Where(x => x.BList.Any(z=>z.Name=="jack")))//通過(guò)子表過(guò)濾主表.ToList();               //不使用Includes一樣可以過(guò)濾              
var list3= db.Queryable<A1>() .Where(x=>x.BList.Any()) //可以加條件.Where(x=>x.BList.Any(z=>z.xxx==x.yyy)).ToList();                //A表和B表不是主鍵情況多了2個(gè)參數(shù) 
//升級(jí)到: SqlSugarCore 5.1.4.147-preview16+
[Navigate(typeof(OptRole), nameof(OptRole.operId), //中間表 aidnameof(OptRole.roleId),//中間表 bidnameof(OperatorInfo.id),//a表 idnameof(Role.id2))] //b表idpublic List<Role> Roles { get; set; }//四參數(shù)重載只支持查詢

var list=db.Queryable<UnitAddress011>().Includes(x => x.Persons).ToList();//m是主表字段 c是子表字段 是一個(gè)json數(shù)組 格式不要錯(cuò)了 
[Navigate(NavigateType.Dynamic, "[{m:\"Id\",c:\"AddressId\"},{m:\"Id2\",c:\"AddressId2\"}]")]
public List<UnitPerson011> Persons { get; set; }

public class StudentA
{[SugarColumn(IsPrimaryKey = true)]public int StudentId { get; set; }public string Name { get; set; }public int SchoolId { get; set; }[Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一對(duì)一public SchoolA SchoolA { get; set; }[Navigate(NavigateType.OneToMany, nameof(BookA.studenId))]//一對(duì)多public List<BookA> Books { get; set; }//只能是null不能賦默認(rèn)值}
public class SchoolA
{[SugarColumn(IsPrimaryKey = true)]public int SchoolId { get; set; }public string SchoolName { get; set; }[Navigate(NavigateType.OneToMany, nameof(RoomA.SchoolId))]//一對(duì)多public List<RoomA> RoomList { get; set; }//只能是null不能賦默認(rèn)值
}public class RoomA
{[SugarColumn(IsPrimaryKey = true)]public int RoomId { get; set; }public string RoomName { get; set; }public int SchoolId { get; set; }
}
public class BookA
{[SugarColumn(IsPrimaryKey = true)]public int BookId { get; set; }public string Name { get; set; }public int studenId { get; set; }
}       var list2 = db.Queryable<StudentA>()//查2層.Includes(st => st.SchoolA, sch=> sch.RoomList)//查詢2級(jí)(等于EF ThenInclude)//查1層.Includes(st=> st.Books)  .ToList()
//說(shuō)明: 一對(duì)多 多對(duì)多 一對(duì)多 只要配好了都可以多層級(jí)使用//如果想超過(guò)3個(gè)層級(jí)需要.AsNavQueryable()
//缺點(diǎn)VS提示會(huì)消失，直接寫不要在乎意提示不出來(lái)，VS關(guān)掉在開(kāi)就行了，只要不改這個(gè)代碼提示就不會(huì)有問(wèn)題
db.Queryable<Order>()Includes(it=>it.xx).AsNavQueryable()//加這個(gè)前面.Includes(it=>it.1,it=>it.2,it=>it.3,it=>it.4，it=>it.5..)
//.AsNavQueryable()能不用盡量不要用，正常Includes(+3)重載完全夠用了

var list = new List<Tree1>();db.Queryable<Tree1>().Includes(it => it.Child).ForEach(it => list.Add(it), 300); //每次查詢300條

//Mapster 工具映射 (推薦) 比AutoMapper方便不需要配置
//Nuget直接安裝就行了//簡(jiǎn)單示例：結(jié)構(gòu)一樣直接轉(zhuǎn)換
var list=db.Queryable<StudentA>().Includes(x => x.Books).ToList();
var dtoList=list.Adapt<List<StudentDTO>>()//技巧示例：這個(gè)用法必學(xué)通過(guò)規(guī)則映射DTOpublic class TreeDTO{public int Id { get; set; }public string Name { get; set; }public int ParentId { get; set; }public string ParentName { get; set; }//對(duì)應(yīng)Parent中的Name}
public class Tree{[SqlSugar.SugarColumn(IsPrimaryKey = true)]public int Id { get; set; }public string Name { get; set; }public int ParentId { get; set; }[Navigate(NavigateType.OneToOne,nameof(ParentId))]public Tree Parent { get; set; } 
}var list= db.Queryable<Tree>().Includes(it => it.Parent) .ToList();//DTO和List不能是同一個(gè)類不然這種映射會(huì)失效var dtolist= list.Adapt<List<TreeDTO>>();//DTO中的ParentName就有值了

//簡(jiǎn)單的用法   5.1.4.71
var list = db.Queryable<Student_004>().Includes(x => x.books).Select(x => new Student_004DTO{books = x.books }, true)//true是自動(dòng)映射其他屬性，匿名對(duì)象需要手動(dòng).ToList();//Mapster轉(zhuǎn)換  5.1.4.71
var list = db.Queryable<Student_004>().Includes(x => x.books).Select(x => new Student_004DTO{name=x.Name,books = x.books.Adapt<List<BooksDTO>>() //導(dǎo)航對(duì)象用 Mapster轉(zhuǎn)換 （NUGET安裝）}).ToList();            //DTO中用方法  5.1.4.71
var list = db.Queryable<Student_004>().Includes(x => x.books).Select(x => new Student_004DTO{name=x.Name, //導(dǎo)航對(duì)象books可以是C#任何方法結(jié)尾bookIds=x.books.Select(it=>it.id).ToList(), booksDto=x.books.Select(it=>new BookDTO(){  id=it.Id,Name=it.Name  }).ToList()}).ToList();   //聯(lián)表查詢用DTO寫法  5.1.4.71           
var list5= db.Queryable<Student_004>().Includes(x => x.school_001, x => x.rooms).Includes(x => x.books).LeftJoin<Order>((x, y) => x.Id==y.sid).Select((x,y) => new Student_004DTO{SchoolId = x.SchoolId,books = x.books,school_001 = x.school_001,Name=y.Name}).ToList();

//注意：不需 Includes 就可以使用
Where(it=>it.導(dǎo)航對(duì)象.Any())
Where(it=>it.導(dǎo)航對(duì)象.Any(z=>z.id==1))
Where(it=>it..導(dǎo)航對(duì)象.Any(List<IConditionalModel>)//5.1 //Count用法類似

//注意：不需 Includes 就可以使用
Where(x=>SqlFunc.Exists(x.SchoolA.Id))//查詢存在一對(duì)一的主表數(shù)據(jù)
Where(x=>SqlFunc.Exists(x.SchoolA.Id,List<IConditionalModel>))//查詢存在一對(duì)一的主表數(shù)據(jù)

//自動(dòng)寫法,Books下面的A和B都會(huì)查詢出來(lái)
.IncludesAllSecondLayer(x=>x.Books)  //自動(dòng)只能有這么多層次，更深層級(jí)需要手動(dòng)寫法//手動(dòng)寫法
.Includes(x => x.Books,x=>x.A) 
.Includes(x => x.Books,x=>x.B)

//聯(lián)表查詢用DTO寫法  5.1.4.71           
var list5= db.Queryable<Student_004>().Includes(x => x.school_001, x => x.rooms).Includes(x => x.books).LeftJoin<Order>((x, y) => x.Id==y.sid).Select((x,y) => new Student_004DTO{SchoolId = x.SchoolId,books = x.books,school_001 = x.school_001,Name=y.Name}).ToList();

public class StudentA
{[SugarColumn(IsPrimaryKey = true)]public int StudentId { get; set; }public string Name { get; set; }public int SchoolId { get; set; }[SugarColumn(IsIgnore = true)]public SchoolA SchoolA { get; set; }
}public class SchoolA
{[SugarColumn(IsPrimaryKey = true)]public int SchoolId { get; set; }public string SchoolName { get; set; }[SugarColumn(IsIgnore = true)]public List<RoomA> RoomList { get; set; }[SugarColumn(IsIgnore = true)]public List<TeacherA> TeacherList { get; set; }
}
public class TeacherA
{[SugarColumn(IsPrimaryKey = true)]public int Id { get; set; }public int SchoolId { get; set; }public string Name { get; set; }
}
public class RoomA
{[SugarColumn(IsPrimaryKey = true)]public int RoomId { get; set; }public string RoomName { get; set; }public int SchoolId { get; set; }
}

db.CodeFirst.InitTables<StudentA, RoomA, SchoolA,TeacherA>();
db.DbMaintenance.TruncateTable<StudentA>();
db.DbMaintenance.TruncateTable<RoomA>();
db.DbMaintenance.TruncateTable<SchoolA>();
db.DbMaintenance.TruncateTable<TeacherA>();
db.Insertable(new RoomA() { RoomId = 1, RoomName = "北大001室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 2, RoomName = "北大002室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 3, RoomName = "北大003室", SchoolId = 1 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 4, RoomName = "清華001廳", SchoolId = 2 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 5, RoomName = "清華002廳", SchoolId = 2 }).ExecuteCommand();
db.Insertable(new RoomA() { RoomId = 6, RoomName = "清華003廳", SchoolId = 2 }).ExecuteCommand();db.Insertable(new SchoolA() { SchoolId = 1, SchoolName = "北大" }).ExecuteCommand();
db.Insertable(new SchoolA() { SchoolId = 2, SchoolName = "清華" }).ExecuteCommand();db.Insertable(new StudentA() { StudentId = 1, SchoolId = 1, Name = "北大jack" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 2, SchoolId = 1, Name = "北大tom" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 3, SchoolId = 2, Name = "清華jack" }).ExecuteCommand();
db.Insertable(new StudentA() { StudentId = 4, SchoolId = 2, Name = "清華tom" }).ExecuteCommand();db.Insertable(new TeacherA() {  SchoolId=1, Id=1, Name="北大老師01" }).ExecuteCommand();
db.Insertable(new TeacherA() { SchoolId = 1, Id =2, Name = "北大老師02" }).ExecuteCommand();db.Insertable(new TeacherA() { SchoolId = 2, Id = 3, Name = "清華老師01" }).ExecuteCommand();
db.Insertable(new TeacherA() { SchoolId = 2, Id = 4, Name = "清華老師02" }).ExecuteCommand();

var list = db.Queryable<StudentA>().ToList();//這兒也可以聯(lián)表查詢
db.ThenMapper(list, stu =>
{//如果加Where不能帶有stu參數(shù)，stu參數(shù)寫到 SetContext//可以用Where寫SetContext但是不能帶有stu對(duì)象stu.SchoolA=db.Queryable<SchoolA>().SetContext(scl=>scl.SchoolId,()=>stu.SchoolId,stu).FirstOrDefault();//可以聯(lián)查詢的//stu.xxxx=db.Queryable<SchoolA>().LeftJoin<XXX>().Select(xxxx).SetContext(....).ToList();
});
// SetContext不會(huì)生成循環(huán)操作，高性能  和直接Where性能是不一樣的

var treeRoot=db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一層
db.ThenMapper(treeRoot, item =>
{item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二層
db.ThenMapper(treeRoot.SelectMany(it=>it.Child), it =>
{it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//第三層
db.ThenMapper(treeRoot.SelectMany(it => it.Child).SelectMany(it=>it.Child), it =>
{it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//這兒只是用樹(shù)型結(jié)構(gòu)來(lái)證明可以實(shí)現(xiàn)無(wú)限級(jí)別導(dǎo)航查詢 ,實(shí)際開(kāi)發(fā)中樹(shù)型結(jié)構(gòu)用ToTree實(shí)現(xiàn)
public class Tree
{
[SqlSugar.SugarColumn(IsPrimaryKey =true)]
public int Id { get; set; }
public string Name { get; set; }
public int ParentId { get; set; }
[SqlSugar.SugarColumn(IsIgnore = true)]
public Tree Parent { get; set; }
[SqlSugar.SugarColumn(IsIgnore = true)]
public List<Tree> Child { get; set; }
}
// SetContext不會(huì)生成循環(huán)操作，高性能  和直接Where性能是不一樣的

EntityService= (type, columnInfo) =>
{p.IfTable<Order>().OneToOne(it => it.Item, nameof(Order.ItemId));
}

var list3 = db.Queryable<UnitaStudentA>().IncludesAllFirstLayer().ToList();//有重載可以排除不想要的//排除說(shuō)明：//IncludesAllFirstLayer(nameof(UnitaStudentA.ProjectPhases)) //這樣就是排除ProjectPhases的導(dǎo)航屬性//可以排除多個(gè)//IncludesAllFirstLayer("a","b") //自動(dòng)導(dǎo)航如果有重復(fù)的情況: 誰(shuí)在前面執(zhí)行哪個(gè)
var list3 = db.Queryable<UnitaStudentA>().Includes(it=>it.Order.Where(s=>s.id==1).ToList()).IncludesAllFirstLayer().ToList();//自動(dòng)導(dǎo)航和Order重復(fù)//根據(jù)名字導(dǎo)航              
db.Queryable<Order>()
//等同于Includes(it=>it.ProjectPhases)
.IncludesByNameString(nameof(Order.ProjectPhases)).ToList()

?

 vra list=db.Queryable<Order>().Includes(it=>it.導(dǎo)航對(duì)象.ToList())//通過(guò).ToList()轉(zhuǎn)成SqlSugar導(dǎo)航類型就行.ToList();

var dis=db.Queryable<UnitTestDis<Cat>>().Includes(x => x.Animals).ToList();//T是Cat那么就能導(dǎo)航Catvar dis2 = db.Queryable<UnitTestDis<Dog>>().Includes(x => x.Animals).ToList();//T是Dog那么就能導(dǎo)航Dog