我想分享我的拐杖，以解决一个相当平凡的问题：如何用Entity Framework交朋友全文MSSQL搜索。 该主题非常专业，但在我看来今天很重要。 对于那些感兴趣的人，我要猫。
一切始于痛苦
我用C＃（ASP.NET）开发项目，有时编写微服务。 在大多数情况下，我使用MSSQL数据库来处理数据。 实体框架用作数据库和我的项目之间的链接。 使用EF，我有很多机会来处理数据，生成正确的查询，调节服务器上的负载。 神奇的LINQ机制简单地吸引了它的功能。 多年来，我不再想像使用数据库的更快，更方便的方法。 但是，与几乎所有ORM一样，EF也有许多缺点。 首先，这是性能，但这是另一篇文章的主题。 其次，它涵盖了数据库本身的功能。
MSSQL具有内置的全文本搜索功能，可以直接使用。 要执行全文查询，可以使用内置谓词（CONTAINS和FREETEXT）或函数（CONTAINSTABLE和FREETEXTTABLE）。 只有一个问题：EF根本不支持全文查询！
我将以实际经验为例。 假设我有一个文章表-Article，并且为此创建了一个描述该表的类：
/// c# public partial class Article { public int Id { get; set; } public System.DateTime Date { get; set; } public string Text { get; set; } public bool Active { get; set; } }
然后，我需要对这些文章进行选择，例如，输出最近发表的10篇文章：
/// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray();
直到出现添加全文本搜索的任务之前，一切都非常美好。 由于EF（.NET core 2.1已经部分支持EF）不支持全文选择功能，因此它仍然可以使用某些第三方库或使用纯SQL编写查询。
上例中的SQL查询并不那么复杂：
SELECT TOP (10) [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active] FROM [dbo].[Article] AS [Extent1] WHERE [Extent1].[Active] = 1 ORDER BY [Extent1].[Date] DESC
在实际的项目中，事情并不是那么简单。 对数据库的查询要复杂得多，并且难以手动维护。 结果，我第一次使用LINQ编写查询，然后将SQL查询的生成文本输入数据库，并已经向其中引入了全文数据选择条件。 然后，将其发送到db.Database.SqlQuery并接收所需的数据。 当然，这一切都很好，只要请求不需要挂起十几个具有复杂联接和条件的不同过滤器即可。
所以-我有特定的痛苦。 我们必须解决它！
寻找解决方案
再次坐在我最喜欢的搜索中，希望找到至少一些解决方案，我遇到了这个存储库 。 使用此解决方案，可以实现LINQ谓词支持（CONTAINS和FREETEXT）。 得益于特殊IDbCommandInterceptor接口EF 6的支持，该接口允许您截取完成的SQL查询，因此在将其发送到数据库之前已实现了此解决方案。 生成的特殊标记字符串被替换为Contains字段，然后在生成请求后，将该地方替换为谓词Example：
/// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text));
但是，如果需要根据匹配程度对数据选择进行排序，则此解决方案将不再适用，您将必须手动编写SQL查询。 本质上，此解决方案用谓词选择代替了常规的LIKE。
因此，在这个阶段，我有一个问题：是否可以使用内置的MS SQL函数（CONTAINSTABLE和FREETEXTTABLE）实现真正的全文搜索，以便所有这些都可以通过LINQ生成，甚至支持按匹配程度对查询进行排序？ 事实证明，您可以！
实作
首先，必须开发使用LINQ编写查询本身的逻辑。 由于在带有全文选择的实际SQL查询中，JOIN最常用于连接具有排名的虚拟表，因此我决定在LINQ查询中采用相同的方法。
这是这种LINQ查询的示例：
/// c# var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText)); var query = db.Article .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new { article.Id, article.Text, fts.Key, fts.Rank, }) .OrderByDescending(n => n.Rank);
这样的代码尚无法编译，但已经在视觉上解决了按等级对结果数据进行排序的问题。 有待实践。
此请求中使用的其他类FTS_Int ：
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
该名称不是偶然选择的，因为此类中的关键列应与搜索表中的关键列在滴答处重合（在我的示例中为[Article].[Id]类型int ）。 万一您需要使用其他类型的键列对其他表进行查询，我假定只复制一个相似的类并创建所需类型的键。
应当在queryText变量中传递形成全文查询的queryText 。 为了形成该变量的文本，实现了一个单独的函数：
/// c# string queryText = FtsSearch.Query( dbContext: db, // , ftsEnum: FtsEnum.CONTAINS, // : CONTAINS FREETEXT tableQuery: typeof(News), // tableFts: typeof(FTS_Int), // search: "text"); //
完成准备请求和数据获取：
/// c# var result = FtsSearch.Execute(() => query.ToList());
最后的FtsSearch.Execute包装函数用于临时连接IDbCommandInterceptor接口。 在以上链接提供的示例中，作者更喜欢对所有请求始终使用查询替换算法。 结果，在连接查询替换机制后，每个请求都会搜索必要的替换组合。 此选项对我来说似乎很浪费，因此，数据请求本身的执行是在传输的函数中执行的，该函数在调用之前连接查询自动替换，而在调用之后断开查询的自动替换。
申请书
我正在使用使用edmx文件从数据库自动生成数据模型类。 由于由于FTS_Int中缺少必要的元数据，您根本无法在EF中使用创建的FTS_Int类，因此我根据其模型创建了一个真实的表（也许有人知道更好的方法，对于您在注释中的帮助，我将感到高兴）：
在edmx文件中创建的表的屏幕截图
CREATE TABLE [dbo].[FTS_Int] ( [Key] INT NOT NULL, [Rank] INT NOT NULL, [Query] NVARCHAR (1) NOT NULL, CONSTRAINT [PK_FTS_Int] PRIMARY KEY CLUSTERED ([Key] ASC) );
之后，从数据库更新edmx文件时，添加创建的表并获取其生成的类：
/// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } }
将不对该表进行任何查询；仅需要该表，以便正确形成元数据来创建查询。 使用全文数据库查询的最后一个示例：
/// c# string queryText = FtsSearch.Query( dbContext: db, ftsEnum: FtsEnum.CONTAINS, tableQuery: typeof(Article), tableFts: typeof(FTS_Int), search: "text"); var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText)); var query = db.Article .Where(n => n.Active) .Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new { article, fts.Rank, }) .OrderByDescending(n => n.Rank) .Take(10) .Select(n => n.article); var result = FtsSearch.Execute(() => query.ToList());
还支持异步请求：
/// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
自动更正之前生成的SQL查询：
SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Date] AS [Date], [Project1].[Text] AS [Text], [Project1].[Active] AS [Active] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active], [Extent2].[Rank] AS [Rank] FROM [dbo].[Article] AS [Extent1] INNER JOIN [dbo].[FTS_Int] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key] WHERE ([Extent1].[Active] = 1) AND ([Extent2].[Query] LIKE @p__linq__0 ESCAPE N'~') ) AS [Project1] ORDER BY [Project1].[Rank] DESC
自动更正后生成的SQL查询：
SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Date] AS [Date], [Project1].[Text] AS [Text], [Project1].[Active] AS [Active] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Text] AS [Text], [Extent1].[Active] AS [Active], [Extent2].[Rank] AS [Rank] FROM [dbo].[Article] AS [Extent1] INNER JOIN CONTAINSTABLE([dbo].[Article],(*),'text') AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key] WHERE ([Extent1].[Active] = 1) AND (1=1) ) AS [Project1] ORDER BY [Project1].[Rank] DESC
默认情况下，全文搜索适用于表的所有列：
CONTAINSTABLE([dbo].[Article],(*),'text')
如果只需要选择某些字段，则可以在FtsSearch.Query函数的fields参数中指定它们。
合计
结果是LINQ支持全文搜索。
这种方法的细微差别。
FtsSearch.Query函数FtsSearch.Query的search参数不使用任何检查或包装程序来防止SQL注入。 该变量的值按原样传递给请求文本。 如果对此有任何想法，请在评论中写下。 我使用了通常的正则表达式，该正则表达式只是删除了字母和数字以外的所有字符。
您还需要考虑为全文查询构建表达式的功能。 功能参数
CONTAINSTABLE([dbo].[News],(*),' ')
它的格式无效，因为MS SQL要求用逻辑文字分隔单词。 为了成功完成请求，您需要像这样修复它：
CONTAINSTABLE([dbo].[News],(*),' and ')
或更改数据检索功能
FREETEXTTABLE([dbo].[News],(*),' ')
有关创建查询功能的更多信息，最好参考官方文档 。
使用此解决方案的标准日志记录无法正常工作。 为此添加了一个特殊的记录器：
/// c# db.Database.Log = (val) => Console.WriteLine(val);
如果查看生成的数据库查询，则将在处理自动替换功能之前生成查询。
在测试期间，我检查了更复杂的查询，并从不同的表中进行了多个选择，并且没有问题。
GitHub来源