1.6 Query‐Include (original) (raw)
Does not depend on the database structure, only requires entity configuration can be used
using SqlSugar; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks;
namespace OrmTest
{
internal class _6_NavQuery
{
///
// Initialize database table structures.
InitializeDatabase(db);
// One-to-One navigation query test.
OneToOneTest(db);
// One-to-Many navigation query test.
OneToManyTest(db);
// Many-to-Many navigation query test.
ManyToManyTest(db);
}
/// <summary>
/// Test many-to-many navigation queries.
/// </summary>
private static void ManyToManyTest(SqlSugarClient db)
{
// Many-to-many navigation query, query table A and include its BList.
var list4 = db.Queryable<A>().Includes(it => it.BList).ToList();
// Many-to-many navigation query with filtered BList while preserving the original A records, regardless of the filter on BList.
var list5 = db.Queryable<A>().Includes(it => it.BList.Where(s => s.BId == 1).ToList()).ToList();
// Many-to-many navigation query with filtered A records while preserving the original BList, regardless of the filter on A records.
var list6 = db.Queryable<A>().Includes(it => it.BList)
.Where(it =>it.BList.Any(s => s.BId == 1)).ToList();
// Many-to-many navigation query with filtered BList and filtered A records, but not preserving the original A and B records.
var list7 = db.Queryable<A>()
.Includes(it => it.BList.Where(s => s.BId == 1).ToList())
.Where(it => it.BList.Any(s => s.BId == 1)).ToList();
}
/// <summary>
/// Test one-to-many navigation queries.
/// 测试一对多导航查询。
/// </summary>
private static void OneToManyTest(SqlSugarClient db)
{
// One-to-many navigation query, query table Student and include its Books.
var list4 = db.Queryable<Student>().Includes(it => it.Books).ToList();
// One-to-many navigation query with filtered Books while preserving the original Student records, regardless of the filter on Books.
var list5 = db.Queryable<Student>().Includes(it => it.Books.Where(s => s.BookId == 1).ToList()).ToList();
// One-to-many navigation query with filtered Student records while preserving the original Books, regardless of the filter on Student records.
var list6 = db.Queryable<Student>().Includes(it => it.Books)
.Where(it => it.Books.Any(s => s.BookId == 1)).ToList();
// One-to-many navigation query with filtered Books and filtered Student records, but not preserving the original Student and Books records.
var list7 = db.Queryable<Student>()
.Includes(it => it.Books.Where(s => s.BookId == 1).ToList())
.Where(it => it.Books.Any(s => s.BookId == 1)).ToList();
}
/// <summary>
/// Test one-to-one navigation queries.
/// </summary>
private static void OneToOneTest(SqlSugarClient db)
{
// One-to-one navigation query with condition, query table Student and include its associated School with specific SchoolId.
var list = db.Queryable<Student>()
.Where(it => it.School.SchoolId == 1)
.ToList();
// Inner join navigation query, query table Student and include its associated School.
var list2 = db.Queryable<Student>().IncludeInnerJoin(it => it.School)
.ToList();
// Includes navigation query, query table Student and include its associated School.
var list3 = db.Queryable<Student>().Includes(it => it.School).ToList();
}
/// <summary>
/// Initialize database tables and insert sample data for navigation query examples.
/// </summary>
private static void InitializeDatabase(SqlSugarClient db)
{
// Initialize and truncate tables for Student, School, and Book entities.
db.CodeFirst.InitTables<Student, School, Book>();
db.DbMaintenance.TruncateTable<Student, School, Book>();
// Sample data for Student, School, and Book entities.
var students = new List<Student>
{
new Student
{
Name = "Student 1",
SexCode = "M",
School = new School { SchoolName = "School 1" },
Books = new List<Book>
{
new Book { Name = "Book 1" },
new Book { Name = "Book 2" }
}
},
new Student
{
Name = "Student 2",
SexCode = "F",
School = new School { SchoolName = "School 2" },
Books = new List<Book>
{
new Book { Name = "Book 3" }
}
}
};
// Insert sample data for Student, School, and Book entities using navigation properties.
db.InsertNav(students)
.Include(it => it.School)
.Include(it => it.Books).ExecuteCommand();
// Initialize and truncate tables for A, B, and ABMapping entities.
db.CodeFirst.InitTables<A, B, ABMapping>();
db.DbMaintenance.TruncateTable<A, B, ABMapping>();
// Sample data for A, B, and ABMapping entities.
List<A> a1 = new List<A> { new A() { Name = "A1" }, new A() { Name = "A2" } };
B b1 = new B { Name = "B1" };
B b2 = new B { Name = "B2" };
a1[0].BList = new List<B> { b1, b2 };
// Insert sample data for A, B, and ABMapping entities using navigation properties.
// 使用导航属性插入A、B和ABMapping表的示例数据。
db.InsertNav(a1).Include(x => x.BList).ExecuteCommand();
}
/// <summary>
/// Student entity representing the Student table in the database.
/// </summary>
[SugarTable("Student06")]
public class Student
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int StudentId { get; set; }
public string Name { get; set; }
public string SexCode { get; set; }
public int SchoolId { get; set; }
// One-to-One navigation property to School entity.
[Navigate(NavigateType.OneToOne, nameof(SchoolId))]
public School School { get; set; }
// One-to-Many navigation property to Book entities.
[Navigate(NavigateType.OneToMany, nameof(Book.StudentId))]
public List<Book> Books { get; set; }
}
/// <summary>
/// School entity representing the School table in the database.
/// </summary>
[SugarTable("School06")]
public class School
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int SchoolId { get; set; }
public string SchoolName { get; set; }
}
/// <summary>
/// Book entity representing the Book table in the database.
/// </summary>
[SugarTable("Book06")]
public class Book
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int BookId { get; set; }
public string Name { get; set; }
public int StudentId { get; set; }
}
/// <summary>
/// A entity representing the A table in the database for many-to-many relationship.
/// </summary>
[SugarTable("A06")]
public class A
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int AId { get; set; }
public string Name { get; set; }
// Many-to-Many navigation property to B entities using ABMapping table.
[Navigate(typeof(ABMapping), nameof(ABMapping.AId), nameof(ABMapping.BId))]
public List<B> BList { get; set; }
}
/// <summary>
/// B entity representing the B table in the database for many-to-many relationship.
/// </summary>
[SugarTable("B06")]
public class B
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int BId { get; set; }
public string Name { get; set; }
// Many-to-Many navigation property to A entities using ABMapping table.
[Navigate(typeof(ABMapping), nameof(ABMapping.BId), nameof(ABMapping.AId))]
public List<A> AList { get; set; }
}
/// <summary>
/// ABMapping entity representing the intermediate table for many-to-many relationship between A and B entities.
/// </summary>
[SugarTable("ABMapping06")]
public class ABMapping
{
[SugarColumn(IsPrimaryKey = true)]
public int AId { get; set; }
[SugarColumn(IsPrimaryKey = true)]
public int BId { get; set; }
}
}}
var list2 = db.Queryable() // Include Layer 2 .Includes(st => st.SchoolA, sch=> sch.roomlist)// Query level 2 (equal to EF ThenInclude) // Include Layer 1 .Includes(st=> st.Books) .ToList()