I want to share my crutch in solving a rather trivial problem: how to make friends full-text search MSSQL with the Entity Framework. The topic is very highly specialized, but it seems to me that it is relevant today. I ask those interested under the cat.

It all started with pain

I develop projects on C # (ASP.NET) and sometimes I write microservices. In most cases, I use the MSSQL database to work with data. The Entity Framework is used as a link between the database and my project. With EF, I get ample opportunities to work with data, form the right requests, manage the load on the server. The magic mechanism of LINQ simply fascinates with its capabilities. Years later, I no longer imagine faster and more convenient ways to work with the database. But like almost any ORM, EF has a number of drawbacks. Firstly, this is performance, but this is a topic for a separate article. And secondly, it is the coverage of the database itself.

MSSQL has a built-in full-text search that works out of the box. To perform full-text queries, you can use built-in predicates (CONTAINS and FREETEXT) or functions (CONTAINSTABLE and FREETEXTTABLE). There is only one problem: EF does not support full-text queries, from the word at all!

I will give an example from real experience. Suppose I have a table of articles - Article, and I create for it a class describing this table:

/// 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; } } 

Then I need to make a selection of these articles, say, to display the last 10 published articles:

 /// c# dbEntities db = new dbEntities(); var articles = db.Article .Where(n => n.Active) .OrderByDescending(n => n.Date) .Take(10) .ToArray(); 

Everything is very beautiful until the task appears to add a full-text search. Since there is no support for full-text selection functions in EF (in the .NET core 2.1 already partially exists ), it remains to either use some third-party library or write a query in pure SQL.

The SQL query from the example above is not so complicated:

 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 

In real projects, things are not so simple. Queries to the database are an order of magnitude more difficult and manual maintenance is difficult and time consuming. As a result, the first time I wrote a query using LINQ, then I took out the generated SQL text of the query to the database, and I already implemented full-text data retrieval conditions into it. Then I sent it to db.Database.SqlQuery and got the data I needed. This is all well and good as long as the query does not need to hang a dozen different filters with complex join-us and conditions.

So - I have a specific pain. We must solve it!

Looking for a solution

Once again, sitting in my favorite search in the hope of finding at least some solution, I came across this repository . With this solution, predicate support (CONTAINS and FREETEXT) can be implemented in LINQ. Thanks to the support of the EF 6 special interface IDbCommandInterceptor , which allows you to intercept a ready-made SQL query, this solution was implemented before sending it to the database. In the Contains field, a special generated string is inserted into the string, and then after the query is generated, this place is replaced with the predicate. Example:

 /// c# var text = FullTextSearchModelUtil.Contains("code"); db.Tables.Where(c=>c.Fullname.Contains(text)); 

However, if the data selection needs to be sorted by the rank of matches, then this solution will no longer work and you will have to write a SQL query manually. In fact, this solution replaces the usual LIKE with a sample by predicate.

So, at this stage I had a question: is it possible to implement real full-text search using the built-in MS SQL functions (CONTAINSTABLE and FREETEXTTABLE) so that all this is generated via LINQ and with support for sorting the query by the rank of matches? As it turned out, you can!

Implementation

To begin with, it was necessary to develop the logic of writing the query itself using LINQ. Since in real SQL queries with full-text selections, JOIN is most often used to attach a virtual table with ranks, I decided to follow the same path in the LINQ query.

Here is an example of such a LINQ query:

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

Such code could not be compiled yet, but it already visually solved the problem of sorting the resulting data by rank. It remained to implement it in practice.

Additional class FTS_Int used in this query:

 /// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } } 

The name was not chosen by chance, since the key column in this class must match the key column in the search table (in my example with [Article].[Id] type int ). In case you need to make queries on other tables with other types of key columns, I assumed just to copy a similar class and create its Key of the type you need.

The condition for the formation of a full-text query was supposed to be passed in the variable queryText . For the formation of the text of this variable a separate function was implemented:

 /// c# string queryText = FtsSearch.Query( dbContext: db, //   ,       ftsEnum: FtsEnum.CONTAINS, //  : CONTAINS  FREETEXT tableQuery: typeof(News), //       tableFts: typeof(FTS_Int), //    search: "text"); //    

Execution of a ready request and data acquisition:

 /// c# var result = FtsSearch.Execute(() => query.ToList()); 

The last function FtsSearch.Execute wrapper is used to temporarily connect the IDbCommandInterceptor interface. In the example given by the link above, the author chose to use the query substitution algorithm all the time for requests. As a result, after the replacement of the request replacement mechanism, each request is searched for the necessary replacement combination. I found this option wasteful, so the execution of the data request itself is performed in the function that is passed, which before the call turns on the query autochange, and after the call it turns it off.

Application

I use autogeneration of data model classes from a database using an edmx file. Since the FTS_Int class just created cannot be used in EF due to the lack of necessary metadata in DbContext , I created a real table based on its model (maybe someone knows a better way, I will be glad for your help in the comments):

Screenshot of the table created in the edmx file

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

After that, when updating the edmx file from the database, we add the created table and get its generated class:

 /// c# public partial class FTS_Int { public int Key { get; set; } public int Rank { get; set; } public string Query { get; set; } } 

Queries to this table will not be maintained, it is only needed to correctly form the metadata for creating the query. The final example of using full-text database query is:

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

There is also support for asynchronous requests:

 /// c# var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync()); 

SQL query formed before autochange:

 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 query formed after autochange:

 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 

By default, full-text search works on all columns of the table:

 CONTAINSTABLE([dbo].[Article],(*),'text') 

If you need to make a selection only for some fields, you can specify them in the fields parameter of the FtsSearch.Query function.

Total

The result is full-text search support in LINQ.

The nuances of this approach.

1. The search parameter in the FtsSearch.Query function FtsSearch.Query not use any checks or wrappers to protect against SQL injections. The value of this variable is passed as is to the query text. If you have any ideas about this, write in the comments. I used a regular regular expression that simply removes all characters other than letters and numbers.

   
   

2. You also need to take into account the features of constructing expressions for full-text queries. Parameter to function

   
   

    /*    */ CONTAINSTABLE([dbo].[News],(*),' ') 

   
   

   It has an invalid format because MS SQL requires the separation of words by logical literals. In order for the request to be executed successfully, you need to fix it like this:

   
   

    /*   */ CONTAINSTABLE([dbo].[News],(*),' and ') 

   
   

   or change the data retrieval function

   
   

    /*   */ FREETEXTTABLE([dbo].[News],(*),' ') 

   
   

   For more information about the features of creating requests, it is better to refer to the official documentation .

   
   

3. Standard logging with this solution does not work correctly. For this, a special logger was added:

   
   

    /// c# db.Database.Log = (val) => Console.WriteLine(val); 

   
   

   If you look at the generated query to the database, it will be generated before processing by the autochange function.

   
   

During testing, I also checked on more complex queries with multiple samples from different tables and there were no problems here.

Sources on GitHub