内连接返回两个表中匹配的记录:
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; }
}
左连接返回左表所有记录及右表匹配记录:
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>();
}
}
右连接返回右表所有记录及左表匹配记录:
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();
}
}
全连接返回两个表的所有记录:
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>();
}
}
定义实体类之间的导航关系:
// 用户实体
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; }
}
使用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();
}
}
使用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();
}
}
配置一对一关系:
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; }
}
}
查询一对一关系数据:
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();
}
}
一对一关系的级联操作:
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;
}
}
}
配置一对多关系:
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; }
}
}
查询一对多关系数据:
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();
}
}
对一对多关系进行分页加载:
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;
}
}
配置多对多关系:
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; }
}
}
查询多对多关系数据:
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>();
}
}
操作多对多关系的中间表:
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();
}
}
进行三表联合查询:
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; }
}
进行更复杂的多表联查:
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>();
}
}
查询同一张表的层级关系:
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; }
}
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;
}
}
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的多表联查与导航属性:
通过本章的学习,你应该能够熟练处理各种复杂的多表查询场景,并能够根据实际需求选择合适的查询方式。
下一章: 第08章-事务处理
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。