String[] in columns returned from KeywordQuery.Execute

CoreyRoth — Thu, 11 Dec 2008 16:17:51 GMT

I have posted in the past about how to use the KeywordQuery class to execute an Enterprise Search query. This makes it easy to get a DataTable containing search results. However, one caveat with this is that some columns get returned as a string array (string[]) instead of a string. This can make data binding a pain. If you wanted to bind your DataTable to a GridView, it simply will not display these columns. I used to think it had something to do with it only happening on custom managed properties that you create, but that is not actually the cast. It turns out this is actually related to the post yesterday, because it seems to happen for the same reason. If you have a managed property that has more than one crawled property mapped to it, you get a string[] instead of a string. I am sure there is a good reason for this, but I don't know what it is.

So how do we deal with this? Option 1 of course is to only map one crawled property to your managed properties. This is probably not going to be an ideal solution. Another option we have is to use LINQ to DataSets to coerce that string[] into something usable. First, we execute the query and load into a DataTable as usual.

// execute the query and put it in a dataTable
ResultTableCollection queryResults = keywordQuery.Execute();
ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
DataTable queryDataTable = new DataTable();
queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

In this case, I want to bind the builtin Title managed property as well as two of my own managed properties. I use LINQ to DataSets to put these into a new anonymous type. The Field property is generic so you can specify the type that you want. In this case string[]. Notice the Title property doesn't require the use of the string array.

var formattedResults = from row in queryDataTable.AsEnumerable()
                       select new
                       {
                           Title = row.Field<string>("Title"),
                           MyManagedProperty1 = row.Field<string[]>("MyManagedProperty1")[0],
                           MyManagedProperty2 = row.Field<string[]>("MyManagedProperty2")[0]
                       };

We then can bind this to a GridView as usual (if that's what you need it for).

SearchGridView.DataSource = formattedResults;
SearchGridView.DataBind();

I am sure there is a good reason that these fields get returned as a string[]. If you don't like these options, you can always call the web service and get the results as XML.

How to: Use the MOSS Enterprise Search KeywordQuery class

CoreyRoth — Tue, 19 Feb 2008 19:35:00 GMT

I don't think there are enough complete examples on using the KeywordQuery class out there, so I am posting this today to help out. The example in the SDK is close, but not quite enough. The KeywordQuery class is used to execute a keyword syntax query against MOSS Enterprise Search. There is also a similar class that uses WSS search which basically works the same way. To use the KeywordQuery class start by passing it the path to your SSP in the constructor. This is a good place to use object initializers as I pointed out last week to set other needed properties.

// create a new KeywordQuery class, set the query and set to RelevantResults.
KeywordQuery myQuery = new KeywordQuery(siteCollection)
{
    QueryText = string.Format("Color:\"{0}\"", "Red"),
    ResultTypes = ResultType.RelevantResults
};

In this case I am doing a keyword query searching on the managed property Color with a value of red. You must set the ResultTypes property to RelevantResults in order to get search results back. To execute the query, use the Execute method. This method returns a ResultTableCollection which in turn contains a ResultTable for each type of Result (i.e.: RelevantResults). You can then load this into a datatable and do whatever with the data.

// execute the query and load the results into a datatable
ResultTableCollection queryResults = myQuery.Execute();
ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
DataTable queryDataTable = new DataTable();
queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

Out of the box, this code will only return the default search properties such as Rank, Title, Author, Size, Path, Description, etc. If you want to return managed properties, make use of the SelectProperties string collection.

myQuery.SelectProperties.Add("Color");
myQuery.SelectProperties.Add("Size");
myQuery.SelectProperties.Add("Quantity");

Whenever I have worked with this class, I have discovered that adding anything to the SelectProperties collection will cause the default properties to no longer be returned. For example add the title and path properties back to the results with the following.

myQuery.SelectProperties.Add("Title");
myQuery.SelectProperties.Add("Path");

Lastly, using LINQ to DataSet you can further subquery your results if you needed to (i.e.: with a Quantity > 10).

var results = from queryResult in queryDataTable.AsEnumerable()
              where queryResult.Field<int>("Quantity") > 10
              select new
              {
                  Title = queryResult.Field<string>("Title"),
                  Path = queryResult.Field<string>("Path"),
                  Size = queryResult.Field<string>("Size"),
                  Quantity = queryResult.Field<int>("Quantity")
              };

Here is the complete code sample.

using (SPSite siteCollection = new SPSite(siteCollectionUrl))
{
    // create a new KeywordQuery class, set the query and set to RelevantResults.
    KeywordQuery myQuery = new KeywordQuery(siteCollection)
    {
        QueryText = string.Format("Color:\"{0}\"", "Red"),
        ResultTypes = ResultType.RelevantResults
    };
 
    // 
    myQuery.SelectProperties.Add("Title");
    myQuery.SelectProperties.Add("Path");
    myQuery.SelectProperties.Add("Color");
    myQuery.SelectProperties.Add("Size");
    myQuery.SelectProperties.Add("Quantity");
 
    // execute the query and load the results into a datatable
    ResultTableCollection queryResults = myQuery.Execute();
    ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
    DataTable queryDataTable = new DataTable();
    queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);
 
    // query the results into a new anonymous type
    var results = from queryResult in queryDataTable.AsEnumerable()
                  where queryResult.Field<int>("Quantity") > 10
                  select new
                  {
                      Title = queryResult.Field<string>("Title"),
                      Path = queryResult.Field<string>("Path"),
                      Size = queryResult.Field<string>("Size"),
                      Quantity = queryResult.Field<int>("Quantity")
                  };
}

Corey Roth [MVP] : LINQ to DataSet, Enterprise Search, MOSS

String[] in columns returned from KeywordQuery.Execute

How to: Use the MOSS Enterprise Search KeywordQuery class