c# - 按相关性/排名对搜索结果进行排序

Question

我在 C# 中创建了一个列出组织的 .net MVC 应用程序，目前数据库 (SQLServer) 中有 6000 条记录。组织表中的字段是:

• 职位(酒精支持小组)
• 联系人(詹姆斯·邦德)
• 内容(我们为有酗酒问题的人提供支持)
• 关键字(酒精、成瘾、酒精)

当前的搜索是使用 linq 完成的，例如:

iList<Organisation> 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();

然后结果按标题排序。哪个不合适。

如果有人搜索“Alcohol Support”，我希望将上述结果放在列表的顶部。

我希望结果按以下顺序排列:

1. 组织名称中的完整句子匹配。
2. 组织名称中的所有搜索词。
3. 组织名称中的任何搜索词。
4. 组织关键字中的任何搜索词。
5. 组织内容中的完整句子匹配。

正在寻找有关实现此功能的最佳方法的建议，或者是否有人知道已经在执行此操作的任何算法/库？

** 更新 **我现在正在寻找一个更简单的解决方案，请参阅此链接:

Sorting collection based on keywords with Linq

Answer 1

总结:

• 需求

  • R01 |标题 |完整匹配|按顺序
  • R02 |标题 |完全匹配|以任意顺序
  • R03 |标题 |任何匹配|
  • R04 |关键字 |任何匹配|
  • R05 |内容 |完整匹配 |

• 对于每个需求，我们将执行 SQL 调用

• 我们将仅返回行 ID 的每个 SQl 调用

• 然后我们按顺序对 Id 进行分组

• 我们将进行最后的 SQL 调用

---

步骤 01 : R01

• 这里我们将使用 EF

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

这个使用linq2Sql的Contains会被翻译成sqlWHERE IN

步骤02:R02

• 这里我们将使用纯 sqlWHERE + LIKE + AND

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

步骤03:R03

• 这里我们将使用纯 sqlWHERE + OR + And

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

步骤04:R04

• 这里我们将使用纯 sqlWHERE + OR + And

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

步骤 05 : R05

• 这里我们将使用 EF

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

这个使用linq2Sql的Contains会被翻译成sqlWHERE IN

第 6 步 - 将 ID 分组并忽略重复的

• 使用行 ID 的表单步骤 1 到 5

我们将根据顺序检索对 id 进行排序

        var ids = new Dictionary<int, int>();

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

        };
        .
        .

步骤 07 - 重新排序

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

第 08 步 - 现在我们将使用 Oredred Id 来获取数据

• 这里我们将使用纯 sqlORDER BY + CASE WHEN 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
  

第 9 步完整代码

---

    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<int>(getWhere("Title", "AND"), terms)
    .ToList();

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

    var Ids4 = db.Database
    .SqlQuery<int>(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<Org>(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<int> _Ids1, List<int> _Ids2,
        List<int> _Ids3, List<int> _Ids4, List<int> _Ids5)
    {
        var idsDic = new Dictionary<int, int>();

        var idsArr = new List<int>[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");
    }
    }


    }

---

注解01:SQL注入(inject)

• 什么是SQL注入(inject)

  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

注释 01.01:问题

注释 01.02:Microsoft 文档

• 来自 Microsoft 文档: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).

注释 01.03:在这个项目中

• 使用 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 + "} +'%'  ";
  }
  

• 将生成:

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

• 在 sqlserver 中使用 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'
  

注释 01.04:另一种格式

• 我们也可以使用 SqlParameter 类

  var sql4 = " Select Id From Orgs where  ";
  var sql4_parameter = new List<SqlParameter>();
  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]));
  }
  

• 这里是sql

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