Write ICriteria for CONTAINSTABLE (sql fulltext search)

Question

I need to do fulltext search over multiple tables, so i use SqlServer CONTAINSTABLE function, it works fine as a SQL query, but how can i rewrite CONTAINSTABLE into 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]

I need to convert this SQL to NHibernate Criteria, can somebody help me?

score 5 · Answer 1 · edited May 23 '17 at 12:32

The same problem which i also got

NHibernate + SqlServer full text search

and i didn't got any solution so i went for interceptor concept where

1)I created detached criteria for main table and joined the full text table

2)Also created a custom interceptor for nhibernate which will replace full text table name with (select [key] as foreignkey,[rank] as rank FROM CONTAINSTABLE(full_text,full_text_col , 'Foo*'))

Code

Generated query

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

Query after interception

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

Interceptor code

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;
    }
}

Entity

candidtes table

id int

name string

fulltext_id

full_text table contains full text index

id int

full_text_col text

rank int //always null

Relation

Candidate - FullText (1-1)

OpenSession

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

@chuck-see edited code-at present i don't have exact code(which is in office) any way this method is an exact solution its a hack — Anand, Dec 24 '11 at 17:24
I have a lot of tables where I search using fulltext, and sometimes I search into multiple tables at once (join), should I do a dummy table /interceptor for each table where I have to search ? — Omu, Dec 24 '11 at 18:58
no you have to use only one dummy table for fulltext result return value.ie (full_text id int key int)which table always contain no value and add an entity FullText with properties of Id and Key — Anand, Dec 25 '11 at 04:15
this dummy table has to have a foreign key to each table where I do search (I see you have "candidate_Id") ? — Omu, Dec 26 '11 at 17:06
@ChuckNorris-Here i shared the doc which contains query of actual nhibernate generated query and query edited by interceptor.I think by looking this query you may get some idea.Also i specified schema for two table (main table which will be joined to multiple table and full text table where i configured full text index).And with this you can search multiple table at once(join) bcoz same thing im doing.Check the link http://www.box.com/s/7if894sr9piz41go7ctz — Anand, Dec 27 '11 at 05:08
Your example is good, but is SQL Injection Attacks vulnerable. — Omu, Dec 29 '11 at 12:45
I am processing the full text string before executing the query for some sql server requirements like remove sql noise words and some alignment blah..blah..So 0% possibility of sql injection — Anand, Dec 29 '11 at 14:37

score 2 · Answer 2 · edited Jan 10 '16 at 21:32

2

You can use named queries

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>

edited Jan 10 '16 at 21:32

Owen Pauling

11,349
20
53
64

answered Dec 23 '11 at 18:56

Vadim

17,897
4
38
62

I need to do this using ICriteria not SQL – Omu Dec 23 '11 at 19:03
1

The criteria API only allows you to use your entities in your joins. You can't specify a table valued function as a source for an entity. Hence, it is not supported. You can try the other solution using Interceptors, but it's unlikely to be very stable imo. – Vadim Dec 30 '11 at 18:41

Write ICriteria for CONTAINSTABLE (sql fulltext search)

2 Answers2

Code