How to left join two Datables?

Question

Here i my Scenario

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

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?

Answer 1

If you join two tables in Linq it will be treated as inner join not outer join, so you will get only matching results,i think your query is returning below result.

SerialNumber  PartNumber    Status 
001             A           HOT 
002             B           HOT

try below Code

 var ret = (from p in TableB.AsEnumerable()
                   join q in TableA.AsEnumerable()
                   on p["SerialNumber"] equals q["SerialNumber"] into tempTable
                   from r in tempTable.DefaultIfEmpty()
                   select new
                   {
                       SerialNUmber = p["SerialNumber"],
                       UnitStatus = "HOT",
                       PartNumber = p["PartNumber"]
                   }).ToList();

Answer 2

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