Multiple fields to same list in join (CAML)

Question

Current situation:
=============
In my example, I've got 2 custom lists:

• Accounts
• Addresses

In the "Accounts"-list I've created 2 lookup columns to the "Addresses"-list

• Address1
• Address2

When I try to create a CAML query, I can succesfully get listitems with each join separately:

<Join Type='INNER' ListAlias='Addresses'>
    <Eq>
        <FieldRef Name='Address1' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>

But when I try to add a second one (for Address2) WITH different ListAlias it throws an exception:

<Join Type='INNER' ListAlias='Address1'>
    <Eq>
        <FieldRef Name='Address1' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>
<Join Type='INNER' ListAlias='Address2'>
    <Eq>
        <FieldRef Name='Address2' RefType='Id'/>
        <FieldRef List='Addresses' Name='ID'/>
    </Eq>
</Join>

ERROR:
=============

{System.ArgumentException: Value does not fall within the expected range.
 at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
 at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView)
 at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
 at Microsoft.SharePoint.SPListItemCollection.get_Count()
 at ...}

Question:
=============
Does anyone have any idea how to solve this?

Changing the ListAlias to Address1 and Address2 does not work either... but according to this: http://msdn.microsoft.com/en-us/library/ee535061(v=office.14).aspx it should...

Answer 1

Just in case you will not be able to struggle through the "Join" thing, please consider those alternative solutions:

1. Separate queries

Why not query the lists separately and then merge them manually? If you need to operate with the lists programmatically, I think this would be the best approach.

First, select all items from the Accounts list (or filter them based on your requirements, if any). Then, grab all IDs for Address items, and fetch them from Addresses list. Finally, merge them using Linq.

Tentative code (not tested, and I use multilookup here for Addresses column):

var items = accountsList.GetItems();
var ids = items
    .Cast<SPListItem>()
    .SelectMany(li => new SPFieldLookupValueCollection(li["Addresses Field"] as String).Cast<SPFieldLookupValue>().Select(lv => lv.LookupId))
    .Distinct()
    .ToArray();

var query = new SPQuery() {
    Query = "<Where><In><FieldRef Name=\"ID\"><Values>" + string.Join("", ids.Select(id => "<Value Type=\"Counter\">" + id + "</Value>") + "</Values></Where>"
};

var addressItems = addressesList.GetItems(query);

var joinedList = items.Cast<SPListItem>().Select(item => new
{
  Item = item,
  Addresses = addressItems
     .Cast<SPListItem>()
     .Where(li => new SPFieldLookupValueCollection(item["Addresses Field"] as String).Cast<SPFieldLookupValue>().Select(lv => lv.LookupId).Contains(Convert.ToInt32(li["ID"]))).ToArray()
}).ToArray();

If you add some abstractions/helper methods/method extensions to the code above, it will look much more pretty and it will do the work. If you will have much data there, it might be you will have to process the Addresses list in several steps, or something like this, but anyway...

2. Dependent Lookups

The second idea is to use dependent lookups instead of joins. Please, keep in mind that there are some important limitations to this approach (details below).

When you create a lookup field, you can pull along some other columns from the target list:

On the screenshot you can see that I've checked Postal code and CountryText fields to be pulled into my "Accounts" list.

Result will be the following:

So, you've got some additional columns which contain the required data. In SharePoint API, these columns are called dependent lookups.

Admittedly, they don't look very nice, but they do the work: data is here, without any joins. The view can easily be fixed to look more pretty using XSLT or Client rendering (in SP2013).

So now if you want same data in your code, you can get the contents of the view using SPList.GetItems(SPView) method, or you can reference the dependent lookup columns in your SPQuery.

Limitations

Unfortunately, lookup columns have some limitations. They can target only limited number of field types:

1.Counter (usually ID field) 2.Text (Single line of text) 3.Number 4.DateTime 5.Computed (only if SPFieldComputed.EnableLookups is set to true) 6.Calculated (only if it's Output type is equal to Text)

See this answer for details.

You can still include some types of fields by referencing them via Calculated columns, as I referenced CountryText column in my example above (the original "Country" column in of type "Choice", and thus cannot be used directly as a target of lookup):

Unfortunately, Calculated columns also have their limitations regarding accepted column types...