IList back from Query is stuttering badly

jfputnam2 · **Posted:** Mon Dec 28, 2009 6:46 pm

Using NHibernate and MS Sql Server and VB in VS 2008.

I have a query that returns the right number of records but they are all the first record in the set. Records 2,3 do not get loaded.

I have show sql turned on and when I cut and paste that query into Microsoft SQL Managment Studio in a query window it gives the correct results.

The mapping:

Code:

<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
<class name="<assembly>.DBWrappers.DB_Association, <assembly>" lazy="false" table="Association"> 
 
    <id name="User_ID" type="integer" column="User_ID">
       <generator class="native" />
    </id>
    <property name="Account_ID" type="integer" column="Account_ID"/>
    <property name="Supervisor_ID" type="integer" column="Supervisor_ID"/>
</class> 
</hibernate-mapping>

The class:

Code:

Imports NHibernate
Imports NHibernate.Cfg
Imports log4net
Imports System.Configuration
Imports Iesi.Collections


Namespace DBWrappers

    Public Class DB_Association

        '------------------------------------------------------------------------------------------------
        '
        '    DataBase Wrapper class for Table Association  using the nhibernate framework 
        '
        '    AutoGenerated, Author: J.Putnam Date: 12/11/2009 3:16:58 PM
        '
        '------------------------------------------------------------------------------------------------

        '---Table column  variables 

        Private _User_ID As Integer
        Private _Account_ID As Integer
        Private _Supervisor_ID As Integer



        '------------------------------------------------------------------------------------------------
        '---------Get/Set for field: User_ID 
        '------------------------------------------------------------------------------------------------

        Public Overridable Property User_ID() As Integer
            Get
                Return _User_ID
            End Get
            Set(ByVal Value As Integer)
                _User_ID = Value
            End Set
        End Property

        '------------------------------------------------------------------------------------------------
        '---------Get/Set for field: Account_ID 
        '------------------------------------------------------------------------------------------------

        Public Overridable Property Account_ID() As Integer
            Get
                Return _Account_ID
            End Get
            Set(ByVal Value As Integer)
                _Account_ID = Value
            End Set
        End Property

        '------------------------------------------------------------------------------------------------
        '---------Get/Set for field: Supervisor_ID 
        '------------------------------------------------------------------------------------------------

        Public Overridable Property Supervisor_ID() As Integer
            Get
                Return _Supervisor_ID
            End Get
            Set(ByVal Value As Integer)
                _Supervisor_ID = Value
            End Set
        End Property
       

    End Class
End Namespace

The code that is running the query:

Code:

        Dim session2 As ISession = hibClass.OpenSession()

        Dim queryBack2 As IQuery = session2.CreateQuery("select a from DB_Association a where a.User_ID = 10")

        Dim assocAcctList As IList = queryBack2.List()  '---get users associated to this account

        For Each assocRec2 As DB_Association In assocAcctList

            System.Console.WriteLine("      --acct: " & assocRec2.Account_ID & " user: " & assocRec2.User_ID & " supId: " & assocRec2.Supervisor_ID)

        Next
        session2.Close()

The reulsts of the running code:

NHibernate: select db_associa0_.User_ID as User1_94_, db_associa0_.Account_ID as Account2_94_, db_associa0_.Supervisor_ID as Supervisor3_94_ from Association db_associa0_ where db_associa0_.User_ID=10

--acct: 2 user: 10 supId: 9 These lines should have different account numbers!!!!
--acct: 2 user: 10 supId: 9
--acct: 2 user: 10 supId: 9

The results from running the sql directly:

Code:

    user  acct#  sup#
10   2   9    You can see the query returns different account numbers!!!
10   3   9
10   4   9

I don't think this should be possible and may be a bug in nhibernate.

Anyone know how this could happen, or what to do to isolate the issue further?

wolli · **Posted:** Tue Dec 29, 2009 5:52 am

Your id value in this table is not unique. Hibernate distinguish the objects using the id field, therefore for hibernate all these objects are the same, so hibernate only loads the first one. You need a unique id (primary key) for the Association table:

id <--- native
user_id,
account_id,
supervisor_id

jfputnam2 · **Posted:** Tue Dec 29, 2009 11:54 am

Wolfgang,

Thanks for your help, that was the problem. It is interesting thought that the SQL generated is not the SQL used for the retrieval.

I assume then that the exposed SQL is supose to be equivalent to what NHibernate is doing internally. At the very least that is a bug.

I added an identity column and get proper results. The problem here is that there is a lot of legacy code now that needs regression testing. I could have done a composite unique primary key but you get some record update issues with that.

Thanks for your help.

---John Putnam