Is it possible to use Full Text Search (FTS) with LINQ?

I wonder if is possible to use FTS with LINQ using .NET Framework 3.5. I'm searching around the documentation that I didn't find anything useful yet.

Does anyone have any experience on this?

Yes. However you have to create SQL server function first and call that as by default LINQ will use a like.

This blog post which will explain the detail but this is the extract:

> To get it working you need to create a table valued 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)) > > > You then add this function to your LINQ 2 SQL model and he presto you > can now write queries like. > > var sessList = from s in DB.Sessions join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId select s;

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

That said, you can use a stored procedure that utilizes FTS and have the LINQ To SQL query pull data from that.

if you do not want to create joins and want to simplify your C# code, you can create SQL function and use it in "from" clause:

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 produce simple SQL like this:

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

This is works in search by several columns as you can see from the ad_Search function implementation.

I don't believe so. You can use 'contains' on a field, but it only generates a LIKE query. If you want to use full text I would recommend using a stored proc to do the query then pass it back to LINQ

No, full text searching is something very specific to sql server (in which text is indexed by words, and queries hit this index versus traversing a character array). Linq does not support this, any .Contains() calls will hit the un-managed string functions but will not benefit from indexing.

I made a working prototype, for SQL Server's CONTAINS only and no wildcard columns. What it achieves is for you to use CONTAINS like ordinary 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 by EFProviderWrapperToolkit/EFTracingProvider, because CONTAINS is not a function and by default the generated SQL treats its result as bit.

BUT:

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

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

Source of my prototype

Function Definitions: (EDMX)

Under 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: the weird cases of chars are used to enable the same function with different parameter types (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 as same as the value in edmx:StorageModels/Schema/@Namespace

Rewrite EF SQL: (by 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 by nuget, it should add these lines into 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>