CODEWITHSUNDEEP
nhibernatesql-server-2008fluent-nhibernate
cmv
cmv

Reputation: 956

NHibernate cannot use results of containstable() query

I'm in the process of converting a LINQ2SQL project to use NHibernate. One query that I have not been able to port effectively is a full-text search. Previously I was using a stored procedure to accomplish the search but now I'm accessing it as a query directly. The SQL query is:

select a.id, a.parentID, a.text, a.summary
from articles a
    inner join containstable(articles, (summary, text), :query) ar on
        a.id = ar.[KEY]
order by ar.RANK desc

When I run the query in SSMS I get the results I expect - just the matching items of the articles table. However, NHibernate does not seem to be able to interpret the results. The code I query with is:

IQuery q = session.CreateSQLQuery(
            "select a.id, a.parentID, a.[text], a.summary from articles a "
        +   "inner join containstable(articles, (summary, text), :query) ar on "
        +   "a.id = ar.[KEY] "
        +   "order by ar.RANK desc")
    .SetParameter<string>("query", query);

var results = q.List<article>();

NHibernate throws an error that System.Object[] cannot be converted to my POCO type. I tried the non-generic List() method and discovered that each item in that list was an array of objects. Other queries on the table work fine, however this is the only SQL query (everything else is Criteria or QueryOver). Why won't this query work?

Upvotes: 1

Views: 285

Answers (2)

Diego Mijelshon
Diego Mijelshon

Reputation: 52735

You need to tell NH what those rows represent.

session.CreateSQLQuery(...)
       .AddEntity(typeof(Article))
       .SetParameter(...)
       .List<Article>();

Read Chapter 16. Native SQL for more details

Upvotes: 2

Digbyswift
Digbyswift

Reputation: 10400

If you are running the query directly as SQL then NHibernate will have no knowledge of how to map it to your POCOs even if the tables are mapped. You need to supply NHibernate some information about how the SQL projection should be mapped.

Use HQL or LinqToNHibernate if this is an option open to you.

Upvotes: 1

Related Questions