LINQ's Left outer join column selection

Question

I have three tables from a dataset ds.

var test0 = from a in ds.Tables[0].AsEnumerable()
            select a["ID"].ToString();

test0 has the following values --

  [0] "8" 
  [1] "9" 
  [2] "11" 
  [3] "2" 
  [4] "1"

var test1 = from a in ds.Tables[1].AsEnumerable()
            select a["SubscriptionID"].ToString();

---

test1 has these --

  [0] "25" 
  [1] "27" 
  [2] "4" 
  [3] "26" 
  [4] "5" 
  [5] "6" 
  [6] "1" 
  [7] "24" 
  [8] "23" 
  [9] "2" 
  [10] "9" 

var comTable1 =
  from a in ds.Tables[0].AsEnumerable()
  from b in ds.Tables[1].AsEnumerable()
    .Where(bb => bb["SubscriptionID"].ToString() == a["ID"].ToString())
    .DefaultIfEmpty()
  select b;

---

comTable1 returns these values, which are correct --

   [0] null  
   [1] {System.Data.DataRow}  
   [2] null  
   [3] {System.Data.DataRow}  
   [4] {System.Data.DataRow}

The problem for me is that if I want to select the specific field, it will throw a message "Object reference not set to an instance of an object." in comTable2 with the following codes --

var comTable2 =
  from a in ds.Tables[0].AsEnumerable()
  from b in ds.Tables[1].AsEnumerable()
    .Where(bb => bb["SubscriptionID"].ToString() == a["ID"].ToString())
    .DefaultIfEmpty()
  select b["SubscriptionID"];

---

Why the left join in LINQ does not return other no null values? Is there a way to avoid this?

I ask this because my codes need to continue the left join with other tables like --

var comTable =
  from a in ds.Tables[0].AsEnumerable()
  from b in ds.Tables[1].AsEnumerable()
    .Where(bb => bb["SubscriptionID"].ToString() == a["ID"].ToString())
    .DefaultIfEmpty()
  from c in ds.Tables[2].AsEnumerable()
    .Where(cc => cc["ID"].ToString() == (b["GroupID"]??"null").ToString())
    .DefaultIfEmpty()
  select c;

Now I could not get anything from b and c.

Thanks!

Answer 1

Well yes - if you try to dereference a null value, you will get an exception. Try this:

var comTable2 = from a in ds.Tables[0].AsEnumerable()
                from b in ds.Tables[1]
                            .AsEnumerable()
                            .Where(bb => bb["SubscriptionID"].ToString() 
                                   == a["ID"].ToString())
                            .DefaultIfEmpty()
                select b == null ? null : b["SubscriptionID"];

Basically the conditional expression at the end will leave null values where there were no matches, and the subscription ID if it did.

In your bigger query, you'd still need to deal with the possibility of b being null. Do you definitely want a left outer join here, rather than the inner join given by a LINQ join clause?