<CharlieDigital /> Programming, Politics, and uhh…pineapples

Why Your CAML Query Might Be Returning No Results

A curious thing.

We were working on a custom view for one of our lists and found that we were not able to retrieve a list item using a CAML query that should have worked.

After spending quite a bit of time studying the query itself, I decided to look at the actual SQL that was being generated.

This list has a large number of columns and SharePoint was wrapping the rows according to the boundary conditions.  For example, when you have 16 integer or user fields, the 17th field of this type will force the row to wrap into a second row.

When this occurs, in the AllUserData table, you will see multiple rows for the same item, each with a different tp_RowOrdinal. Looking in the table, it was clear that the value was in the table, but it was not on the first row (tp_RowOrdinal="0").

The problem with this is that for some odd reason, the SQL query that gets generated when you query on the field that has wrapped onto the second row inexplicably only queries the first row.

In the generated SQL, I found the following:

...AND (UserData.tp_RowOrdinal=0) AND ((UserData.[int10] = N''34'') ...

In this case, "34" represents the ID of a user and "int10" represents the column that the data is in.  But the key is that even though this data occurred in the row with ordinal 2, the SQL will filter only on the row with ordinal 0.

This is kind of inexplicably bad but not all is lost.

The dirty workaround is that you can do one of two things:

1. If you are adding the columns manually or in a single content type, you need to ensure that the field(s) that you want to query against appear earlier in your content type definition or they get added earlier.
2. If you are adding the columns via content types in a Schema.xml file, you need to ensure (1) above and that you add that content type to the list earlier in the XML.

There is an MSDN discussion on this, but Microsoft seems to claim that this is not a "bug" (it most certainly is).

SharePoint DirectoryNotFoundException (0×80070003)

I've been dealing with an interesting SharePoint error for the better part of a day-and-a-half now and I thought it was worth sharing.

The error surfaced was a DirectoryNotFoundException (0x80070003) when attempting to call BreakRoleInheritance in an asynchronous event receiver.

The purpose of the receiver was to read a set of rules which specified how to configure permissions for objects based on metadata and content type. However, this would fail with the aforementioned error, but only for folders.

Of course, this was a weird error because I could certainly see the folder in the list.

It turns out that the root cause is how we were setting the titles/names on our folders.  One issue with folders is that depending on how you add the list item, you may have to go back and rename it.  Otherwise, it gets a title based on its ID.

Our original logic looked like this (note the use of the Name property) and it was raised after the item was created in the list:

if (entity.ContentTypeId.StartsWith("0x0120")) // Only for folders
{
    item = list.GetItemById(item.ID);
    item["Name"] = entity.Title;
    item.SystemUpdate(false);
}

(Assume that entity is simply a container that describes the list item to create.)

The exception occurs in the case where the rename executes before the asynchronous event receiver finishes executing, thus the directory -- as originally named -- no longer exists since it's been renamed by the code above.  This caused random errors on our systems based on the order of execution and of course, when the debugger is attached, it works perfectly fine (I think because it changes the threading model).

We've changed our code now to something like this instead:

private SPListItem AddItem<T>(T entity, SPList list, string parentFileRef) where T : CtmoModelBase, new()
{
    if (string.IsNullOrEmpty(parentFileRef)) //Add to sub-folders
    {
        parentFileRef = list.RootFolder.Url;
    }

    _log.DebugFormat("Saving object to container URL: {0}", parentFileRef);

    try
    {
        Web.AllowUnsafeUpdates = true;

        if (entity.ContentTypeId.StartsWith("0x0120"))
        {
            // Add a folder (same in all cases)
            return list.AddItem(parentFileRef, SPFileSystemObjectType.Folder, entity.Title);
        }

        if (list.BaseType == SPBaseType.DocumentLibrary)
        {
            // Add a file to a document library.
            SPFolder parentFolder = Web.GetFolder(parentFileRef);

            return parentFolder.Files.Add(entity.Title, entity.BinaryContents, true).Item;
        }
        else
        {
            // Add a list item to a custom list.
            return list.AddItem(parentFileRef, SPFileSystemObjectType.File, entity.Title);
        }
    }
    finally
    {
        Web.AllowUnsafeUpdates = false;
    }
}

Which has solved the issue as the name of folder and items created from folder based content types no longer need to be updated to set the display name.

Working With Folder Specific Views in SharePoint Lists

One neat feature in SharePoint that I recently discovered is the ability to specify views specific to folder based content types in a list.

What's great about this is that if you're building a hierarchical store in a list with folder based content types, then you can have each level of the hierarchy display different views.

As an example, consider a school district.

They may map their classes like this:

• School A (Folder based CT)
• Grade A1 (Folder Based CT)
• Class A1.1 (Folder Based CT)
• Student A1.1.1 (Item Based CT)
• Student A1.1.2
• Class A1.2
• Grade A2
• School B
• Grade B1
• Grade B2

Now for each of the folder based content types, you can specify a view specific to that content type by using the DefaultViewForContentType="TRUE" and ContentTypeID attributes of View.  For example, on the Grade level view, when a user selects a Grade, I would want to show a column called "Teacher" because in this view, I'm showing Class items.  However, at the School level, "Teacher" doesn't make sense as a column.

Now none of this is all very difficult to figure out, however there are a few notes:

1. To get this to work, you have to specify these values for EVERY view.  If you don't, you will see that at each level of the hierarchy, you will have the view available, but the navigation will not automatically select that content type specific view.
2. For the default view, you need to add it as 0x012001.

This is a great tip as it creates for a much better end-user experience by allowing them to have contextual views based on the current content type.

Folders and Nested Folders in ListInstances

This took me about 3 hours to figure out so I figure it was worth sharing.

First, I assumed that it was possible given that the .stp file export of a custom list with folders and nested folders seem to work fine.  Online, the feedback seems to be a bit different and my early results seemed to confirm it.  However, I persisted and after several (and I mean several) tries, I finally got it working.

So I share with you the magic sauce for those times when you want to seed your deployment with hierarchical data.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance Title="Seed Data"
      OnQuickLaunch="TRUE"
      RootWebOnly="TRUE"
      TemplateType="10000"
      Url="Lists/SeedData"
      Description="Seed data">
      <Data>
          <Rows>
              <Row>
                  <Field Name="ID">1</Field>
                  <Field Name="ContentType">Country</Field>
                  <Field Name="Title">USA</Field>
                  <Field Name="FSObjType">1</Field>
              </Row>
              <Row>
                  <Field Name="ID">2</Field>
                  <Field Name="ContentType">State</Field>
                  <Field Name="Title">New Jersey</Field>
                  <Field Name='FileDirRef'>USA</Field>
                  <Field Name="FSObjType">1</Field>
              </Row>
              <Row>
                  <Field Name="ID">3</Field>
                  <Field Name="ContentType">County</Field>
                  <Field Name="Title">Mercer</Field>
                  <Field Name="FileDirRef">USA/New Jersey</Field>
                  <Field Name="FSObjType">0</Field>
              </Row>
          </Rows>
      </Data>
  </ListInstance>
</Elements>

Yes, it was a pretty painful experience to get this figured out, but it was definitely worth it.

Force File Name in SharePoint 2010 Search Results

There are a few resources on this for 2007, but I thought I'd add a small update for 2010.

First, start by following the instructions in the links above for the IsDocument property.  You can get to it from the search service administration and it's under Metadata Properties:

Find the IsDocument property in here

Now go to your site and create a new web part page (header, left nav, right body) and call it CustomSearchResults.aspx (or whatever you'd like).  On the right hand side, add a " web part and on the left hand side, add a Refinement Panel web part.

Search web parts available - add Search Core Results and Refinement Panel

Now you need to edit the results web part.

1. Under the Core Results settings panel, expand the Display Properties sub-panel.
2. Uncheck Use Location Visualization to enable the Fetched Properties and XSL Editor options.
3. Modify the Fetched Properties XML string to include <Column Name="Filename"/> (strongly recommend copying the string out and editing it in a text editor).
4. Click the XSL Editor button to modify the XSL.  Again, I strongly recommend copying the XSL out to a text editor.
5. Around line 206-207, you should see the following line:

   <xsl:template match="Result">
       <xsl:variable name="id" select="id"/>

6. Modify it to look like so:

   <xsl:template match="Result">
       <xsl:variable name="id" select="id"/>
       <xsl:variable name="filename" select="filename"/>
       <xsl:variable name="isdocument" select="isdocument"/>

7. Next, around (new) line 324, you should see the following XSL:

   <xsl:otherwise>
       <a id="{concat($currentId,'_Title')}">
           <xsl:attribute name="href">
               <xsl:value-of  select="$url"/>
           </xsl:attribute>
           <xsl:attribute name="title">
               <xsl:value-of select="title"/>
           </xsl:attribute>
           <xsl:choose>
               <xsl:when test="hithighlightedproperties/HHTitle[. != '']">
                   <xsl:call-template name="HitHighlighting">
                       <xsl:with-param name="hh" select="hithighlightedproperties/HHTitle" />
                   </xsl:call-template>
               </xsl:when>
               <xsl:otherwise>
                   <xsl:value-of select="title"/>
               </xsl:otherwise>
           </xsl:choose>
       </a>
   </xsl:otherwise>

8. Update this to the following:

   <xsl:otherwise>
       <a id="{concat($currentId,'_Title')}">
           <xsl:attribute name="href">
               <xsl:value-of  select="$url"/>
           </xsl:attribute>
           <xsl:attribute name="title">
               <xsl:value-of select="title"/>
           </xsl:attribute>
   
           <xsl:if test="$isdocument">
               <xsl:value-of select="filename"/>
           </xsl:if>
           <xsl:if test="not($isdocument)">
               <xsl:choose>
                   <xsl:when test="hithighlightedproperties/HHTitle[. != '']">
                       <xsl:call-template name="HitHighlighting">
                           <xsl:with-param name="hh" select="hithighlightedproperties/HHTitle" />
                       </xsl:call-template>
                   </xsl:when>
                   <xsl:otherwise>
                       <xsl:value-of select="title"/>
                   </xsl:otherwise>
               </xsl:choose>
           </xsl:if>
       </a>
   </xsl:otherwise>

Now hook up your custom search results page by clicking on Site Settings and then Search Settings. In the Site Collection Search Results Page text box, enter the full relative path to your page (including sub-site paths and such).  For example: /sites/site_xyz/pages/mycustomsearchresults.aspx.

That should do it.  Unfortunately, I haven't figured out how to turn on hit highlighting for the file name yet.