直接上代码
//一级关联查询,直接通过字表ID查询字表实体信息
var user = Db.Queryable<SysUser>().Where(it => it.UserCode == userCode).Mapper(it=>it.employee, it => it.EmployeeId).ToList();
//多级关联查询一,USER-->EMPLOYEE-->DEPARTMENT user.employee.department
var user1 = Db.Queryable<SysUser>().Where(it => it.UserCode == userCode).Mapper((it, cache) =>
{
it.employee = cache.GetListByPrimaryKeys<Employee>(ea => ea.EmployeeId).FirstOrDefault<Employee>();
if (it.employee != null)
it.employee.department = cache.GetListByPrimaryKeys<Department>(ed => it.employee.DepartmentId).FirstOrDefault<Department>();
}).ToList();
//多级关联查询二,USER-->EMPLOYEE-->DEPARTMENT user.employee.department
var list = Db.Queryable<SysUser, Employee, Department>((U, E, D) => new object[] {
JoinType.Left,U.EmployeeId==E.EmployeeId,
JoinType.Left,E.DepartmentId == D.DepartmentId}
).Where((U, E, D) => U.UserCode == userCode)
.Select((U, E, D) => new { U = U, E = E, D = D }).ToList();
var user2 = new SysUser();
user2.employee = new Employee();
user2.employee.department = new Department();
user2 = list[0].U;
user2.employee = list[0].E;
user2.employee.department = list[0].D;
表结构
/// <summary>
/// 用户表
/// </summary>
public class SysUser
{
private System.Guid _UserId;
/// <summary>
///
/// </summary>
[SugarColumn(IsPrimaryKey = true, IsIdentity = false)]
public System.Guid UserId { get { return this._UserId; } set { this._UserId = value; } }
private System.String _UserName;
/// <summary>
///
/// </summary>
public System.String UserName { get { return this._UserName; } set { this._UserName = value?.Trim(); } }
private System.String _UserCode;
/// <summary>
///
/// </summary>
public System.String UserCode { get { return this._UserCode; } set { this._UserCode = value?.Trim(); } }
private System.Guid _EmployeeId;
/// <summary>
///
/// </summary>
public System.Guid EmployeeId { get { return this._EmployeeId; } set { this._EmployeeId = value; } }
private System.String _PassWord;
/// <summary>
///
/// </summary>
public System.String PassWord { get { return this._PassWord; } set { this._PassWord = value?.Trim(); } }
private Employee _employee;
[SugarColumn(IsIgnore = true)]
public Employee employee { get { return this._employee; } set { this._employee = value; } }
}
/// <summary>
/// 部门表
/// </summary>
public class Department
{
private System.Guid _DepartmentId;
/// <summary>
/// 部门ID
/// </summary>
[SugarColumn(IsPrimaryKey = true, IsIdentity = false)]
public System.Guid DepartmentId { get { return this._DepartmentId; } set { this._DepartmentId = value; } }
private System.String _RepealRemark;
/// <summary>
/// 撤消原因
/// </summary>
public System.String RepealRemark { get { return this._RepealRemark; } set { this._RepealRemark = value?.Trim(); } }
private System.Guid _ParentId;
/// <summary>
/// 上级部门
/// </summary>
public System.Guid ParentId { get { return this._ParentId; } set { this._ParentId = value; } }
private System.String _Code;
/// <summary>
/// 部门编码
/// </summary>
public System.String Code { get { return this._Code; } set { this._Code = value?.Trim(); } }
private System.String _Name;
/// <summary>
/// 部门名称
/// </summary>
public System.String Name { get { return this._Name; } set { this._Name = value?.Trim(); } }
}
/// <summary>
/// 员工
/// </summary>
public class Employee
{
private System.Guid? _InnerEmployee;
/// <summary>
/// 内部介绍人
/// </summary>
public System.Guid? InnerEmployee { get { return this._InnerEmployee; } set { this._InnerEmployee = value; } }
private System.DateTime _CertificateLimit;
/// <summary>
/// 证件有效期
/// </summary>
public System.DateTime CertificateLimit { get { return this._CertificateLimit; } set { this._CertificateLimit = value; } }
private System.DateTime _JobDate;
/// <summary>
/// 入职日期(首次入职)
/// </summary>
public System.DateTime JobDate { get { return this._JobDate; } set { this._JobDate = value; } }
private System.Guid _EmployeeId;
/// <summary>
/// 员工ID
/// </summary>
[SugarColumn(IsPrimaryKey = true, IsIdentity = false)]
public System.Guid EmployeeId { get { return this._EmployeeId; } set { this._EmployeeId = value; } }
private System.String _CnName;
/// <summary>
/// 中文名称
/// </summary>
public System.String CnName { get { return this._CnName; } set { this._CnName = value?.Trim(); } }
private System.String _IDCardNo;
/// <summary>
/// 证件号码
/// </summary>
public System.String IDCardNo { get { return this._IDCardNo; } set { this._IDCardNo = value?.Trim(); } }
private Department _department;
[SugarColumn(IsIgnore = true)]
public Department department { get { return this._department; } set { this._department = value; } }
}