Returning a list of KeyValuePair from a sql-query

[Gengis]

Dear all,

I have been using nhibernate and hibernate for a long time and I am
facing a pretty interesting problem.

The query I have to deal with returns a list of candidates by matching
their resume (a word file, stored as a varbinary(max) in the DB and
mapped to a byte[] in the POCO) with a full text expression (exemple :
nhibernate AND (Oracle OR "SQL Server") ).

The query uses CONTAINSTABLE to process the fulltext search and order
the results by RANK Desc.

<sql-query name="find" >
<return alias="c" class="Candidate" />
select
c.Id_candidate as {c.Id},
c.Name as {c.Name}
from
CANDIDATE c
INNER JOIN CONTAINSTABLE(CANDIDATE, RESUME, :keyword) x on
c.ID_CANDIDATE=x.[KEY]
order by
x.RANK desc
</sql-query>

It woks fine but I need to get the rank value in the results. Of
course I do not have any 'Rank' property in my Candidate c# class. And
even if I had one I would not be able to use it the select
clause because this field is not mapped to any column.

I tried to return a list of KeyValuePair<Candidate, int> where the
value is the quality of the result within the specific search
regarding the search expression :

<sql-query name="find" >
<return alias="c"
class="System.Collections.Generic.KeyValuePair`2[[MyNamespace.Candidate],
[System.Int32]]" />
select
c.Id_candidate as {c.Key.Id},
c.Name as {c.Key.Name},
x.RANK as {c.Value}
from
CANDIDATE c
INNER JOIN CONTAINSTABLE(CANDIDATE, RESUME, :keyword) x on
c.ID_CANDIDATE=x.[KEY]
order by
x.RANK desc
</sql-query>
but it does not work, the application crashs at startup when
nhibernate try to 'compile' mapping file (message : "Errors in named
queries: {find}")

Any Idea ?

Thanks.

[Fabio Maulo]

GetNamedQuery("blah").List<object[]>().ToDictionary(x=> new Candidate{Id = (Guid)x[0], Name= (string)x[1]}, x=> (int)x[2]);

On Sat, Feb 26, 2011 at 11:48 AM, Gengis <alaric....@gmail.com> wrote:

Dear all,

I have been using nhibernate and hibernate for a long time and I am
facing a pretty interesting problem.

The query I have to deal with return a list of candidate by matching

their resume (a word file, stored as a varbinary(max) in the DB and
mapped to a byte[] in the POCO) with a full text expression (exemple :
nhibernate AND (Oracle OR "SQL Server") ).

The query use CONTAINSTABLE to process the fulltext search and order
the result by RANK Desc.

<sql-query name="find" >
   <return alias="c" class="Candidate" />
   select
       c.Id_candidate as {c.Id},
       c.Name as {c.Name}
   from
       CANDIDATE c
       INNER JOIN CONTAINSTABLE(CANDIDATE, RESUME, :keyword) x on
c.ID_CANDIDATE=x.[KEY]
   order by
     x.RANK desc
</sql-query>

It woks fine but I need to get the rank value in the results. Of

course I do not have any 'Rank' property in my Candidate c# class. Any
even if I had this field I would not be able to use it the select

clause because this field is not mapped to any column.

My goal is to return a list of KeyValuePair<Candidate, int> where the

value is the quality of the result within the specific search

regarding the search expression.

I tried this :

<sql-query name="find" >
   <return alias="c"
class="System.Collections.Generic.KeyValuePair`2[[MyNamespace.Candidate],
[System.Int32]]" />
   select
       c.Id_candidate as {c.Key.Id},
       c.Name as {c.Key.Name},
       x.RANK as {c.Value}
   from
       CANDIDATE c
       INNER JOIN CONTAINSTABLE(CANDIDATE, RESUME, :keyword) x on
c.ID_CANDIDATE=x.[KEY]
   order by
     x.RANK desc
</sql-query>
but it does not work, the application crashs at startup when

nhibernate try to 'compile' mapping file.

Any Idea ?

Thanks.








--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo