c# - 如何左连接两个数据表？

Question

这是我的场景

我有第一个数据表:TableA

SerialNumber   PartNumber
    001               A
    002               B

        var TableA = (from p in ddata.AsEnumerable()
                 join q in Select.AsEnumerable()
                   on p.Field<string>("SerialNumber") equals q.Field<string>("SerialNumber") 
                  select new
                   {
                       SerialNUmber = q["SerialNumber"],
                       PartNumber = q["PartNumber"],
                        UnitStatus = "HOT"
               }).ToList();

第二个表:表B

SerialNumber  PartNumber
001             A
002             B
003             C
004             D

   var merger = (from w in ddata.AsEnumerable()
                              select new
                              {
                                  SerialNUmber = w["SerialNumber"],
                                  PartNumber = w["PartNumber"],
                                  UnitStatus = "COLD"
                              }).ToList();

要求:

如果 Serials 等于两个表，则加入两个数据表并显示结果情况，则状态为 HOT，否则为 NORMAL。

SerialNumber  PartNumber    Status
001             A           HOT
002             B           HOT
003             C           NORMAL
004             D           NORMAL

这可能吗？感谢 Regrads？

Answer 1

尽管我使用“Intersect”、“Except”和“Union”而不是左连接，但它肯定是可能的。我希望这是可以接受的:

var both = list1.Intersect(list2);
var ret = both
              .Select(x => new { SerialNumber = x.SerialNumber, PartNumber = x.PartNumber, Status = "HOT" })
              .Union(
                  list1.Union(list2).Distinct()
                      .Except(both)
                      .Select(x => new { SerialNumber = x.SerialNumber, PartNumber = x.PartNumber, Status = "NORMAL" })
              )

LinqPad 测试:

var list1 = new [] {
new { SerialNumber = 1, PartNumber = "A" },
new { SerialNumber = 2, PartNumber = "B" },
};

var list2 = new [] {
new { SerialNumber = 1, PartNumber = "A" },
new { SerialNumber = 2, PartNumber = "B" },
new { SerialNumber = 3, PartNumber = "C" },
new { SerialNumber = 4, PartNumber = "D" },
};

var both = list1.Intersect(list2);
both
    .Select(x => new { SerialNumber = x.SerialNumber, PartNumber = x.PartNumber, Status = "HOT" })
    .Union(
        list1.Union(list2).Distinct()
            .Except(both)
            .Select(x => new { SerialNumber = x.SerialNumber, PartNumber = x.PartNumber, Status = "NORMAL" })
    )
.Dump();

返回:

SerialNumber PartNumber Status 
1            A          HOT
2            B          HOT
3            C          NORMAL
4            D          NORMAL