SpQuery.Joins and ProjectedFields

In SharePoint 2010 we have a good feature to creating relation among the Lists and get the output. SPQuery has two properties which can be used to achieve this.

· Joins

· ProjectedFields

Joins:

Used to join two lists for queries

ProjectedFields:

Specifies the fields in joined lists that can be included in the view when one or more other lists have been joined in a query.

Example:

Consider the following Diagram.

Here I have created 3 SharePoint Custom List named Customers, Locations and CityState. While querying the Customers List I like to have ZipCode and AreaCode along with the result. How we can achieve this?

Here I have changed the display name of the Title column to CustomerName,City & State in the appropriate SharePoint list.

First we will do an initial Filter on the Customers List

1 2 3 4 5 6 7 8 9 10

using (SPSite site = new SPSite("http://Server/")) {   using (SPWeb web = site.OpenWeb())     {     SPList list = web.Lists["Customers"];     SPQuery query = new SPQuery();     query.Query = "<Where><Eq><FieldRef Name='Title'/><Value             Type='Text'>Manimaran</Value></Eq></Where>";    } }

Now come the Joins, ProjectedFields and ViewFields

Joins:

1 2 3 4 5 6 7 8 9 10 11 12

query.Joins = @"<Join Type='LEFT' ListAlias='Locations'> <Eq> <FieldRef Name='CityName' RefType='ID'/> <FieldRef List='Locations' Name='ID'/> </Eq> </Join> <Join Type='LEFT' ListAlias='CityState'> <Eq> <FieldRef Name='State' RefType='ID'/> <FieldRef List='CityState' Name='ID'/> </Eq> </Join>";

Here Type will have the following values

· LEFT

· INNER

ProjectedFields:

1 2 3

query.ProjectedFields = @" <Field Name='ZipCode' Type='Lookup' List='Locations' ShowField='ZipCode'/> <Field Name='AreaCode' Type='Lookup' List='CityState' ShowField='AreaCode'/> ";

here Name is the alias name we provided. This Name should be used in the ViewFields property of the SPQuery. Suppose if you provide PostalCode instead of ZipCode, the PostalCode needs to be used in ViewFields.

ShowField :- Field From where we need to get the value.

ViewFields:

1 2 3 4 5

query.ViewFields = @"<FieldRef Name='Title'/> <FieldRef Name='CityName'/> <FieldRef Name='ZipCode'/> <FieldRef Name='State'/> <FieldRef Name='AreaCode'/> “;

Now get the Items for the above query.

1	SPListItemCollection items = list.GetItems(query);

The output of the ZipCode and AreaCode will come as Lookup value we have to access the value as

1 2 3 4 5

SPFieldLookupValue lookupZip = new SPFieldLookupValue(items[0]["ZipCode1"].ToString()); string ZipCodeValue = lookupZip.LookupValue;   SPFieldLookupValue lookupAreaCode = new SPFieldLookupValue(items[0]["AreaCode1"].ToString()); string AreaCode = lookupAreaCode.LookupValue;

I have added another one column (LandMarks) which is a single line of text column in the Locations List.

Then we need to make change in the ProjectedFields and ViewFields as follows.

ProjectedFields:

1 2 3 4 5

query.ProjectedFields = @" <Field Name='ZipCode1' Type='Lookup' List='Locations' ShowField='ZipCode'/> <Field Name='LandMarks1' Type='Lookup' List='Locations' ShowField='LandMarks'/> <Field Name='AreaCode1' Type='Lookup' List='CityState' ShowField='AreaCode'/> ";

ViewFields:

1 2 3 4 5 6 7 8 9 10 11 12 13

query.ViewFields = @"<FieldRef Name='Title'/>   <FieldRef Name='CityName'/>   <FieldRef Name='ZipCode1'/>   <FieldRef Name='State'/>   <FieldRef Name='AreaCode1'/>   <FieldRef Name='LandMarks1'/>   ";

Observations:

1. DataTable is not supported when using Joins. I get the error “Object reference not set to an instance of an object” when I call the result in datable as

list.GetItems(query).GetDataTable()

2. Multiple Lines of text, Choice type columns are not supported in ProjectedFields.