Calling the GetDataTable method of a SPListItemCollection object that was retrieved using a join with projected fields will throw the following error:
Object reference not set to an instance of an object
SPQuery query = new SPQuery(); query.Query = "<Where><Eq><FieldRef Name="Title"/><Value Type="Text">Search Criteria</Value></Eq></Where>"; query.Joins = "<Join Type="Left" ListAlias="Name of join list"><Eq><FieldRef Name="Title" RefType="Id" /><FieldRef List="Name of join list" Name="Id" /></Eq></Join>"; query.ProjectedFields = "<Field Name='Field name' Type='Lookup' List='Name of join list' ShowField='Field name'/>"; query.ViewFields = "<FieldRef Name="Field Name" /><FieldRef Name="Title" />"; // Next line will cause a "Object reference not set to an instance of an object" exception. DataTable table = listItems.GetDataTable();
Simply access the Fields property prior to calling GetDataTable to resolve this issue:
SPFieldCollection fields = listItems.Fields; DataTable table = listItems.GetDataTable();
Accessing the Fields property instantiates an object that is used by the GetDataTable method when there are projected fields in the join and the exception is resolved.
awesome fix.. but when we include orderby a projected field in CAML, no error but the projected column on which I am trying to sort does not have any data in the output. any idea?
You could always do the sorting within the data table itself: