C＃实体框架中用于全文搜索的SQL字符串格式 [英] SQL String format for Full Text Search in C# Entity Framework

问题描述

我正在尝试创建一个参数化的sql字符串，当在数据库服务器上执行该字符串时，要以此结尾，当执行该字符串时，它会返回所需的结果：

I'm trying to create a parameterized sql string which I want to end up as this when executed on the database server, which when executed, returns the desired results:

SELECT * FROM OrderHeader WHERE FacilityId = 9 AND CONTAINS(*,'"Rich*"')

我尝试了以下三种尝试以及结果，但是无法像上面的目标声明一样将其显示出来，结果是附近的语法错误在第一个语句中使用'@ p1'，在后两个语句中使用零。

I've tried the three attempts below and the result, but can't get it to come out like above goal statement and the result is either a syntax error near '@p1' in the first statement, and zero results in the second two statements.

尝试1：

string sql = "SELECT * " +
    "FROM OrderHeader " +
    "WHERE FacilityId = @p0 AND CONTAINS(*,\"@p1*\")";

var orderByCustomerFullTextQuery = base.DbSet.SqlQuery(sql, intFacilityId, strSearchTerm).AsQueryable();

exec sp_executesql N'SELECT * FROM OrderHeader WHERE FacilityId = @p0 AND CONTAINS(*,"@p1*")',N'@p0 int,@p1 nvarchar(4)',@p0=9,@p1=N'Rich'

尝试2：

string sql = "SELECT * " +
    "FROM OrderHeader " +
    "WHERE FacilityId = @p0 AND CONTAINS(*,'\"@p1*\"')";

var orderByCustomerFullTextQuery = base.DbSet.SqlQuery(sql, intFacilityId, strSearchTerm).AsQueryable();

exec sp_executesql N'SELECT * FROM OrderHeader WHERE FacilityId = @p0 AND CONTAINS(*,''"@p1*"'')',N'@p0 int,@p1 nvarchar(4)',@p0=9,@p1=N'Rich'

尝试3：

string sql = "SELECT * " +
    "FROM OrderHeader " +
    "WHERE FacilityId = @p0 AND CONTAINS(*,\'\"@p1*\"\')";

var orderByCustomerFullTextQuery = base.DbSet.SqlQuery(sql, intFacilityId, strSearchTerm).AsQueryable();

exec sp_executesql N'SELECT * FROM OrderHeader WHERE FacilityId = @p0 AND CONTAINS(*,''"@p1*"'')',N'@p0 int,@p1 nvarchar(4)',@p0=9,@p1=N'Rich'

推荐答案

尝试以下操作：

string sql = "SELECT * " +
    "FROM OrderHeader " +
    "WHERE FacilityId = @p0 AND CONTAINS(*,'\"' + @p1 + '*\"')";

其他尝试的问题是 @ p1 @ p1 * 字符串文字中，在 sp_executesql 上下文中评估了c>变量。它不是查询中的元素，只是字符串文字的一部分。我们可以将 @ p1 变量与完整的 @ p1 * 值分开，将其分成三部分：用于引号的文字，仅 @ p1 ，以及用于结尾的通配符和引号的文字。它应该给您等效的条件来执行以下查询：

The problem with the other attempts is the @p1 variable was evaluated in the sp_executesql context as part of a larger "@p1*" string literal. It wasn't an element in the query... just part of a string literal. We can separate the @p1 variable from the full "@p1*" value by breaking this up into three separate pieces: a literal for the leading quote, just the @p1, and a literal for the trailing wildcard and quote. It should give you the equivalent of executing this query:

SELECT * FROM OrderHeader WHERE FacilityId = 9 AND CONTAINS(*,'"' + 'Rich' + '*"')

此操作失败（尽管它确实应该工作），您还可以尝试在将值分配给参数时对其进行调整：

Failing this (though it really should work), you can also try tweaking the values as you assign them to the parameter:

string sql = "SELECT * " +
    "FROM OrderHeader " +
    "WHERE FacilityId = @p0 AND CONTAINS(*, @p1)";

...SqlQuery(sql, intFacilityId, "\"" + strSearchTerm + "*\"").AsQueryable();

这篇关于C＃实体框架中用于全文搜索的SQL字符串格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！