How to left join two Datables?

Here i my Scenario

I Have First Datable :T开发者_StackOverflowableA

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

Requirement:

Join The Two Datatables and Show Result Case if Serials is equal to both Table then Status is HOT else NORMAL.

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

Is this Possible?Thanks in Regrads?

It is most certainly possible although I used 'Intersect', 'Except', and 'Union' instead of a left join. I hope that 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();

Returns:

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