nhibernate sql查询结果与直接查询结果不同 [英] nhibernate sql-query results different from direct query results

问题描述

更新:我已经在下面回答了我的问题，请窥视一下，让我知道您是否有更好的方法

执行摘要:
从SQL Management Studio调用sqlserver函数时，我得到一个包含结果{1、2、3}的列表. 当使用NHibernate从代码中调用方法时，我得到此列表{1，1，1}. ("1"是整个结果行，而不是标量"1") 我也尝试过使用不同的数据集并得到相同的行为.

长话短说:
我在sql server中有一个sql函数，称为GetHistory(itemId).它返回一个包含结果的表. 当我从SQL Management Studio查询时，会得到结果列表.我通过调用函数来查询，如下所示:

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)

在给定的数据库上，这将导致3行.每行都有一个时间，类型和说明.
在NHibernate中，我为此专门创建了一个新实体，因为没有这样的有机表/实体. 所以我有一个映射:

on my given DB this results in 3 rows. each row has a Time, 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>

业务对象如下:

 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();

当使用调试器检查此列表时，我得到3个相同行的实体3次. 我也尝试过直接从NHibernate使用查询(使用sql-query)而不是通过DB函数，但是得到了相同的结果.
我的直觉是映射有问题，或者NHibernate有问题:)
帮助！

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!

推荐答案

好的，在我发布问题后不久，我就知道了.
编写类似的内容对我有很大帮助猜测.这就是问题所在:
我使用的ID列不是唯一的.它是一个ID，而不是查询的正确ID.这是为什么?因为我的查询返回了来自三个不同表的三个查询的并集，并且返回的ID实际上是ItemId(与传递给函数的那个​​相同！)

所以现在我必须有一个唯一的ID.我尝试过短暂地使用NHibernate的Composite-id标签而不是id-但面对困难，我决定不浪费更多时间在自动魔术师上，而是从查询中返回ID.

发现我无法在具有表变量的函数内使用SELECT IDENTITY()INTO ...，我考虑了RAND一秒钟，最终使用ROW_NUMBER()OVER(SORT BY blah)生成了我的假ID. 现在，尽管这段代码无法赢得选美比赛，但目前效果很好.
PS:是否有更好的生成ID的方法的建议?还是您曾经获得过复合ID为您工作?让我知道...

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...

这篇关于nhibernate sql查询结果与直接查询结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！