left outer join produces Object reference not set to an instance of an object.

Question

Hi ,

creating a simple linq outer join bewtwen Customers and Orders from the class below:

Public Class clsData
    Private _dtCustomer As DataTable
    Private _Orders As DataTable
    Public Sub New()
        _dtCustomer = New DataTable
        _dtCustomer.Columns.Add("CustomerID", GetType(System.Int32))
        _dtCustomer.Columns.Add("Name", GetType(System.String))
        _dtCustomer.Rows.Add(1, "Customer1")
        _dtCustomer.Rows.Add(2, "Customer2")
        _dtCustomer.Rows.Add(3, "Customer3")
        _dtCustomer.Rows.Add(4, "Customer4")
        _dtCustomer.Rows.Add(5, "Customer5")
        _Orders = New DataTable
        _Orders.Columns.Add("OrderID", GetType(System.Int32))
        _Orders.Columns.Add("CustomerID", GetType(System.Int32))
        _Orders.Columns.Add("OrderName", GetType(System.String))
        _Orders.Rows.Add(1, 1, "Order1A")
        _Orders.Rows.Add(2, 1, "Order1B")
        _Orders.Rows.Add(3, 1, "Order1C")
        _Orders.Rows.Add(33, 3, "Order3")
        _Orders.Rows.Add(55, 5, "Order5")
    End Sub
    Public ReadOnly Property CustomerDatasource As DataTable
        Get
            Return _dtCustomer
        End Get
    End Property
    Public ReadOnly Property OrderDatasource As DataTable
        Get
            Return _Orders
        End Get
    End Property
End Class

then I run the code below:

        Dim odata As New clsData
        Dim query = From c In odata.CustomerDatasource.AsEnumerable Group Join o In odata.OrderDatasource.AsEnumerable On c("CustomerID") Equals o("CustomerID") Into CustomerOrders = Group From CustomerOrder In CustomerOrders.DefaultIfEmpty Select {c.Item("Name"), CustomerOrder.Item("OrderID"), CustomerOrder.Item("OrderName")}
        For Each item In query
            Console.WriteLine(item(0) & vbTab & item(1) & vbTab & item(2))
        Next

Works fine for the first 3 rows (as it should) as
Customer1 has 3 orders...Customer 2 has no orders and I am expecting Nulls for OderID and OrderName but this is where is falls over and I get  "Object reference not set to an instance of an object".

Tried the old IIF statement to track for nothing and tried IsDBNull but still coming up with the same error.

Any ideas?

Answer 1

Hi SuperRoo123,

You can filter the CustomerOrder  with where statement, please try the code below.

        Dim query = From c In odata.CustomerDatasource.AsEnumerable
                    Group Join o In odata.OrderDatasource.AsEnumerable
                    On c("CustomerID") Equals o("CustomerID") Into
                    CustomerOrders = Group
                    From CustomerOrder In CustomerOrders.DefaultIfEmpty
                    Where CustomerOrder IsNot Nothing
                                           Select {c.Item("Name"), CustomerOrder.Item("OrderID"), CustomerOrder.Item("OrderName")}

The result is :

Customer1 1 Order1A
Customer1 2 Order1B
Customer1 3 Order1C
Customer3 33 Order3
Customer5 55 Order5

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us