1.6 Query‐Include

1、Nav query

One to one /One to many / Many to many

2、Complete example

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
    {
        /// <summary>
        /// Initialize navigation query examples.
        /// </summary>
        public static void Init()
        {
            var db = DbHelper.GetNewDb();

            // 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; }
        }
    }
}

3、Multilevel navigation

var list2 = db.Queryable<StudentA>()
// 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()