SearchExtensions: Ranked searches now supported by the fluent API

I have just released the latest update to SearchExtensions nuget package.

This update allows for Ranked Searches to be performed as part of the fluent API.

Performing a Ranked Search

Using the fluent Api, performing a ranked search is the same as performing a regular search, you simply need to transform your results to Ranked using the .ToRanked() method.

var result = data.Search(x => x.Name)
                 .Containing("search")
                 .ToRanked();  // <-- This transforms the result

The ToRanked() method transforms the result from IQueryable<T> to IQueryable<IRanked<T>>

public interface IRanked<out T>
{
    int Hits { get; }
    T Item { get; }
}

This means you can order by the results with the most hits. The hit count is also calculated across multiple properties, for example you can add a second property to Search and the Hits property will be calculated across all properties.

var result = data.Search(x => x.Name, x => x.Description)
                 .Containing("search")
                 .ToRanked();

In the above example, The hit count will ad all hits in both Name and Description properties.

The SQL

When connected to a SQL server data source, when performing a basic ranked search across 2 columns, the following SQL is generated:

SELECT 
    ((( CAST(LEN(
          CASE WHEN ([Extent1].[Name] IS NULL) 
               THEN N'' 
               ELSE [Extent1].[Name] 
          END) AS int)) - 
       ( CAST(LEN(REPLACE(
          CASE WHEN ([Extent1].[Name] IS NULL) 
          THEN N'' 
          ELSE [Extent1].[Name] 
          END, N'search', N'')) AS int))) / 6) 
    +  -- ** Add hits of first property to that of the second
    ((( CAST(LEN(
        CASE WHEN ([Extent1].[Description] IS NULL) 
             THEN N'' 
             ELSE [Extent1].[Description] 
         END) AS int)) - 
      ( CAST(LEN(REPLACE(
          CASE WHEN ([Extent1].[Description] IS NULL) 
               THEN N'' 
               ELSE [Extent1].[Description] 
          END, N'search', N'')) AS int))) / 6) AS [C2], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[StringOne] AS [Name], 
    [Extent1].[Description] AS [Description]
    FROM [dbo].[TestModels] AS [Extent1]
    WHERE ([Extent1].[Name] LIKE N'%search%') OR ([Extent1].[Description] LIKE N'%search%')

This is fairly hard to decipher but I will try and explain what is going on. At the top we are counting the amount of hits each row has. This is done by performin the following psuedo code:

([Property].length - [Property].Replace([searchTerm], '').length) / [searchTerm].length

This translates to the following SQL (with null checks). The following assumes we performed a search for the word "search" on the Property property.

-- ** GET THE LENGTH OF THE PROPERTY **    
    LEN(CASE WHEN ([Extent1].[Property] IS NULL) 
             THEN N'' 
             ELSE [Extent1].[Property] 
        END) 
-- ** SUBTRACT **
    -   
-- ** SEARCH FOR THE SEARCH TERM AND REMOVE OCCURRENCES **
    LEN(REPLACE(
          CASE WHEN ([Extent1].[Property] IS NULL) 
          THEN N'' 
          ELSE [Extent1].[Property] 
          END, N'search', N''))
 -- ** DIVIDE THE RESULT BY THE LENGTH OF THE 
 --    SEARCH TERM TO WORK OUT THE NUMBER OF HITS **
     / 6) 

Ranked Search combinations

Ranked searches can be combined with any of the other fluent search methods however it is only the Containing() method that contributes to the ranked search hit count.

var result = data.Search(x => x.Name, x => x.Description)
                 .StartsWith("abc", "test", "john")
                 .Containing("efg", "hij")
                 .ToRanked()

The above search in plain english...

• Name OR Description property starts with "abc", "test", or "john"
• AND Name OR Description contains "efg" or "hij"

I hope this new feature is helpful to those of you out there that have downloaded NinjaNye.SearchExtensions.

If you are new to NinjaNye.SearchExtensions you can download the package using the following command:

PM> Install-Package NinjaNye.SearchExtensions

Please let me know your thoughts in the comments below.