我想知道是否可以在使用.NET Framework 3.5时,使用LINQ来进行全文搜索(FTS)。我已经查阅了文档,但没有找到有用的信息。
有人有相关经验吗?
我想知道是否可以在使用.NET Framework 3.5时,使用LINQ来进行全文搜索(FTS)。我已经查阅了文档,但没有找到有用的信息。
有人有相关经验吗?
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;
不支持使用LINQ To SQL进行全文搜索。
话虽如此,您可以使用利用全文搜索的存储过程,并让LINQ To SQL查询从中提取数据。
如果您不想创建连接并且想简化 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))
)
更新 DBML 后,将其用于 Linq 查询:
string searchKeyword = "word and subword";
var result = from ad in context.ad_Search(searchKeyword)
select ad;
这将生成类似于以下的简单SQL:
SELECT [t0].ID, [t0].Title, [t0].Description
FROM [dbo].[ad_Search](@p0) AS [t0]
正如您从ad_Search函数实现中可以看到的那样,这在搜索多列时有效。
我不这么认为。你可以在字段上使用“contains”关键词,但这只会生成一个LIKE
查询。如果你想使用全文搜索,我建议使用存储过程来执行查询,然后将其传递回LINQ。
不,全文搜索是SQL Server中非常特定的功能(其中文本通过单词索引,并且查询命中此索引而不是遍历字符数组)。LINQ不支持这一点,任何.Contains()调用都会命中未经管理的字符串函数,但不会从索引中受益。
我制作了一个工作原型,仅适用于 SQL Server 的 CONTAINS 和没有通配符列。它实现的功能是让您像普通的 LINQ 函数一样使用 CONTAINS:
var query = context.CreateObjectSet<MyFile>()
.Where(file => file.FileName.Contains("pdf")
&& FullTextFunctions.ContainsBinary(file.FileTable_Ref.file_stream, "Hello"));
1.在代码和EDMX中定义函数以支持CONTAINS关键字。
2.通过EFProviderWrapperToolkit/EFTracingProvider重写EF SQL,因为CONTAINS不是一个函数,并且默认情况下生成的SQL将其结果视为bit。
1.Contains实际上不是一个函数,您无法从中选择布尔结果。它只能用于条件语句。
2.如果查询包含带有特殊字符的非参数化字符串,则下面的SQL重写代码很可能会出错。
在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>
备注:字符的奇怪用法是为了使相同的函数能够使用不同的参数类型(varbinary 和 nvarchar)
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.");
}
}
提示:"MyModel.Store" 与 edmx:StorageModels/Schema/@Namespace 中的值相同
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;
}
}
如果您通过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>