根据相关性/排名对搜索结果进行排序

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

结果按标题排序，这并不合适。如果有人搜索“酒精支持”，我希望上述结果排在列表顶部。我希望按以下方式对结果进行排名：

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

请问如何最佳实现此功能？或者是否有已经实现此功能的算法/库？

**更新** 现在我正在寻找一个更简单的解决方案，请参考以下链接：使用Linq基于关键字对集合进行排序

- Dez79

---

结果没有按照任何东西排序，它们以源列表中找到的顺序返回。您的代码将需要根据字段或排名方法指定排序顺序，使用OrderBy。如果不存在排名字段，则您的代码将需要提供用于排序结果的比较器。 - Panagiotis Kanavos

@PanagiotisKanavos 谢谢，它是一个 SQL Server 数据库。 - Dez79

我想最好从数据库中进行操作。但如果你想继续使用linq，我的建议是创建一组搜索结果，将它们连接在一起，然后去重。例如： myorgs.Where(x => x.Title.Contains(searchText)) .Concat(myorgs.Where(x => searchText.Split(' ').All(con => x.Title.Contains(con)))) .Concat(myorgs.Where(x => searchText.Split(' ').Any(con => x.Title.Contains(con)))) // more .Distinct(); - Malior

你可以使用SQL Server的全文搜索功能。EF Core 2.1支持FreeText，如此SQ问题所示。其他函数可以通过FromSql来使用。 - Panagiotis Kanavos

@Malior，这个程序的唯一工作方式是将所有内容加载到内存中。这无法转换为SQL。如果您将所有内容加载到内存中，最好使用专门的全文搜索库。无论如何，您的查询都不会对结果进行排名。 - Panagiotis Kanavos

Answer 1

摘要：

• 需求：

  • R01 | 标题 | 完全匹配 |顺序
  • R02 | 标题 | 完全匹配 |任意顺序
  • R03 | 标题 | 任意匹配 |
  • R04 | 关键词 | 任意匹配 |
  • R05 | 内容 | 完全匹配 |

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

• 每个 SQL 调用，我们仅返回行 id

• 然后按顺序分组 Id

• 最后进行最终的 SQL 调用

---

步骤 01：R01

• 这里我们将使用EF

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

使用linq2Sql的Contains将被翻译成sql的WHERE IN

步骤02: R02

• 在这里我们将使用纯 SQL WHERE + LIKE + AND

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

步骤03：R03

• 在这里我们将使用普通的 SQL WHERE + OR + And

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

步骤 04：R04

这里我们将使用普通的SQL语句 WHERE + 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将被翻译为sql中的WHERE IN

步骤06 - 分组ID并忽略重复的

• 使用步骤1至5中的行ID

我们将根据检索顺序对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 - 现在我们将使用有序ID获取数据

这里我们将使用纯SQL ORDER BY + CASE WHEN THEN ELSE END 第九步 完整代码 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 注入 什么是 SQL 注入 SQL 注入是一种代码注入技术，用于攻击数据驱动的应用程序，在其中恶意 SQL 语句被插入到一个输入字段中以执行。 注意 01.01：问题 注意 01.02：Microsoft 文档 来自微软文档：如何：直接执行 SQL 查询 | Microsoft Docs 参数在查询文本中使用与 Console.WriteLine() 和 String.Format() 相同的花括号表示法表示。实际上，String.Format() 在提供的查询字符串上被调用，将花括号参数替换为生成的参数名称，例如 @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'酒精',@p1=N'支持' 注意 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 +'%'

- Mohamed Elrashid