3

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...

6
  • Important notice: Multilookup is perfectly supported in CAML queries (if you use SPQuery or client-side CamlQuery). There are even several specialized elements like Includes and NotIncludes which allow to work with multilookups. Though multilookups are not supported in SPSiteDataQuery and it's descendants. Mar 4, 2013 at 12:38
  • Is there an example of this? If you look to this site's anwser, they say it isn't possible social.technet.microsoft.com/Forums/en-PH/… Mar 4, 2013 at 13:20
  • 1
    This only applies to "Join" element, but you can still use MultiLookups e.g. in "Where" clause. Perhaps in your case MultiLookup column cannot be used, true, but I just wanted to point out that this comment "(PS...why not multi-lookup??? -> multilookup is not supported in CAML queries)" is not accurate. It is supported, though in not all cases. Mar 4, 2013 at 13:38
  • Ok, thanks for clearing that up! Updated the post ;-) Mar 4, 2013 at 13:40
  • And I think most likely you can achieve your goal without any "Joins", just by adding dependent lookup columns. Mar 4, 2013 at 13:43

1 Answer 1

2

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:

enter image description here

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:

enter image description here

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):

enter image description here

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

3
  • Thanks for this Andrey, I'll keep this in mind. Preferably I would choose the dependent lookup columns if the joins don't work, but my only concern with this is I'll get to the column treshold (Lookup: max 96) since I have another table Contacts that will inherit from Accounts (and Accounts from Addresses), Address allready has +25 columns, Accounts also -> that's allready +75 columns Mar 4, 2013 at 15:30
  • As for the first solution... this is a great one, but I need to query from different datasources and compare the data. It would be better if I got ALL needed data at once, since we've had performance issues in the past Mar 4, 2013 at 15:33
  • Wow, 75 columns is really a great many! I'm afraid the second approach will not do. "List View Lookup Thresold" is by default 8 (see this post for details: sympmarc.com/2012/07/23/…), and big number of lookup columns in fact often cause performance issues, though I'm not sure if that concerns dependent lookups. But please test this before you start actual implementation. Since there are so many columns, I would say the first approach is even better. Two simple queries often perform better than a single heavy one. Mar 4, 2013 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.