开发者

nhibernate sql-query results different from direct query results

UPDATE: I've answered my question below, take a peek and let me know if you have a better way of doing this


executive summary:

when calling my sqlserver function from SQL Management Studio I get a list with results {1, 2, 3}. when calling the method from a code using NHibernate I get this list {1, 1, 1}. ('1' is an entire result row, not the scalar '1') I've also tried it with different data sets and got the same behavior.

the long story:

I have an sql function in sql server, called GetHistory(itemId). it returns a table with the results. when I query from SQL Management Studio I get a list of results. I query by calling the function like so:

select * from GetHistory(10001)

on my given DB this results in 3 rows. each row has a Tim开发者_如何转开发e, Type and Description.

in NHibernate, I've created a new entity especially for this, as there's no such organic table/entity. so I have a mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
 namespace="myNamespace" assembly="myAssembly">
 <class name="HistoryEvent">
 <id name="id" type="long" access="field">
  <column name="Id"/>
  <generator class="native"/>
 </id>
 <property name="type" column="Type" type="short" access="field"/>
 <property name="time" column="Time" type="datetime" access="field"/>
 <property name="description" column="Description" type="string" access="field"/>
</class>

<sql-query name='GetHistory'>
    <return class='HistoryEvent, myAssembly' alias='historyEvent'/>
 <![CDATA[SELECT * FROM GetHistory(:id)]]>
</sql-query>
</hibernate-mapping>

the business object looks like this:

 public class HistoryEvent
 {
     private long id;
  private short type;
  private string description;
  private DateTime time;
        ... here be properties with public getter etc...
    }

and finally, I call this function from my code like so:

IList result = s.GetNamedQuery("GetHistory").SetInt64("id", id).List();

when inspecting this list with the debugger I get 3 entities which are the same row 3 times. I've also tried using the query directly from NHibernate (using sql-query) instead of going through the DB function, but got the same results.

my intuition is that something is wrong with my mapping, or something is wrong with NHibernate :)

HELP!


ok, shortly after I posted the question, I figured it out.
writing everything like that really helped me I guess. this was the problem:
the Id column I used wasn't unique. it's AN ID, just not THE RIGHT ID for the query. why is that? because my query returns a union of three queries from three different tables, and the ID returned was actually the ItemId (same one that was passed to the function!)

so now I had to have a unique ID. I attempted briefly to use NHibernate's composite-id tag instead of the id - but facing difficulty I decided not to waste more time on the automagical stuff and return IDs from the query.

finding out I couldn't use SELECT IDENTITY() INTO ... inside functions with a table variable, I considered RAND for a sec and eventually used ROW_NUMBER() OVER (SORT BY blah) to generate my fake IDs. for now this works quite well, although this code wouldn't win a beauty contest.
PS: got suggestions for a better way to generate an ID? or did you ever get composite-id to work for you? let me know...

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜