Linq full outer join with NULL records C# from datatables

Question

Please can someone help? I need to return a table on Extn_In_Call_Records = Extn_Number and if either side does not match still return a calue just like a SQL Full Outer join. I have spent hours looking at this but cant get it to work!! I can get the code to work below if I remove the union but then it only returns matched results. The Datatable is being populated from MYSQL. Any help woud be great. ``` //Full Table DataTable fullext = new DataTable(); fullext.Columns.Add("Extn_In_Call_Records", typeof(string)); fullext.Columns.Add("Total_Calls", typeof(int)); fullext.Columns.Add("Extn_Number", typeof(string)); fullext.Columns.Add("Phys_Switch_Name", typeof(string)); //End Full Table try { //Full Result var result = from callrc in callrecdt.AsEnumerable() join physex in physextns.AsEnumerable() on callrc["Extn_In_Call_Records"] equals physex["Extn_Number"] .Union from physex in physextns.AsEnumerable() join callrc in callrecdt.AsEnumerable() on physex["Extn_Number"] equals callrc["Ext…

Accepted Answer

Per LINQ - Full Outer Join, the easiest way to perform a full outer join is to union two left joins. A left-join in LINQ (using the extension method syntax) takes the form:

```
var leftJoined = from left in lefts
                 join right in rights
                   on left.Key equals right.Key
                 into temp
                 from newRight in temp.DefaultIfEmpty(/* default value for right */)
                 select new
                 {
                     /* use left and newRight to construct the joined object */
                 }
```

In your case, you want to do:

```
// initialize some default elements to use later if
// they're of the same type then a single default is fine
var defaultPhysex = new {...};
var defaultCallrc = new {...};

var left = from callrc in callrecdt.AsEnumerable()
           join physex in physextns.AsEnumerable()
             on callrc["Extn_In_Call_Records"] equals physx["Extn_Number"]
           into temp
           from physex in temp.DefaultIfEmpty(defaultPhysex)
           select new 
           {
               // callrc is accessible here, as is the new physex
               Field1 = ...,
               Field2 = ...,
           }

var right = from physex in physextns.AsEnumerable()
            join callrc in callrecdt.AsEnumerable()
              on callrc["Extn_In_Call_Records"] equals physx["Extn_Number"]
            into temp
            from callrc in temp.DefaultIfEmpty(defaultCallrc)
            select new 
            {
                // physex is accessible here, as is the new callrc
                Field1 = ...,
                Field2 = ...,
            }

var union = left.Union(right);
```

Linq full outer join with NULL records C# from datatables

Solution

Problem

Related problems