是否可以使用全文搜索(FTS)与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.

我想知道是否可以使用。net Framework 3.5使用LINQ的FTS。我正在查找我没有找到有用的文档。

Does anyone have any experience on this?

有人有这方面的经验吗?

6 个解决方案

#1

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

是的。但是，您必须首先创建SQL server函数，并将其称为默认的LINQ将使用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.

然后将这个函数添加到LINQ 2 SQL模型中，然后您就可以像这样编写查询了。

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

#2

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

不。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.

也就是说，您可以使用一个使用FTS的存储过程，并使用LINQ到SQL查询提取数据。

#3

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

我不这么认为。您可以在字段上使用“contains”，但它只生成一个LIKE查询。如果您想使用全文，我建议使用存储的proc来执行查询，然后将其传回LINQ

#4

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:

如果您不想创建连接并想简化您的c#代码，您可以创建SQL函数并在“from”子句中使用它:

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:

更新DBML后，在linq中使用:

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

This will produce simple SQL like this:

这将产生这样的简单SQL:

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.

从ad_Search函数实现中可以看到，这可以通过几个列进行搜索。

#5

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.

不，全文搜索是非常特定于sql server的东西(在其中，文本是由单词索引的，而查询则按此索引而不是遍历字符数组)。Linq不支持这一点，任何. contains()调用都会攻击非托管字符串函数，但不会从索引中获益。

#6

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:

我制作了一个可用的原型，因为SQL Server只包含而没有通配符列。它所实现的是您使用的包含普通LINQ函数:

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.

1。函数定义在代码和EDMX中支持CONTAINS关键字。

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

2。使用EFProviderWrapperToolkit/EFTracingProvider重写EF SQL，因为CONTAINS不是函数，默认情况下生成的SQL将其结果作为位处理。

BUT:

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

1。包含并不是一个真正的函数，你不能从它中选择布尔结果。它只能在条件下使用。

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

2。如果查询包含具有特殊字符的非参数化字符串，下面的SQL重写代码可能会出错。

Source of my prototype

Function Definitions: (EDMX)

Under edmx:StorageModels/Schema

在edmx:StorageModels /模式

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

PS: chars的奇怪情况被用来启用具有不同参数类型的相同函数(varbinary和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

PS:“MyModel。Store“与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:

如果你通过nuget得到它，它应该将这些行添加到你的app.config或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>