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...
精彩评论