Can I use full-text search (FTS) with LINQ?

Question

I wonder if it is possible to use FTS with LINQ using the .NET Framework 3.5. I am looking through the documentation that I have not found anything useful yet.

Does anyone have any experience with this?

Answer 1

Yes. However, you must first create a SQL server function and call this, because LINQ will use this by default.

This blog post will explain the details, but this is an excerpt:

To make it work, you need to create a table function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,

create function udf_sessionSearch (@keywords nvarchar(4000)) returns table as return (select [SessionId],[rank] from containstable(Session,(description,title),@keywords)) 

Then you add this function to your LINQ 2 SQL model, and it teaches you can now write type queries.

  var sessList = from s in DB.Sessions join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId select s; 

Answer 2

No. Full text search is not supported by LINQ To SQL.

However, you can use a stored procedure using FTS and get the LINQ To SQL query from this.

Answer 3

I don’t believe that. You can use 'contains' in the field, but it only generates a LIKE request. If you want to use the full text, I would recommend using the stored proc to execute the request and then pass it LINQ

Answer 4

If you do not want to create connections and want to simplify your C # code, you can create an SQL function and use it in the "from" section:

 CREATE FUNCTION ad_Search ( @keyword nvarchar(4000) ) RETURNS TABLE AS RETURN ( select * from Ad where (CONTAINS(Description, @keyword) OR CONTAINS(Title, @keyword)) ) 

After updating your DBML, use it in linq:

 string searchKeyword = "word and subword"; var result = from ad in context.ad_Search(searchKeyword) select ad; 

This will result in simple SQL like this:

 SELECT [t0].ID, [t0].Title, [t0].Description FROM [dbo].[ad_Search](@p0) AS [t0] 

This works in multiple column searches, as you can see from the ad_Search function implementation.

Answer 5

No, full-text search is something very specific to the SQL server (in which text is indexed by words, and queries fall into this index compared to moving an array of characters). Linq does not support this, any .Contains () calls will fall into unmanaged string functions, but will not be useful for indexing.

Answer 6

I created a working prototype for SQL Server CONTAINS only and without wildcard columns. What it achieves is to use CONTAINS, like normal LINQ functions:

 var query = context.CreateObjectSet<MyFile>() .Where(file => file.FileName.Contains("pdf") && FullTextFunctions.ContainsBinary(file.FileTable_Ref.file_stream, "Hello")); 

You will need:

1. Function definitions in code and EDMX to support the CONTAINS keyword.

2.Rewrite EF SQL using EFProviderWrapperToolkit / EFTracingProvider, because CONTAINS is not a function, and by default the generated SQL treats its result as a bit.

BUT:

1.Contains is not really a function, and you cannot choose logical results from it. It can only be used in conditions.

2. The SQL rewrite code below is likely to break if the queries contain non-parameterized strings with special characters.

Source of my prototype

Function Definitions: (EDMX)

In edmx: StorageModels / Schema

 <Function Name="conTAINs" BuiltIn="true" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" ReturnType="bit" Schema="dbo"> <Parameter Name="dataColumn" Type="varbinary" Mode="In" /> <Parameter Name="keywords" Type="nvarchar" Mode="In" /> </Function> <Function Name="conTAInS" BuiltIn="true" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" ReturnType="bit" Schema="dbo"> <Parameter Name="textColumn" Type="nvarchar" Mode="In" /> <Parameter Name="keywords" Type="nvarchar" Mode="In" /> </Function> 

PS: strange cases of characters are used to enable the same function with different types of parameters (varbinary and nvarchar)

Function Definitions: (code)

 using System.Data.Objects.DataClasses; public static class FullTextFunctions { [EdmFunction("MyModel.Store", "conTAINs")] public static bool ContainsBinary(byte[] dataColumn, string keywords) { throw new System.NotSupportedException("Direct calls are not supported."); } [EdmFunction("MyModel.Store", "conTAInS")] public static bool ContainsString(string textColumn, string keywords) { throw new System.NotSupportedException("Direct calls are not supported."); } } 

PS: "MyModel.Store" is the same as the value in edmx: StorageModels / Schema / @ Namespace

Rewrite EF SQL: (using EFProviderWrapperToolkit)

 using EFProviderWrapperToolkit; using EFTracingProvider; public class TracedMyDataContext : MyDataContext { public TracedMyDataContext() : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers( "name=MyDataContext", "EFTracingProvider")) { var tracingConnection = (EFTracingConnection) ((EntityConnection) Connection).StoreConnection; tracingConnection.CommandExecuting += TracedMyDataContext_CommandExecuting; } protected static void TracedMyDataContext_CommandExecuting(object sender, CommandExecutionEventArgs e) { e.Command.CommandText = FixFullTextContainsBinary(e.Command.CommandText); e.Command.CommandText = FixFullTextContainsString(e.Command.CommandText); } private static string FixFullTextContainsBinary(string commandText, int startIndex = 0) { var patternBeg = "(conTAINs("; var patternEnd = ")) = 1"; var exprBeg = commandText.IndexOf(patternBeg, startIndex, StringComparison.Ordinal); if (exprBeg == -1) return commandText; var exprEnd = FindEnd(commandText, exprBeg + patternBeg.Length, ')'); if (commandText.Substring(exprEnd).StartsWith(patternEnd)) { var newCommandText = commandText.Substring(0, exprEnd + 2) + commandText.Substring(exprEnd + patternEnd.Length); return FixFullTextContainsBinary(newCommandText, exprEnd + 2); } return commandText; } private static string FixFullTextContainsString(string commandText, int startIndex = 0) { var patternBeg = "(conTAInS("; var patternEnd = ")) = 1"; var exprBeg = commandText.IndexOf(patternBeg, startIndex, StringComparison.Ordinal); if (exprBeg == -1) return commandText; var exprEnd = FindEnd(commandText, exprBeg + patternBeg.Length, ')'); if (exprEnd != -1 && commandText.Substring(exprEnd).StartsWith(patternEnd)) { var newCommandText = commandText.Substring(0, exprEnd + 2) + commandText.Substring(exprEnd + patternEnd.Length); return FixFullTextContainsString(newCommandText, exprEnd + 2); } return commandText; } private static int FindEnd(string commandText, int startIndex, char endChar) { // TODO: handle escape chars between parens/squares/quotes var lvlParan = 0; var lvlSquare = 0; var lvlQuoteS = 0; var lvlQuoteD = 0; for (var i = startIndex; i < commandText.Length; i++) { var c = commandText[i]; if (c == endChar && lvlParan == 0 && lvlSquare == 0 && (lvlQuoteS % 2) == 0 && (lvlQuoteD % 2) == 0) return i; switch (c) { case '(': ++lvlParan; break; case ')': --lvlParan; break; case '[': ++lvlSquare; break; case ']': --lvlSquare; break; case '\'': ++lvlQuoteS; break; case '"': ++lvlQuoteD; break; } } return -1; } } 

Enable EFProviderWrapperToolkit:

If you get it via nuget, it should add these lines to your app.config or web.config:

 <system.data> <DbProviderFactories> <add name="EFTracingProvider" invariant="EFTracingProvider" description="Tracing Provider Wrapper" type="EFTracingProvider.EFTracingProviderFactory, EFTracingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" /> <add name="EFProviderWrapper" invariant="EFProviderWrapper" description="Generic Provider Wrapper" type="EFProviderWrapperToolkit.EFProviderWrapperFactory, EFProviderWrapperToolkit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" /> </DbProviderFactories> </system.data>