Nhibernate (or fluent) doesn’t support full text searching because full text search differs from one database to another. Nhibernate allows you to switch your data store (mysql, ms sql server, oracle etc) easily and if it were to support full text searching, then it will tie itself to the sql required to perform full text search for a particular database. So you need to have a workaround to get full text search working with fluent nhibernate. Here’s my solution:
public virtual IList<RelatedArticle> RelatedArticles(ISession session)
{
IQuery query = session.CreateQuery("select new RelatedArticle(A.Title, A.Slug, A.Description, C.Slug) from Article as A inner join A.Category as C WHERE CONTAINS((A.Subject, A.Content), :title)");
query.SetParameter("title", "\"" + Title + "\"");
// no more than 5 related articles
return query.SetMaxResults(5).List<RelatedArticle>();
}
This works fine but contains does not give me the option to sort out the result by rank, that is, the most relevant result first. Note that RelatedArticle is an unrelated class mapped using fluent nhibernate to Article class and that by using session.CreateQuery, the query needs to be written in HQL (Hibernate Query Language) and not native SQL. To be able to do that now, I have to use CONTAINSTABLE or FREETEXTTABLE (I’m choosing freetexttable because I don’t need the exact phrase but something related to that). However FREETEXTTABLE in Microsoft SQL Server will return a table and you can no longer use HQL but have to resort to native SQL using session.CreateSQLQuery and this is where the code will fail. Since I’m mapped RelatedArticle using ImportType<RelatedArticle> in ArticleMap class, nhibernate complains withe the following error message “No persister for: RelatedArticle”. What this means is that it is having difficulty finding the mapping for the RelatedArticle class. My solution was as follows:
public virtual IList<RelatedArticle> RelatedArticles(ISession session)
{
IQuery query = session.CreateSQLQuery("select A.Title, A.Slug, A.Description, C.Slug AS CategorySlug from Article A inner join Category C ON C.Id = A.CategoryId INNER JOIN FREETEXTTABLE (Article, (Subject, Content), :title, 10) AS ktl ON A.Id = ktl.[KEY] WHERE ORDER BY ktl.RANK DESC").SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(RelatedArticle)));
query.SetParameter("title", Subject);
return query.SetMaxResults(5).List<RelatedArticle>();
}
I’m not sure why CreateSQLQuery is having problems while HQL could execute the code without complaining though but I ain’t got time to sort it out yet. As long as the above is working, I suppose I can come back to it later.