SharePoint search: Use the RefinementFilter property to avoid the 4096 characters limit of SharePoint search queries

Even after 5 years of SharePoint and O365 development I sometimes stumble across endpoints I did not know about. Quite often those endpoints are quite helpful.

In this case it was the  RefinementFilter property that can be added to SharePoint Search calls. Using this property you can ignore the query length limit of 4096 characters. This can be super helpful when querying huge collections of items.

Use Case

We want get the Created Date of all items a user is following (currently only the modified date is returned when querying MS Graph for item favorites).

First we load the followed items using MS Graph. Afterwards we load the created date of the items using SharePoint search.

Lets assume the user is following these items:

1. https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage1.aspx
2. https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage2.aspx
3. https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage3.aspx
4. https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage4.aspx
5. https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage5.aspx

Search using KQL

Without the Refinement Filter property a search query for this would look like this using pure KQL:

* DocumentLink="https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage1.aspx" OR DocumentLink="https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage2.aspx" OR DocumentLink="https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage3.aspx" OR DocumentLink="https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage4.aspx" OR DocumentLink="https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage5.aspx"

This string is already 473 characters long. Lets assume the user does not only have 5 but 50 favorites. This would already create a query which is longer than 4096 characters. Therefore we would have to make at least two api calls to get all data we need.

Search Using Refinement Filter

The refinement filter property allows us to create additional filters using the FAST Query Language.

Using the RefinementFilter property the query looks like this:

*

On top of this we also set the RefinementFilter Property using FQL instead of KQL.

DocumentLink:or("https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage1.aspx","https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage2.aspx","https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage3.aspx","https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage4.aspx","https://AnyTestTenant.sharepoint.com/sites/TestSite/SitePages/TestPage5.aspx")

Even though the length of both queries is similar the second method has no data limit. This means that we can add all item urls to the Refinement filter property and won’t get any problems with the 4096 character limit.

This method has some minor downsides: Some special properties e.g. Path can not be used because of encoding problems. Instead you have to use the DocumentLink property as specified above for documents or SitePath (e.g. SitePath:or(„{ItemUrl1}*„,“{ItemUrl2}*„)) for other types such as ListItems.

C# Example

The following example shows how to use this technique to load the favorite data using the MS Graph and the refinement filters:

FavoriteHelper helper = new FavoriteHelper();
var favorites = await helper.GetFavorites(context, graphServiceClient);
foreach (var favoriteResult in favorites)
{
Console.WriteLine(favoriteResult?["Created"]);
}

using Microsoft.Graph;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Search.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TantumPoint.Examples
{
    public class FavoriteHelper
    {
        public async Task<IEnumerable<IDictionary<string, object>>> GetFavorites(ClientContext context, GraphServiceClient graphServiceClient)
        {

            var followedAppUrls = await GetFollowedItemUrl(graphServiceClient);
            return await ExecuteSharePointSearchOperation(context, followedAppUrls);
        }

        private async Task<IEnumerable<IDictionary<string, object>>> ExecuteSharePointSearchOperation(ClientContext context, List<string> followedAppUrls)
        {
            if (context == null) throw new ArgumentNullException(nameof(context));
            if (!followedAppUrls.Any()) throw new InvalidOperationException("The user is not following anything.");

            var keywordQuery = new KeywordQuery(context)
            {
                QueryText = $"*",
                TrimDuplicates = false
            };
            var refinementFilterQuery = CreateRefinementFilterQuery(followedAppUrls);
            keywordQuery.RefinementFilters.Add(refinementFilterQuery);

            var searchExecutor = new SearchExecutor(context);
            var results = searchExecutor.ExecuteQuery(keywordQuery);
            await context.ExecuteQueryAsync();
            return results.Value.FirstOrDefault().ResultRows;
        }

        private string CreateRefinementFilterQuery(List<string> followedAppUrls)
        {
            if (followedAppUrls == null) throw new ArgumentNullException(nameof(followedAppUrls));
            if (!followedAppUrls.Any()) return null;

            var combinedWebUrls = "DocumentLink:\"" + string.Join("\", DocumentLink:\"", followedAppUrls.ToArray()) + "\"";
            string refinementFilter = $"{(followedAppUrls.Count == 1 ? "equals" : "or")}({combinedWebUrls})";

            return refinementFilter;
        }

        private async Task<List<string>> GetFollowedItemUrl(GraphServiceClient graphServiceClient)
        {
            var followedAppUrls = await graphServiceClient.Me.Drive.Following
            .Request()
            .GetAsync();

            var driveItems = new List<string>();
            do
            {
                foreach (var driveItem in followedAppUrls)
                {
                    driveItems.Add(driveItem.WebUrl);
                }
            } while (followedAppUrls.NextPageRequest != null && (followedAppUrls = await followedAppUrls.NextPageRequest.GetAsync()).Count > 0);

            return driveItems;
        }
    }
}