Pages

Thursday, August 22, 2013

Keyword Query in SharePoint 2013


With the release of SharePoint 2013, a lot has changed with the way things were being implemented. For instance, till SP 2010, it was possible to use FullTextSQLQuery to query the results obtained from SharePoint Search Crawls. It was much easier to use considering its syntax was similar to SQL.

Now, in SP 2013, it has been deprecated. There is not even a provision to support backward compatibility FullTextSQLQuery on SharePoint 2013 platform. The only place we developers can now resort to is the KeywordQuery. It can be implemented in the pretty much same way as it was implemented on SP 2010.

So, let me take this opportunity to shed some light on Keyword Query and how it can be used to query crawl results.

KeywordQuery supports two syntax for query string formation –
  1. Keyword Query Language (KQL) and
  2. FAST Query Language (FQL)

KQL is the default syntax which can be used. FQL needs to be explicitly enabled to use it. Following is the sample code which showcases both these approaches –

Approach 1 – KeywordQuery using KQL

static void Main(string[] args)
{
SPSite site = new SPSite("url_whose_results_you_wish_to_query");
SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));

using (KeywordQuery keywordQuery = new KeywordQuery(proxy))
   {
    keywordQuery.ResultsProvider = SearchProvider.Default;
    keywordQuery.StartRow = 0;
    keywordQuery.RowLimit = 500;
    keywordQuery.EnableStemming = true;
    keywordQuery.TrimDuplicates = false;
    keywordQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
    keywordQuery.Culture = CultureInfo.CurrentCulture;
    keywordQuery.KeywordInclusion = KeywordInclusion.AllKeywords;
    keywordQuery.QueryText = "scope:\"All Sites\" AND (contentclass:\"STS_Site\" OR contentclass:\"STS_Web\")";
    keywordQuery.SelectProperties.Add("path");
    keywordQuery.SelectProperties.Add("title");
    keywordQuery.SelectProperties.Add("contentclass");
    SearchExecutor executor = new SearchExecutor();
    ResultTableCollection resultTableCollection = executor.ExecuteQuery(keywordQuery);
    var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
    var resultTable = resultTables.FirstOrDefault();
    }
}


In the above code snippet, note the SPSite URL which should be URL of the site whose search results you wish to query.

In the previous version of SharePoint, RowLimit actually had no limits. But in newer versions, it has the maximum limit of 500. This number is however configurable if we run PowerShell script on the SharePoint Search Service Application.

Also, note that now, we cannot directly execute the Query. Earlier we would simply call the method

KeywordQuery.ExecuteQuery()

and it would fetch the results for us. Now, SP 2013 introduces a new class SearchExecutor specifically built to execute queries. So in order to run a query, we would first have to initialize this class and then call its ExecuteQuery method on our KeywordQuery.

ResultType class and its corresponding KeywordQuery.ResultType property are also now obsolete. Instead we would now need to call a filter method on our resultant collection to get our desired results (see the code above).

Notice the query text in the above snippet. It is much similar to what was used in SP 2010.

Another important thing to notice above is that we would need to explicitly specify which columns should be returned in the query result. Of course, it was done earlier and in much simpler fashion using FullTextSQLQuery. Now, we would need to call the method –

keywordQuery.SelectProperties.Add("<column_name>")

to return this column in the result table collection.


Approach 2 – KeywordQuery using FQL
  
static void Main(string[] args)
{
    SPSite site = new SPSite("<url_whose_results_you_wish_to_query>");
    SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));
    using (KeywordQuery keywordQuery = new KeywordQuery(proxy))
    {
        keywordQuery.ResultsProvider = SearchProvider.Default;
        keywordQuery.SourceId = new Guid("<RESULT_SOURCE_GUID>");
        keywordQuery.EnableFQL = true;
        keywordQuery.StartRow = 0;
        keywordQuery.RowLimit = 500;
        keywordQuery.EnableStemming = true;
        keywordQuery.TrimDuplicates = false;
        keywordQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
        keywordQuery.Culture = CultureInfo.CurrentCulture;
        keywordQuery.KeywordInclusion = KeywordInclusion.AllKeywords;
        keywordQuery.QueryText = "and(scope:string(\"All Sites\"), or(contentclass:string(\"STS_Site\") , contentclass:string(\"STS_Web\")))";
        keywordQuery.QueryText = "and(scope:string(\"All Sites\"), or(contentclass:STS_Site, contentclass:STS_Web))";
        keywordQuery.SelectProperties.Add("path");
        keywordQuery.SelectProperties.Add("title");
        keywordQuery.SelectProperties.Add("contentclass");
        SearchExecutor executor = new SearchExecutor();
        ResultTableCollection resultTableCollection = executor.ExecuteQuery(keywordQuery);
        var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
        var resultTable = resultTables.FirstOrDefault();
    }
}


Notice the highlighted lines of code in Cyan color in the above snippet. Only those lines are different from what we saw earlier. Rest all settings remain the same. in the above example, i have used two types of FQL queries. You can use either of them.

SourceID needs to be explicitly mentioned here because in SharePoint 2013, we need to create a custom Result Source which possesses a different query string in it. I would cover creating Result Sources in SharePoint 2013 in a separate post.


Happy coding !!

6 comments:

  1. Hi,
    Thanks for this post.
    I have a document library and I created a Result source (CV_RS) for that library only. It has a column named "Job_Id".

    I want to get results based on that CV_RS and JobId. This JobId I can get from QueryString.

    Regards,
    Muneeb

    ReplyDelete
  2. Hello Muneeb,

    Since you have created a result source for your requirments, add its GUID at the following line of code -

    keywordQuery.SourceId = new Guid("");

    Once you have the result source configured, you may then modify your search query to retrieve results based on your custom column 'Job_id'.
    Please make sure you add this column in the search results returned by the query. To do this, use this -

    keywordQuery.SelectProperties.Add("");

    Hope it helps !

    Cheers :)

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Thank Harsh for your support.
    You mean by adding this keywordQuery.SelectProperties.Add("Job_Id") i would be able to see it in final datatable returned in the code. But i tried and its not happening. I get rid of the Result Source and defining my own QueryText dynamically. Below mentioned is my code and here {22-2014-02-13} is my Job_Id.
    Its works perfectly fine but How I can view all my columns like FullName, Department, Job_Id in the final DataTable.

    using (SPSite siteCollection = new SPSite("http://URL/"))
    {
    KeywordQuery keywordQuery = new KeywordQuery(siteCollection);
    keywordQuery.QueryText = "{22-2014-02-12} (Share*) (contentclass:STS_ListItem_DocumentLibrary OR IsDocument:True) Path:http://URL/CV_DocLib/";


    keywordQuery.SelectProperties.Add("Job_Id");
    SearchExecutor searchExecutor = new SearchExecutor();
    ResultTableCollection resultTableCollection = searchExecutor.ExecuteQuery(keywordQuery);
    var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);

    var resultTable = resultTables.FirstOrDefault();

    DataTable dataTable = resultTable.Table;
    }

    ReplyDelete
  5. Hello Muneeb,

    Add the following lines to your code to get Full Name and Department in your search result. Also, make sure these properties and searchable, queryable and retreivable.

    keywordQuery.SelectProperties.Add("FullName");
    keywordQuery.SelectProperties.Add("Department");

    Hope this helps !
    Cheers !

    ReplyDelete
  6. Hi Harsh,
    Very good post. I am also facing one issue.I am using csom, preparing fql query and passing it to search service.we have used scope parameter in 2010 but how can we pass same in 2013 as in 2013 we have result source in place of scope.
    Please help me if you have any details on this.

    ReplyDelete