第七章：多表联查与导航属性

7.1 联表查询基础

7.1.1 Inner Join内连接

内连接返回两个表中匹配的记录:

public class InnerJoinExamples
{
    private readonly SqlSugarClient _db;

    public InnerJoinExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础内连接
    public List<UserDeptDto> BasicInnerJoin()
    {
        return _db.Queryable<User, Department>((u, d) => new JoinQueryInfos(
                JoinType.Inner, u.DepartmentId == d.Id
            ))
            .Select((u, d) => new UserDeptDto
            {
                UserId = u.Id,
                UserName = u.Name,
                UserEmail = u.Email,
                DepartmentName = d.Name,
                DepartmentCode = d.Code
            })
            .ToList();
    }

    // 使用InnerJoin方法
    public List<UserDeptDto> UseInnerJoinMethod()
    {
        return _db.Queryable<User>()
            .InnerJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new UserDeptDto
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentName = d.Name
            })
            .ToList();
    }

    // 带条件的内连接
    public List<UserDeptDto> InnerJoinWithWhere(int minAge, int deptStatus)
    {
        return _db.Queryable<User>()
            .InnerJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Where((u, d) => u.Age >= minAge)
            .Where((u, d) => d.Status == deptStatus)
            .Select((u, d) => new UserDeptDto
            {
                UserId = u.Id,
                UserName = u.Name,
                UserAge = u.Age,
                DepartmentName = d.Name
            })
            .ToList();
    }

    // 内连接后分组统计
    public List<object> InnerJoinWithGroup()
    {
        return _db.Queryable<User>()
            .InnerJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .GroupBy((u, d) => d.Name)
            .Select((u, d) => new
            {
                DepartmentName = d.Name,
                UserCount = SqlFunc.AggregateCount(u.Id),
                AvgAge = SqlFunc.AggregateAvg(u.Age)
            })
            .ToList<object>();
    }
}

public class UserDeptDto
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string UserEmail { get; set; }
    public int UserAge { get; set; }
    public string DepartmentName { get; set; }
    public string DepartmentCode { get; set; }
}

7.1.2 Left Join左连接

左连接返回左表所有记录及右表匹配记录:

public class LeftJoinExamples
{
    private readonly SqlSugarClient _db;

    public LeftJoinExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础左连接
    public List<UserDeptDto> BasicLeftJoin()
    {
        return _db.Queryable<User>()
            .LeftJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new UserDeptDto
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentName = d.Name ?? "未分配部门"
            })
            .ToList();
    }

    // 查询未分配部门的用户
    public List<User> GetUsersWithoutDepartment()
    {
        return _db.Queryable<User>()
            .LeftJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Where((u, d) => d.Id == null)
            .Select((u, d) => u)
            .ToList();
    }

    // 左连接多表
    public List<object> LeftJoinMultipleTables()
    {
        return _db.Queryable<User>()
            .LeftJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .LeftJoin<Role>((u, d, r) => u.RoleId == r.Id)
            .Select((u, d, r) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentName = d.Name ?? "未分配",
                RoleName = r.Name ?? "无角色"
            })
            .ToList<object>();
    }

    // 左连接统计
    public List<object> LeftJoinWithStats()
    {
        return _db.Queryable<Department>()
            .LeftJoin<User>((d, u) => d.Id == u.DepartmentId)
            .GroupBy((d, u) => new { d.Id, d.Name })
            .Select((d, u) => new
            {
                DepartmentId = d.Id,
                DepartmentName = d.Name,
                UserCount = SqlFunc.AggregateCount(u.Id)
            })
            .ToList<object>();
    }
}

7.1.3 Right Join右连接

右连接返回右表所有记录及左表匹配记录:

public class RightJoinExamples
{
    private readonly SqlSugarClient _db;

    public RightJoinExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础右连接
    public List<object> BasicRightJoin()
    {
        return _db.Queryable<User>()
            .RightJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentId = d.Id,
                DepartmentName = d.Name
            })
            .ToList<object>();
    }

    // 查询没有员工的部门
    public List<Department> GetDepartmentsWithoutUsers()
    {
        return _db.Queryable<User>()
            .RightJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Where((u, d) => u.Id == null)
            .Select((u, d) => d)
            .ToList();
    }
}

7.1.4 Full Join全连接

全连接返回两个表的所有记录:

public class FullJoinExamples
{
    private readonly SqlSugarClient _db;

    public FullJoinExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 全连接(MySQL不直接支持,需要用Union实现)
    public List<object> FullJoinSimulation()
    {
        var leftJoin = _db.Queryable<User>()
            .LeftJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentId = d.Id,
                DepartmentName = d.Name
            });

        var rightJoin = _db.Queryable<User>()
            .RightJoin<Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentId = d.Id,
                DepartmentName = d.Name
            });

        return _db.Union(leftJoin, rightJoin).ToList<object>();
    }
}

7.2 导航属性

7.2.1 导航属性定义

定义实体类之间的导航关系:

// 用户实体
public class User
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int DepartmentId { get; set; }
    public int RoleId { get; set; }

    // 导航属性 - 一对一
    [Navigate(NavigateType.OneToOne, nameof(Id))]
    public UserProfile Profile { get; set; }

    // 导航属性 - 多对一
    [Navigate(NavigateType.ManyToOne, nameof(DepartmentId))]
    public Department Department { get; set; }

    [Navigate(NavigateType.ManyToOne, nameof(RoleId))]
    public Role Role { get; set; }

    // 导航属性 - 一对多
    [Navigate(NavigateType.OneToMany, nameof(Order.UserId))]
    public List<Order> Orders { get; set; }

    // 导航属性 - 多对多
    [Navigate(typeof(UserRole), nameof(UserRole.UserId), nameof(UserRole.RoleId))]
    public List<Role> Roles { get; set; }
}

// 部门实体
public class Department
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }

    // 导航属性 - 一对多
    [Navigate(NavigateType.OneToMany, nameof(User.DepartmentId))]
    public List<User> Users { get; set; }
}

// 订单实体
public class Order
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string OrderNo { get; set; }
    public int UserId { get; set; }
    public decimal Amount { get; set; }

    // 导航属性 - 多对一
    [Navigate(NavigateType.ManyToOne, nameof(UserId))]
    public User User { get; set; }

    // 导航属性 - 一对多
    [Navigate(NavigateType.OneToMany, nameof(OrderDetail.OrderId))]
    public List<OrderDetail> Details { get; set; }
}

// 用户资料实体
public class UserProfile
{
    [SugarColumn(IsPrimaryKey = true)]
    public int UserId { get; set; }
    public string Avatar { get; set; }
    public string Bio { get; set; }
    public DateTime Birthday { get; set; }

    // 导航属性 - 一对一
    [Navigate(NavigateType.OneToOne, nameof(UserId))]
    public User User { get; set; }
}

7.2.2 Mapper映射

使用Mapper自动填充导航属性:

public class MapperExamples
{
    private readonly SqlSugarClient _db;

    public MapperExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础Mapper使用
    public List<User> BasicMapper()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Department, u => u.DepartmentId)
            .ToList();
    }

    // 映射多个导航属性
    public List<User> MapperMultiple()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Department, u => u.DepartmentId)
            .Mapper(u => u.Role, u => u.RoleId)
            .Mapper(u => u.Profile, u => u.Id)
            .ToList();
    }

    // 映射一对多关系
    public List<User> MapperOneToMany()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Orders, u => u.Orders.First().UserId)
            .ToList();
    }

    // 条件映射
    public List<User> MapperWithCondition()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Orders,
                u => u.Orders.First().UserId,
                o => o.Amount > 1000)  // 只映射金额大于1000的订单
            .ToList();
    }

    // 映射并排序
    public List<User> MapperWithOrder()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Orders,
                u => u.Orders.First().UserId,
                null,
                o => o.OrderBy(x => x.CreateTime, OrderByType.Desc))
            .ToList();
    }
}

7.2.3 Includes加载

使用Includes预加载关联数据:

public class IncludesExamples
{
    private readonly SqlSugarClient _db;

    public IncludesExamples(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础Includes
    public List<User> BasicIncludes()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Department)
            .ToList();
    }

    // 多级Includes
    public List<User> MultiLevelIncludes()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Department)
            .Includes(u => u.Orders, o => o.Details)  // 包含订单及订单详情
            .ToList();
    }

    // 条件Includes
    public List<User> IncludesWithCondition()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Orders.Where(o => o.Amount > 1000).ToList())
            .ToList();
    }

    // Includes多个导航属性
    public List<User> IncludesMultiple()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Department)
            .Includes(u => u.Role)
            .Includes(u => u.Profile)
            .Includes(u => u.Orders)
            .ToList();
    }

    // 深层级Includes
    public List<Department> DeepIncludes()
    {
        return _db.Queryable<Department>()
            .Includes(d => d.Users, u => u.Orders, o => o.Details)
            .ToList();
    }
}

7.3 一对一关系

7.3.1 配置一对一

配置一对一关系:

public class OneToOneConfiguration
{
    // 主表 - User
    public class User
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }

        // 一对一导航属性
        [Navigate(NavigateType.OneToOne, nameof(Id))]
        public UserProfile Profile { get; set; }
    }

    // 从表 - UserProfile
    public class UserProfile
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int UserId { get; set; }
        public string Avatar { get; set; }
        public string Bio { get; set; }
        public DateTime Birthday { get; set; }

        // 反向导航属性
        [Navigate(NavigateType.OneToOne, nameof(UserId))]
        public User User { get; set; }
    }
}

7.3.2 查询一对一

查询一对一关系数据:

public class OneToOneQuery
{
    private readonly SqlSugarClient _db;

    public OneToOneQuery(SqlSugarClient db)
    {
        _db = db;
    }

    // 使用Join查询
    public List<object> QueryWithJoin()
    {
        return _db.Queryable<User>()
            .LeftJoin<UserProfile>((u, p) => u.Id == p.UserId)
            .Select((u, p) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                Avatar = p.Avatar,
                Bio = p.Bio
            })
            .ToList<object>();
    }

    // 使用Mapper查询
    public List<User> QueryWithMapper()
    {
        return _db.Queryable<User>()
            .Mapper(u => u.Profile, u => u.Id)
            .ToList();
    }

    // 使用Includes查询
    public List<User> QueryWithIncludes()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Profile)
            .ToList();
    }

    // 反向查询
    public List<UserProfile> QueryProfileWithUser()
    {
        return _db.Queryable<UserProfile>()
            .Includes(p => p.User)
            .ToList();
    }
}

7.3.3 级联操作

一对一关系的级联操作:

public class OneToOneCascade
{
    private readonly SqlSugarClient _db;

    public OneToOneCascade(SqlSugarClient db)
    {
        _db = db;
    }

    // 级联插入
    public bool InsertWithProfile(User user, UserProfile profile)
    {
        try
        {
            _db.Ado.BeginTran();

            // 插入用户
            var userId = _db.Insertable(user).ExecuteReturnIdentity();

            // 插入用户资料
            profile.UserId = userId;
            _db.Insertable(profile).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    // 级联更新
    public bool UpdateWithProfile(User user, UserProfile profile)
    {
        try
        {
            _db.Ado.BeginTran();

            _db.Updateable(user).ExecuteCommand();
            _db.Updateable(profile).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    // 级联删除
    public bool DeleteWithProfile(int userId)
    {
        try
        {
            _db.Ado.BeginTran();

            _db.Deleteable<UserProfile>().Where(p => p.UserId == userId).ExecuteCommand();
            _db.Deleteable<User>().In(userId).ExecuteCommand();

            _db.Ado.CommitTran();
            return true;
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }
}

7.4 一对多关系

7.4.1 配置一对多

配置一对多关系:

public class OneToManyConfiguration
{
    // 主表 - Department
    public class Department
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }

        // 一对多导航属性
        [Navigate(NavigateType.OneToMany, nameof(User.DepartmentId))]
        public List<User> Users { get; set; }
    }

    // 从表 - User
    public class User
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public int DepartmentId { get; set; }

        // 多对一导航属性
        [Navigate(NavigateType.ManyToOne, nameof(DepartmentId))]
        public Department Department { get; set; }
    }
}

7.4.2 查询一对多

查询一对多关系数据:

public class OneToManyQuery
{
    private readonly SqlSugarClient _db;

    public OneToManyQuery(SqlSugarClient db)
    {
        _db = db;
    }

    // 使用Mapper查询
    public List<Department> QueryWithMapper()
    {
        return _db.Queryable<Department>()
            .Mapper(d => d.Users, d => d.Users.First().DepartmentId)
            .ToList();
    }

    // 使用Includes查询
    public List<Department> QueryWithIncludes()
    {
        return _db.Queryable<Department>()
            .Includes(d => d.Users)
            .ToList();
    }

    // 条件过滤子集合
    public List<Department> QueryWithCondition()
    {
        return _db.Queryable<Department>()
            .Mapper(d => d.Users,
                d => d.Users.First().DepartmentId,
                u => u.Age >= 18)  // 只加载成年用户
            .ToList();
    }

    // 子集合排序
    public List<Department> QueryWithOrder()
    {
        return _db.Queryable<Department>()
            .Mapper(d => d.Users,
                d => d.Users.First().DepartmentId,
                null,
                u => u.OrderBy(x => x.Name))
            .ToList();
    }

    // 反向查询(多对一)
    public List<User> QueryUserWithDepartment()
    {
        return _db.Queryable<User>()
            .Includes(u => u.Department)
            .ToList();
    }
}

7.4.3 分页加载

对一对多关系进行分页加载:

public class OneToManyPagination
{
    private readonly SqlSugarClient _db;

    public OneToManyPagination(SqlSugarClient db)
    {
        _db = db;
    }

    // 子集合分页
    public List<Department> QueryWithPagination(int pageIndex, int pageSize)
    {
        var departments = _db.Queryable<Department>().ToList();

        foreach (var dept in departments)
        {
            dept.Users = _db.Queryable<User>()
                .Where(u => u.DepartmentId == dept.Id)
                .ToPageList(pageIndex, pageSize);
        }

        return departments;
    }

    // 优化版本 - 减少查询次数
    public List<Department> QueryWithOptimizedPagination(int pageIndex, int pageSize)
    {
        // 先查询部门
        var departments = _db.Queryable<Department>().ToList();
        var deptIds = departments.Select(d => d.Id).ToList();

        // 批量查询所有用户
        var allUsers = _db.Queryable<User>()
            .Where(u => deptIds.Contains(u.DepartmentId))
            .ToList();

        // 在内存中分配用户到部门
        foreach (var dept in departments)
        {
            dept.Users = allUsers
                .Where(u => u.DepartmentId == dept.Id)
                .Skip((pageIndex - 1) * pageSize)
                .Take(pageSize)
                .ToList();
        }

        return departments;
    }
}

7.5 多对多关系

7.5.1 配置多对多

配置多对多关系:

public class ManyToManyConfiguration
{
    // 学生表
    public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }

        // 多对多导航属性
        [Navigate(typeof(StudentCourse), nameof(StudentCourse.StudentId), nameof(StudentCourse.CourseId))]
        public List<Course> Courses { get; set; }
    }

    // 课程表
    public class Course
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string Name { get; set; }
        public int Credits { get; set; }

        // 多对多导航属性
        [Navigate(typeof(StudentCourse), nameof(StudentCourse.CourseId), nameof(StudentCourse.StudentId))]
        public List<Student> Students { get; set; }
    }

    // 中间表
    public class StudentCourse
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int StudentId { get; set; }

        [SugarColumn(IsPrimaryKey = true)]
        public int CourseId { get; set; }

        public DateTime EnrollDate { get; set; }
        public decimal? Score { get; set; }
    }
}

7.5.2 查询多对多

查询多对多关系数据:

public class ManyToManyQuery
{
    private readonly SqlSugarClient _db;

    public ManyToManyQuery(SqlSugarClient db)
    {
        _db = db;
    }

    // 使用Mapper查询
    public List<Student> QueryStudentsWithCourses()
    {
        return _db.Queryable<Student>()
            .Mapper(s => s.Courses,
                s => s.Courses.First().Id,
                c => c.Id)
            .ToList();
    }

    // 使用Includes查询
    public List<Student> QueryWithIncludes()
    {
        return _db.Queryable<Student>()
            .Includes(s => s.Courses)
            .ToList();
    }

    // 反向查询
    public List<Course> QueryCoursesWithStudents()
    {
        return _db.Queryable<Course>()
            .Includes(c => c.Students)
            .ToList();
    }

    // 使用Join查询
    public List<object> QueryWithJoin()
    {
        return _db.Queryable<Student, StudentCourse, Course>(
                (s, sc, c) => new JoinQueryInfos(
                    JoinType.Inner, s.Id == sc.StudentId,
                    JoinType.Inner, sc.CourseId == c.Id
                ))
            .Select((s, sc, c) => new
            {
                StudentId = s.Id,
                StudentName = s.Name,
                CourseId = c.Id,
                CourseName = c.Name,
                EnrollDate = sc.EnrollDate,
                Score = sc.Score
            })
            .ToList<object>();
    }

    // 查询学生的课程数量
    public List<object> CountStudentCourses()
    {
        return _db.Queryable<Student, StudentCourse>(
                (s, sc) => s.Id == sc.StudentId)
            .GroupBy((s, sc) => new { s.Id, s.Name })
            .Select((s, sc) => new
            {
                StudentId = s.Id,
                StudentName = s.Name,
                CourseCount = SqlFunc.AggregateCount(sc.CourseId)
            })
            .ToList<object>();
    }
}

7.5.3 中间表操作

操作多对多关系的中间表:

public class ManyToManyOperations
{
    private readonly SqlSugarClient _db;

    public ManyToManyOperations(SqlSugarClient db)
    {
        _db = db;
    }

    // 添加关系
    public bool AddStudentCourse(int studentId, int courseId, DateTime enrollDate)
    {
        var sc = new StudentCourse
        {
            StudentId = studentId,
            CourseId = courseId,
            EnrollDate = enrollDate
        };

        return _db.Insertable(sc).ExecuteCommand() > 0;
    }

    // 批量添加关系
    public bool AddStudentCourses(int studentId, List<int> courseIds)
    {
        var list = courseIds.Select(cid => new StudentCourse
        {
            StudentId = studentId,
            CourseId = cid,
            EnrollDate = DateTime.Now
        }).ToList();

        return _db.Insertable(list).ExecuteCommand() > 0;
    }

    // 删除关系
    public bool RemoveStudentCourse(int studentId, int courseId)
    {
        return _db.Deleteable<StudentCourse>()
            .Where(sc => sc.StudentId == studentId && sc.CourseId == courseId)
            .ExecuteCommand() > 0;
    }

    // 更新中间表数据
    public bool UpdateScore(int studentId, int courseId, decimal score)
    {
        return _db.Updateable<StudentCourse>()
            .SetColumns(sc => sc.Score == score)
            .Where(sc => sc.StudentId == studentId && sc.CourseId == courseId)
            .ExecuteCommand() > 0;
    }

    // 查询学生的所有课程关系
    public List<StudentCourse> GetStudentCourses(int studentId)
    {
        return _db.Queryable<StudentCourse>()
            .Where(sc => sc.StudentId == studentId)
            .ToList();
    }
}

7.6 复杂多表查询

7.6.1 三表联查

进行三表联合查询:

public class ThreeTableJoin
{
    private readonly SqlSugarClient _db;

    public ThreeTableJoin(SqlSugarClient db)
    {
        _db = db;
    }

    // 基础三表联查
    public List<OrderFullDto> BasicThreeTableJoin()
    {
        return _db.Queryable<Order, User, Product>(
                (o, u, p) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, o.ProductId == p.Id
                ))
            .Select((o, u, p) => new OrderFullDto
            {
                OrderId = o.Id,
                OrderNo = o.OrderNo,
                OrderTime = o.CreateTime,
                UserName = u.Name,
                UserEmail = u.Email,
                ProductName = p.Name,
                ProductPrice = p.Price,
                Quantity = o.Quantity,
                TotalAmount = o.Quantity * p.Price
            })
            .ToList();
    }

    // 三表联查带条件
    public List<OrderFullDto> ThreeTableJoinWithCondition(DateTime startDate, DateTime endDate)
    {
        return _db.Queryable<Order, User, Product>(
                (o, u, p) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, o.ProductId == p.Id
                ))
            .Where((o, u, p) => o.CreateTime >= startDate && o.CreateTime < endDate)
            .Where((o, u, p) => o.Status == 1)
            .Select((o, u, p) => new OrderFullDto
            {
                OrderId = o.Id,
                OrderNo = o.OrderNo,
                UserName = u.Name,
                ProductName = p.Name,
                TotalAmount = o.Quantity * p.Price
            })
            .ToList();
    }

    // 三表联查分组统计
    public List<object> ThreeTableJoinWithGroup()
    {
        return _db.Queryable<Order, User, Product>(
                (o, u, p) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, o.ProductId == p.Id
                ))
            .GroupBy((o, u, p) => new { u.Id, u.Name })
            .Select((o, u, p) => new
            {
                UserId = u.Id,
                UserName = u.Name,
                OrderCount = SqlFunc.AggregateCount(o.Id),
                TotalAmount = SqlFunc.AggregateSum(o.Quantity * p.Price)
            })
            .ToList<object>();
    }
}

public class OrderFullDto
{
    public int OrderId { get; set; }
    public string OrderNo { get; set; }
    public DateTime OrderTime { get; set; }
    public string UserName { get; set; }
    public string UserEmail { get; set; }
    public string ProductName { get; set; }
    public decimal ProductPrice { get; set; }
    public int Quantity { get; set; }
    public decimal TotalAmount { get; set; }
}

7.6.2 四表及以上联查

进行更复杂的多表联查:

public class MultiTableJoin
{
    private readonly SqlSugarClient _db;

    public MultiTableJoin(SqlSugarClient db)
    {
        _db = db;
    }

    // 四表联查
    public List<object> FourTableJoin()
    {
        return _db.Queryable<Order, User, Product, Category>(
                (o, u, p, c) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, o.ProductId == p.Id,
                    JoinType.Left, p.CategoryId == c.Id
                ))
            .Select((o, u, p, c) => new
            {
                OrderNo = o.OrderNo,
                UserName = u.Name,
                ProductName = p.Name,
                CategoryName = c.Name,
                TotalAmount = o.Quantity * p.Price
            })
            .ToList<object>();
    }

    // 五表联查
    public List<object> FiveTableJoin()
    {
        return _db.Queryable<Order, User, Department, Product, Category>(
                (o, u, d, p, c) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, u.DepartmentId == d.Id,
                    JoinType.Left, o.ProductId == p.Id,
                    JoinType.Left, p.CategoryId == c.Id
                ))
            .Select((o, u, d, p, c) => new
            {
                OrderNo = o.OrderNo,
                UserName = u.Name,
                DepartmentName = d.Name,
                ProductName = p.Name,
                CategoryName = c.Name,
                TotalAmount = o.Quantity * p.Price
            })
            .ToList<object>();
    }

    // 复杂多表联查with分组
    public List<object> ComplexMultiTableJoin()
    {
        return _db.Queryable<Order, User, Department, Product>(
                (o, u, d, p) => new JoinQueryInfos(
                    JoinType.Left, o.UserId == u.Id,
                    JoinType.Left, u.DepartmentId == d.Id,
                    JoinType.Left, o.ProductId == p.Id
                ))
            .GroupBy((o, u, d, p) => new { d.Id, d.Name, Year = o.CreateTime.Year, Month = o.CreateTime.Month })
            .Select((o, u, d, p) => new
            {
                DepartmentId = d.Id,
                DepartmentName = d.Name,
                Year = o.CreateTime.Year,
                Month = o.CreateTime.Month,
                OrderCount = SqlFunc.AggregateCount(o.Id),
                TotalAmount = SqlFunc.AggregateSum(o.Quantity * p.Price),
                UserCount = SqlFunc.AggregateCountDistinct(u.Id)
            })
            .ToList<object>();
    }
}

7.6.3 自连接查询

查询同一张表的层级关系:

public class SelfJoinQuery
{
    private readonly SqlSugarClient _db;

    public SelfJoinQuery(SqlSugarClient db)
    {
        _db = db;
    }

    // 查询部门及其父部门
    public List<object> QueryDepartmentWithParent()
    {
        return _db.Queryable<Department, Department>(
                (d1, d2) => new JoinQueryInfos(
                    JoinType.Left, d1.ParentId == d2.Id
                ))
            .Select((d1, d2) => new
            {
                DeptId = d1.Id,
                DeptName = d1.Name,
                ParentId = d2.Id,
                ParentName = d2.Name
            })
            .ToList<object>();
    }

    // 查询所有子部门
    public List<Department> QueryChildDepartments(int parentId)
    {
        return _db.Queryable<Department>()
            .Where(d => d.ParentId == parentId)
            .ToList();
    }

    // 递归查询所有子部门
    public List<Department> QueryAllChildDepartments(int parentId)
    {
        var result = new List<Department>();
        var queue = new Queue<int>();
        queue.Enqueue(parentId);

        while (queue.Count > 0)
        {
            var currentId = queue.Dequeue();
            var children = _db.Queryable<Department>()
                .Where(d => d.ParentId == currentId)
                .ToList();

            result.AddRange(children);
            foreach (var child in children)
            {
                queue.Enqueue(child.Id);
            }
        }

        return result;
    }
}

public class Department
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
}

7.7 性能优化

public class JoinPerformanceOptimization
{
    private readonly SqlSugarClient _db;

    public JoinPerformanceOptimization(SqlSugarClient db)
    {
        _db = db;
    }

    // 1. 避免N+1查询
    public List<Department> AvoidNPlusOne()
    {
        // 错误方式 - N+1查询
        // var depts = _db.Queryable<Department>().ToList();
        // foreach(var dept in depts) {
        //     dept.Users = _db.Queryable<User>().Where(u => u.DepartmentId == dept.Id).ToList();
        // }

        // 正确方式 - 使用Mapper或Includes
        return _db.Queryable<Department>()
            .Mapper(d => d.Users, d => d.Users.First().DepartmentId)
            .ToList();
    }

    // 2. 只查询需要的字段
    public List<object> SelectOnlyNeededFields()
    {
        return _db.Queryable<User, Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new
            {
                u.Id,
                u.Name,
                DepartmentName = d.Name
            })
            .ToList<object>();
    }

    // 3. 使用分页减少数据量
    public PagedResult<object> UsePagedQuery(int pageIndex, int pageSize)
    {
        int totalCount = 0;
        var data = _db.Queryable<User, Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new
            {
                u.Id,
                u.Name,
                DepartmentName = d.Name
            })
            .ToPageList(pageIndex, pageSize, ref totalCount);

        return new PagedResult<object>
        {
            Items = data,
            TotalCount = totalCount,
            PageIndex = pageIndex,
            PageSize = pageSize
        };
    }

    // 4. 使用索引优化Join
    public List<object> UseIndexedJoin()
    {
        // 确保连接字段有索引
        // CREATE INDEX idx_user_deptid ON User(DepartmentId);
        // CREATE INDEX idx_order_userid ON Order(UserId);

        return _db.Queryable<User, Department, Order>(
                (u, d, o) => new JoinQueryInfos(
                    JoinType.Left, u.DepartmentId == d.Id,
                    JoinType.Left, o.UserId == u.Id
                ))
            .Select((u, d, o) => new
            {
                u.Name,
                DepartmentName = d.Name,
                OrderCount = SqlFunc.AggregateCount(o.Id)
            })
            .ToList<object>();
    }

    // 5. 批量加载导航属性
    public List<Department> BatchLoadNavigations()
    {
        var depts = _db.Queryable<Department>().ToList();
        var deptIds = depts.Select(d => d.Id).ToList();

        // 批量查询所有用户
        var users = _db.Queryable<User>()
            .Where(u => deptIds.Contains(u.DepartmentId))
            .ToList();

        // 在内存中分配
        foreach (var dept in depts)
        {
            dept.Users = users.Where(u => u.DepartmentId == dept.Id).ToList();
        }

        return depts;
    }
}

7.8 最佳实践

public class MultiTableBestPractices
{
    private readonly SqlSugarClient _db;

    public MultiTableBestPractices(SqlSugarClient db)
    {
        _db = db;
    }

    // 1. 合理使用导航属性
    public void UseNavigationWisely()
    {
        // 简单查询使用Join
        var simpleResult = _db.Queryable<User, Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new { u.Name, DeptName = d.Name })
            .ToList();

        // 需要完整对象使用导航属性
        var fullObjects = _db.Queryable<User>()
            .Includes(u => u.Department)
            .ToList();
    }

    // 2. 控制导航深度
    public List<Department> ControlNavigationDepth()
    {
        // 避免过深的导航层级
        return _db.Queryable<Department>()
            .Includes(d => d.Users)  // 只加载一层
            .ToList();
    }

    // 3. 使用DTO减少数据传输
    public List<UserDeptDto> UseDto()
    {
        return _db.Queryable<User, Department>((u, d) => u.DepartmentId == d.Id)
            .Select((u, d) => new UserDeptDto
            {
                UserId = u.Id,
                UserName = u.Name,
                DepartmentName = d.Name
            })
            .ToList();
    }

    // 4. 合理使用缓存
    public List<Department> UseCaching()
    {
        var cacheKey = "departments_with_users";
        
        // 检查缓存
        var cached = GetFromCache<List<Department>>(cacheKey);
        if (cached != null) return cached;

        // 查询数据库
        var data = _db.Queryable<Department>()
            .Mapper(d => d.Users, d => d.Users.First().DepartmentId)
            .ToList();

        // 写入缓存
        SetCache(cacheKey, data, TimeSpan.FromMinutes(10));

        return data;
    }

    // 5. 异步查询提高性能
    public async Task<List<Department>> UseAsyncQuery()
    {
        return await _db.Queryable<Department>()
            .Mapper(d => d.Users, d => d.Users.First().DepartmentId)
            .ToListAsync();
    }

    private T GetFromCache<T>(string key) where T : class
    {
        // 实现缓存获取逻辑
        return null;
    }

    private void SetCache<T>(string key, T value, TimeSpan expiration)
    {
        // 实现缓存设置逻辑
    }
}

本章小结

本章全面介绍了SqlSugar的多表联查与导航属性:

联表查询: 掌握了Inner Join、Left Join、Right Join和Full Join的使用
导航属性: 学习了如何定义和使用导航属性简化多表查询
Mapper和Includes: 了解了两种加载关联数据的方法
一对一关系: 掌握了一对一关系的配置、查询和级联操作
一对多关系: 学习了一对多关系的处理和分页加载
多对多关系: 了解了多对多关系和中间表的操作
复杂多表查询: 掌握了三表、四表及以上的联表查询
性能优化: 学习了避免N+1查询、使用索引等优化技巧
最佳实践: 总结了多表查询的设计模式和注意事项

通过本章的学习,你应该能够熟练处理各种复杂的多表查询场景,并能够根据实际需求选择合适的查询方式。

下一章: 第八章-事务处理

znlgis 博客