Sorting search results by relevance/rank

Question

I've created a .net MVC application in C# that lists organisations, there are currently 6000 records in the database (SQLServer). The fields in the organisation table are:

Title (Alcohol support group)
ContactPerson (James Bond)
Content (We provide support to people with alcohol problems)
Keywords (Alcohol, Addiction, Alcoholic)

The current search is done using linq, e.g. :

iList orglist = myOrgs.Where(x => x.Title.Contains('abc') || 
                                                x.ContactPerson.Contains('abc') || 
                                                x.Details.Contains('abc') || 
                                                x.Keywords.Contains('abc'))
                                    .OrderBy(x => x.Title).ToList();

The results are then ordered by title. Which is not suitable.

If someone searches for 'Alcohol Support' I want the above result at the top of the list.

I would like the results to be ranked on the following:

Full sentence matches in organisation titles.
All search terms in organisation titles.
Any search terms in organisation titles.
Any search terms in organisation keywords.
Full sentence matches in organisation content.

Looking for advice on the best way to implement this, or if anyone knows of any algorithms/libraries out there that do this already?

** Update ** Im looking at a simpler solution now, see this link:

Sorting collection based on keywords with Linq

user_4879683 · Answer

Summary :

Requirement's
- R01 | titles | Full matches |in order
- R02 | titles | Full matches |in any order
- R03 | titles | Any matches |
- R04 | keywords | Any matches |
- R05 | content | Full matches |
For each Requirement We will do SQL Call
Each SQl Call we will return Only rows id's
Then we Group the Id's in order
We will do a final SQL call

Step 01 : R01

Here we will use EF

db.Orgs.Where(w => w.Title.Contains(search_query))
.Select(s => s.Id).ToList();

This use of linq2Sql Contains will be translated to sql WHERE IN

Step 02 : R02

here we will use plain sql WHERE + LIKE + AND

Select Id From Orgs where 
Title LIKE '%' + @param0 +'%' 
and Title LIKE '%' + @param1 +'%'

Step 03 : R03

here we will use plain sql WHERE + OR + And

Select Id From Orgs 
where Title LIKE '%' + @param0 +'%'  
or  Title LIKE '%' + @param1 +'%'

Step 04 : R04

here we will use plain sql WHERE + OR + And

Select Id From Orgs 
Where Keywords LIKE '%' + @param0 +'%' 
or  Keywords LIKE '%' + @param1 +'%'

Step 05 : R05

Here we will use EF

    db.Orgs
    .Where(w => w.Content.Contains(search_query)).
    Select(s => s.Id).ToList();

This use of linq2Sql Contains will be translated to sql WHERE IN

Step 06 - Group The Id's and ignore duplicable one's

using the row id's form step 1 to 5

we will sort the id's base on order retrieval

        var ids = new Dictionary();

        foreach (var id in Ids1)
        {
            int val;
            if (!ids.TryGetValue(id, out val))
            {
                ids.Add(id, ids.Count());
            }

        };
        .
        .

Step 07 - Re-order

         ids.OrderByDescending(o => o.Value)
        .Select(s => s.Key) .ToArray();

Step 08 - Now we wi use The Oredred Id's to Get The data

here we will use plain sql ORDER BY + CASE WHEN THEN ELSE END

Select * from Orgs 
where  Id in ( 2 , 1 )  
ORDER BY CASE id  
WHEN 2 THEN 0 
WHEN 1 THEN 1 
ELSE 2 END

Step 09 Full Code

    using System;
    using System.Collections.Generic;
    using System.Linq;

    namespace ConsoleApp9
    {
    class Program
    {
    static void search(string search_query)
    {
    //////////////////////////////////////////////////
    var terms = search_query.Split(' ');
    //////////////////////////////////////////////////
    var Ids1 = db.Orgs.
    Where(w => w.Title.Contains(search_query))
    .Select(s => s.Id).ToList();

    var Ids2 = db.Database
    .SqlQuery(getWhere("Title", "AND"), terms)
    .ToList();

    var Ids3 = db.Database
    .SqlQuery(getWhere("Title", "OR"), terms)
    .ToList();

    var Ids4 = db.Database
    .SqlQuery(getWhere("Keywords", "OR"), terms)
    .ToList();

    var Ids5 = db.Orgs
    .Where(w => w.Content.Contains(search_query))
    .Select(s => s.Id).ToList();

    var ordered_ids = reorderList(Ids1, Ids2, Ids3, Ids4, Ids5);

    var OrderedData = db.Database.SqlQuery(getOrdered(ordered_ids)).ToList();

    //////////////////////////////////////////////////

    foreach (var item in OrderedData)
    {
        Console.WriteLine($"{item.Id} - {item.Title} - {item.ContactPerson } - {item.Keywords } - {item.Content  }");

    }

    //////////////////////////////////////////////////
    Console.ReadLine();
    //////////////////////////////////////////////////
    string getWhere(string _column, string _oprator)
    {
        var val = "Select Id From Orgs where ";
        for (int i = 0; i < terms.Length; i++)
        {
            if (i > 0) val += @" " + _oprator + " ";
            val += @" " + _column + " LIKE '%' + {" + i + "} +'%'  ";
        }
        return val;
    }
    //////////////////////////////////////////////////
    string getOrdered(int[] _ids_ordered)
    {
        var val = "Select * From Orgs where ";
        val += " Id in ";
        for (int i = 0; i < _ids_ordered.Length; i++)
        {
            if (i == 0) val += "( ";
            if (i > 0) val += " , ";
            val += _ids_ordered[i];
            if (i == terms.Length - 1) val += " ) ";
        }
        val += " ORDER BY CASE id ";
        for (int i = 0; i < _ids_ordered.Length; i++)
        {
            val += " WHEN " + _ids_ordered[i] + " THEN " + i;
        }
        val += " ELSE " + _ids_ordered.Length + " END ";

        return val;
    }
    //////////////////////////////////////////////////
    int[] reorderList(List _Ids1, List _Ids2,
        List _Ids3, List _Ids4, List _Ids5)
    {
        var idsDic = new Dictionary();

        var idsArr = new List[5] { Ids1, Ids2, Ids3, Ids4, Ids5 };
        for (int i = 0; i < 5; i++)
        {
            idsArr[i].ForEach(id =>
            {
                if (!idsDic.TryGetValue(id, out int val))
                    idsDic.Add(id, idsDic.Count());
            });
        };
        var o_ids = idsDic.OrderByDescending(o => o.Value)
                .Select(s => s.Key).ToArray();
        return o_ids;
    }
    }

    static Model1 db = new Model1();

    static void Main(string[] args)
    {
    string search_quer = "Alcohol Support";
    Console.WriteLine($"searching for {search_quer}");
    search("Alcohol Support");
    }
    }


    }

Note 01 : Sql Injection

What is Sql Injection

SQL injection is a code injection technique, used to attack data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution

Note 01.01 :The Problem

Note 01.02 : Microsoft documentation

From Microsoft documentation :How to: Directly Execute SQL Queries | Microsoft Docs

The parameters are expressed in the query text by using the same curly notation used by Console.WriteLine() and String.Format(). In fact, String.Format() is actually called on the query string you provide, substituting the curly braced parameters with generated parameter names such as @p0, @p1 …, @p(n).

Note 01.03 : In this project

When using EF 6.2

var sql2 = " Select Id From Orgs where ";
for (int i = 0; i < terms.Length; i++)
{
    if (i > 0) sql2 += @" and ";
    sql2 += @" Title LIKE '%' + {" + i + "} +'%'  ";
}

Will generate :

Select Id From Orgs where  
Title LIKE '%' + {0} +'%'   
and  Title LIKE '%' + {1} +'%'

In sqlserver using SQL Server Profiler

exec sp_executesql N' Select Id From Orgs where  
Title LIKE ''%'' + @p0 +''%''   and  Title 
LIKE ''%'' + @p1 +''%''  ',N'@p0 nvarchar(7)
,@p1 nvarchar(7)',@p0=N'Alcohol',@p1=N'Support'

Note 01.04 : Another format

we can also use SqlParameter class

var sql4 = " Select Id From Orgs where  ";
var sql4_parameter = new List();
for (int i = 0; i < terms.Length; i++)
{
    if (i > 0) sql4 += @" or ";
    sql4 += @" Keywords LIKE '%' + @param" + i + " +'%'  ";
    sql4_parameter.Add(new SqlParameter("param" + i, terms[i]));
}

and here is sql

 Select Id From Orgs 
 Where Keywords LIKE '%' + @param0 +'%' 
 or  Keywords LIKE '%' + @param1 +'%'