How to leave to join two Datables?

Question

Here is my script

I have the first Datable: 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();

Second table: TableB

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();

Demand:

Attach two data types and show the result, if Serials is equal to both tables, then Status is HOT else NORMAL.

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

Is it possible? Thanks at Regrads?

Answer 1

This is certainly possible, although I used Intersect, Except, and Union instead of the left join. I hope this is acceptable:

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 Test:

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();

Return:

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