为CONTAINSTABLE编写ICriteria (sql全文搜索)

Question

我需要对多个表进行全文搜索，所以我使用了SqlServer CONTAINSTABLE函数，它作为一个SQL查询工作得很好，但是我如何才能将CONTAINSTABLE重写为NHibernate ICriteria.

SELECT DISTINCT CP.* FROM ContentPack CP
    INNER JOIN [Content] C ON CP.ContentPackId = C.ContentPackId

 INNER JOIN
     CONTAINSTABLE([Content], (Title, [Description]), 'Foo*') AS KEY_TBL
     ON C.Title = KEY_TBL.[KEY]

我需要把这个SQL转换成NHibernate ，有人能帮我吗？

Answer 1

我也遇到了同样的问题

NHibernate + SqlServer full text search

我没有任何解决方案，所以我选择了拦截器的概念

1)我为主表创建了独立的条件，并加入了全文表。

2)还为nhibernate创建了一个自定义拦截器，它将用(select [key] as foreignkey,[rank] as rank FROM CONTAINSTABLE(full_text,full_text_col , 'Foo*'))替换全文表名。

代码

生成查询

SELECT c.id,
        c.name,
        ft.id, 
        ft.rank 
FROM candidates c 
    INNER JOIN full_text ft ON ft.id  = c.fulltext_id 
ORDER BY rank

截取后查询

SELECT c.id,
        c.name,
        ft.id,
        ft.rank 
FROM candidates c 
    INNER JOIN (
            SELECT [KEY] AS id ,[rank] AS rank 
            FROM CONTAINSTABLE(full_text, full_text_col, 'Foo*')
               ) AS ft ON ft.id  = c.fulltext_id 
ORDER BY rank

*DetachedCriteria*

        DetachedCriteria candidateCriteria = DetachedCriteria.For<Candidate>();
        DetachedCriteria fullTextCriteria = candidateCriteria.CreateCriteria("FullText");  

拦截器码

public interface CustomInterceptor : IInterceptor, EmptyInterceptor
{
    private string fulltextString;
    public string FulltextString
    {
        get { return fulltextString; }
        set { fulltextString = value; }
    }
    SqlString IInterceptor.OnPrepareStatement(SqlString sql)
    {
        string query = sql.ToString();
        if (query.Contains("full_text"))
        {
            sql = sql.Replace("full_text", "(select [key] as foreignkey,[rank] as Rank FROM CONTAINSTABLE(full_text, full_text_col, '"+FulltextString+"')) AS ft'");
        }
        return sql;
    }
}

实体

候选表格

id int

名称字符串

fulltext_id

full_text表包含全文索引

id int

full_text_col文本

秩int //始终为空

关系

候选人- FullText (1-1)

OpenSession

CustomInterceptor custonInterceptor=new CustomInterceptor();
custonInterceptor.FulltextString="YourString";
sessionFactory.OpenSession(custonInterceptor);

Answer 2

您可以使用命名查询。

http://nhibernate.info/blog/2009/04/16/nhibernate-mapping-named-queries-lt-query-gt-and-lt-sql-query-gt.html

<sql-query name="MyQuery">
    <return alias="cp"
                    class="ContentPack"/>
    SELECT DISTINCT {cp.*}
    INNER JOIN [Content] C ON CP.ContentPackId = C.ContentPackId
    INNER JOIN
     CONTAINSTABLE([Content], (Title, [Description]), 'Foo*') AS KEY_TBL
     ON C.Title = KEY_TBL.[KEY]
</sql-query>