Maximum lookup columns - the magic number 13

Question

So I did create on an SPWeb 20 custom lists and then a tasks list. For each of those 20 lists I've created 20 site lookup columns. I then created 20 Content Types based on Task content type. Then I've added each content type to the Tasks list. After that I've added each lookup site column to the corresponding list content type. So far so good.

I've built a custom WebPart that displays task items using ListViewByQuery control. Here is how an SPQuery looks:

<Query>    
    <Where>
        <Eq>
            <FieldRef Name='Relaterat_x0020_till_x0020_avtal' LookupId='TRUE' />
            <Value Type='Lookup'>1</Value>
        </Eq>    
    </Where>
</Query>

The problem is that this SPQuery returns results only for the first 13 lookup columns. If I put in the FieldRef the internal name for lookup column no 14 or above, it is not working anymore. Why?

If I don't use LookupId='TRUE' and do this: (use the value of the lookup and not the id)

<Query>    
    <Where>
        <Eq>
            <FieldRef Name='Relaterat_x0020_till_x0020_avtal' />
            <Value Type='Lookup'>Test avtal</Value>
        </Eq>    
    </Where>
</Query>

then it is working.

Here is the code:

// create listViewByQuery
ListViewByQuery listViewByQuery = new ListViewByQuery();
listViewByQuery.List = list;
SPQuery query = new SPQuery(view);

System.Text.StringBuilder sbViewFields = new System.Text.StringBuilder();

// all fields from view
foreach (string field in view.ViewFields)
{
    sbViewFields.Append("<FieldRef Name='" + field + "'/>");
}

string lookupID = HttpContext.Current.Request.QueryString["ID"].ToString();
query.Query = "<Where><Eq><FieldRef Name='" + lookupInternalName + "' LookupId=\"TRUE\" /><Value Type='Lookup'>" + lookupID + "</Value></Eq></Where>";

listViewByQuery.Query = query;
this.Controls.Add(listViewByQuery);

Update:

I've built a C# console app which creates all lists / lookup columns / content types and add the lookup column to corresponding content type. Result: same thing, same magic number 13. Anyone interested in seeing and running the script send me an email at: ovulex [ at ] yahoo.com.

I will report this to Microsoft.

ScreenShot http://img63.imageshack.us/img63/6412/magicnumber13.png

Answer 1

I've not experienced this myself, but my guess is that it has something todo with SQL limitations. I would turn on the SQL profiler and look at the actual query that is executed in SQL and analyze that. No answer, but an idea on to how to figure it out...

Answer 2

I'm wondering if it would make any difference if you would explicitly reference each field within the ViewFields element.