LINQ to OBJECT语句之Join操作符的使用

实例：将员工信息与部门信息关联。

要求：1、join实现员工信息与部门信息关联。

            2、orderby实现以员工入职时间倒序排序。

            3、let投影出员工详细地址。

主代码：

/// <summary>
/// 获取员工并关联部门名称
/// </summary>
/// <returns></returns>
public static List<Emplayee> JoinEmplayeeList()
{
    List<Emplayee> resultList = new List<Emplayee>();
    List<Emplayee> emplayeeList = GetEmplayeeList();        //获取员工信息列表
    List<Department> departmentList = GetDepartmentList();  //获取部门信息列表
    //将员工信息与部门信息关联
    resultList = (from emplayee in emplayeeList
                  join department in departmentList on emplayee.DepartmentId equals department.DepartmentId into t1
                  from depInfo in t1.DefaultIfEmpty()
                  orderby emplayee.AddDate descending
                  let z = emplayee.Country + emplayee.City
                  select new Emplayee()
                  {
                      Name = emplayee.Name,
                      AddDate = emplayee.AddDate,
                      City = emplayee.City,
                      Country = z,
                      DepartmentId = emplayee.DepartmentId,
                      DepartmentName = depInfo == null ? "" : depInfo.DepartmentName  //若关联到部门信息，则返回空字符串
                  }).ToList();
    
    return resultList;
}

其他代码：

/// <summary>
/// 获取员工信息列表
/// </summary>
/// <returns></returns>
public static List<Emplayee> GetEmplayeeList()
{
    List<Emplayee> emplayeeList = new List<Emplayee>();
    Emplayee emplayee1 = new Emplayee() { Name = "张三", AddDate = new DateTime(2011, 2, 17), Country= "中国", City="深圳", DepartmentId = 1 };
    Emplayee emplayee2 = new Emplayee() { Name = "李四", AddDate = new DateTime(2012, 10, 23), Country = "美国", City = "洛杉矶", DepartmentId = 2 };
    Emplayee emplayee3 = new Emplayee() { Name = "王五", AddDate = new DateTime(2012, 8, 6), Country = "法国", City = "巴黎", DepartmentId = 99 };
    emplayeeList.Add(emplayee1);
    emplayeeList.Add(emplayee2);
    emplayeeList.Add(emplayee3);
    return emplayeeList;
}

/// <summary>
/// 获取部门信息列表
/// </summary>
/// <returns></returns>
public static List<Department> GetDepartmentList()
{
    List<Department> departmentList = new List<Department>();
    Department department1 = new Department() { DepartmentId = 1, DepartmentName = "研发部" };
    Department department2 = new Department() { DepartmentId = 2, DepartmentName = "人事部" };
    Department department3 = new Department() { DepartmentId = 3, DepartmentName = "财务部" };
    departmentList.Add(department1);
    departmentList.Add(department2);
    departmentList.Add(department3);
    return departmentList;
}

实体类：

/// <summary>
/// 员工信息类
/// </summary>
public class Emplayee 
{
    /// <summary>
    /// 姓名
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// 入职时间
    /// </summary>
    public DateTime AddDate { get; set; }

    /// <summary>
    /// 国家
    /// </summary>
    public string Country { get; set; }

    /// <summary>
    /// 城市
    /// </summary>
    public string City { get; set; }

    /// <summary>
    /// 部门ID
    /// </summary>
    public int DepartmentId { get; set; }

    /// <summary>
    /// 部门名称
    /// </summary>
    public string DepartmentName { get; set; }
}

/// <summary>
/// 部门信息类
/// </summary>
public class Department
{
    /// <summary>
    /// 部门ID
    /// </summary>
    public int DepartmentId { get; set; }

    /// <summary>
    /// 部门名称
    /// </summary>
    public string DepartmentName { get; set; }
}