Linq GroupJoin(一)

我们知道Linq group join 实现 SQL left join ．隨意兩個測試用表：

1. 員工工資表  
 EmpId varchar(50), //員工 ID
 DeptId int,//部門ID
 Salary decimal(18,2) //工資

2. 部門表
DeptId int, //部門
DeptName nvarchar(100)//部門名稱

  /// <summary>
    /// Linq GroupJoin  類似于  Sql Left outer join 
    /// </summary>        
    private void btnGroupJoin_Click(object sender, EventArgs e)
    {
        DataSet ds = sqlherpler.GetData("SELECT *FROM DepartMent;SELECT * FROM Employee1", null);
        DataTable dtEmployee = ds.Tables[1];//員工表
        DataTable dtDept = ds.Tables[0]; //部門名稱表

        // Linq 表達式形式。 連接得到 部門名稱
        var query1 = from item1 in dtEmployee.AsEnumerable() // 員工表作為左表
                     join item2 in dtDept.AsEnumerable()// 部門表作為右表
                     on item1.Field<int>("deptId") equals item2.Field<int>("deptId") into g //部門ID相等
                     select new { EmpId = item1.Field<string>("empId"),
                                   DeptName =   g.Select (o=>o.Field <string>("deptName")).FirstOrDefault() ?? "Unknow DepartMent",// g.Any() ? g.First().Field <string>("deptName") : "Unknow DepartMent", //得到 部門名稱                                
                                 Salary = item1.Field<decimal>("salary")
                  };
        dgv1.DataSource = query1.ToList();

        //Lambda 函數形式。連接得到每個部門的員工人數，平均工資
        var query2 = dtDept.AsEnumerable().GroupJoin(dtEmployee.AsEnumerable(),
            item1 => item1.Field<int>("deptId"),
            item2 => item2.Field<int>("deptId"),
            (item1, item2) => new
            {   DeptId = item1.Field<int>("deptId"),
                DeptName = item1.Field<string>("deptName"),
                EmployeeNum = item2.Count()   , //部門員工人數
                SalaryAgv =  item2.Any() ? item2.Average(o=>o.Field<decimal>("salary")).ToString ("0.00") : "0.00" //部門員工平均工資
            }) ;
        dgv2.DataSource = query2.ToList();
    }

另，如果是多个字段连接：例

var queryNotExistInLoc = from d in dtDownLoad.AsEnumerable()
                                     join l in dtLoc.AsEnumerable()
                                     on new { k1 = Convert.ToString(d["employmentCode"]), k2 = DateTime.Parse(Convert.ToString(d["time"])) }
                                     equals new { k1 = Convert.ToString(l["employmentCode"]), k2 = DateTime.Parse(Convert.ToString(l["ftime"])) }
                                     into ljoin                                    
                                     select new { d, itemCardType = Convert.ToString (ljoin.Select(o => o["cardType"]).FirstOrDefault() ?? "") };

或

 var query1 = dtDownLoad.AsEnumerable().GroupJoin(dtLoc.AsEnumerable(),
                 item1 => new { k1 = Convert.ToString(item1["employmentCode"]), k2 = DateTime.Parse(Convert.ToString(item1["time"])) },
                 item2 => new { k1 = Convert.ToString(item2["employmentCode"]), k2 = DateTime.Parse(Convert.ToString(item2["ftime"])) },
                 (item1, item2) => new { item1, itemCardType = Convert.ToString(item2.Select(o => o["cardType"]).FirstOrDefault() ?? "") });
            query1.ToList().ForEach(o =>
        Console.WriteLine(o.itemCardType)
          );