Write ICriteria for CONTAINSTABLE (full-text sql search)

Question

I need to do full-text searches across multiple tables, so I use the SqlServer CONTAINSTABLE function, it works fine as an SQL query, but how can I rewrite CONTAINSTABLE in 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 someone help me?

Answer 1

The same thing that I got and

Full Text Search NHibernate + SqlServer

and I had no solution, so I went for the interceptor concept, where

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

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

code

Generated request

 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 

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

An object

revelation table

id int

name string

fulltext_id

full table contains full text index

id int

Full text

rank int // always null

Communication

Candidate - FullText (1-1)

Open session

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

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