SqlSugar多級查詢

 直接上代碼

//一級關聯查詢，直接通過字表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; } }
}