I have a many to many relationship table that associates accounts with associates(users)
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
<class name="<assemblyname>.DBWrappers.DB_Association, <assemblyname>" 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"/>
<one-to-one name="Account" class="<assemblyname>.DBWrappers.DB_Account, <assemblyname>" />
<one-to-one name="Associate" class="<assemblyname>.DBWrappers.DB_Associate, <assemblyname>" />
</class>
</hibernate-mapping>
I have one loop that gets all the records for an account and shows data from the cooresponding user records.
That goes fine showing the different users associated with the account.
But when I get a list of all the association for a users and show the accounts associated with that user, I get the right number of records but each of those records is just the first in the set. Almost as though it is looping though the returned selection but not clearing the re-writing the record.
When I cut and paste the named query I get into MS SQL Server I get the correct record set returned.
Here is the named queries used:
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
<class name="<assemblyname>.DBWrappers.DB_Associate, <assemblyname>" lazy="false" table="Associate">
<id name="User_ID" type="integer" column="User_ID">
<generator class="native" />
</id>
<property name="User_Name" type="string" column="User_Name"/>
<property name="Last_Name" type="string" column="Last_Name"/>
<property name="First_Name" type="string" column="First_Name"/>
<property name="Phone_Number" type="string" column="Phone_Number"/>
<property name="Group_ID" type="integer" column="Group_ID"/>
<property name="Supervisor_ID" type="integer" column="Supervisor_ID"/>
<property name="Email" type="string" column="Email"/>
<property name="External_Only" type="string" column="External_Only"/>
<property name="Has_External" type="string" column="Has_External"/>
<set name="User_Permissions" lazy="false">
<key column ="User_ID"/>
<one-to-many class="<assemblyname>.DBWrappers.DB_User_Permissions, <assemblyname>"/>
</set>
</class>
<query name="AssociateAssociationList" cacheable="false" read-only="true">
<![CDATA[
from DB_Association a
where a.User_ID = :userId order by Account_ID
]]>
</query>
</hibernate-mapping>
and
Code:
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
<class name="<assemblyname>.DBWrappers.DB_Account, <assemblyname>" lazy="false" table="Account">
<id name="Account_ID" type="integer" column="Account_ID">
<generator class="native" />
</id>
<property name="Type_ID" type="integer" column="Type_ID"/>
<property name="MTC_Account_Number" type="string" column="MTC_Account_Number"/>
<property name="Name_on_Account" type="string" column="Name_on_Account"/>
<property name="First_Name" type="string" column="First_Name"/>
<property name="Middle_Name" type="string" column="Middle_Name"/>
<property name="Last_Name" type="string" column="Last_Name"/>
<property name="SSN" type="string" column="SSN"/>
<property name="AltAdmin2" type="string" column="AltAdmin2"/>
<property name="Last_Update_Date" type="datetime" column="Last_Update_Date"/>
<property name="Create_Date" type="datetime" column="Create_Date"/>
<property name="Close_Date" type="datetime" column="Close_Date"/>
<property name="Status" type="integer" column="Status"/>
<property name="User_ID" type="integer" column="User_ID"/>
</class>
<query name="AccountAssociationList" cacheable="true" read-only="true">
<![CDATA[
from DB_Association a
where a.Account_ID = :accountId order by User_ID
]]>
</query>
</hibernate-mapping>
The code that is running
Code:
'----get all associates for an account
Dim session As ISession = hibClass.OpenSession()
Dim accountList As IList = session.CreateQuery("from DB_Account order by Account_Id").List()
For Each acctRec In accountList
'--System.Console.WriteLine(" Account: " & acctRec.Account_ID & " name: " & acctRec.Last_Name)
Dim queryBack As IQuery = session.GetNamedQuery("AccountAssociationList")
queryBack.SetParameter("accountId", acctRec.Account_ID)
Dim assocUserList As IList = queryBack.List() '---get users associated to this account
For Each assocRec As DB_Association In assocUserList
System.Console.WriteLine(" acctRecId: " & acctRec.Account_Id & " --acct: " & assocRec.Account_ID & " user: " & assocRec.User_ID & " supId: " & assocRec.Supervisor_ID)
System.Console.WriteLine(" ---user_id: " & assocRec.Associate.User_ID & " lastName: " & assocRec.Associate.Last_Name)
Next
Next
session.Close()
'----get all associates for an user
session = hibClass.OpenSession()
Dim userAssocList As IList = session.CreateQuery("from DB_Associate order by User_Id").List()
For Each userRec In userAssocList
System.Console.WriteLine(" AssocRec User: " & userRec.User_ID & " name: " & userRec.Last_Name)
Dim session2 As ISession = hibClass.OpenSession()
Dim queryBack2 As IQuery = session2.GetNamedQuery("AssociateAssociationList")
queryBack2.SetParameter("userId", userRec.User_ID)
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)
System.Console.WriteLine(" ---user_id: " & assocRec2.Account.User_ID & " lastName: " & assocRec2.Account.Last_Name)
Next
Next
and example output:
acctRecId: 1 --acct: 1 user: 9 supId: 9
---user_id: 9 lastName: Putnam
acctRecId: 2 --acct: 2 user: 10 supId: 9
---user_id: 10 lastName: ibm
acctRecId: 3 --acct: 2 user: 10 supId: 9
---user_id: 10 lastName: ibm
acctRecId: 3 --acct: 3 user: 12 supId: 9
---user_id: 12 lastName: Putnam2
acctRecId: 3 --acct: 3 user: 13 supId: 9
---user_id: 13 lastName: putnam3
acctRecId: 3 --acct: 3 user: 14 supId: 9
---user_id: 14 lastName: putnam4
acctRecId: 4 --acct: 2 user: 10 supId: 9
---user_id: 10 lastName: ibm
acctRecId: 10 --acct: 1 user: 9 supId: 9
---user_id: 9 lastName: Putnam
AssocRec User: 9 name: Putnam
--acct: 1 user: 9 supId: 9
---user_id: 0 lastName:
--acct: 1 user: 9 supId: 9 <--you can see from above this should be 10
---user_id: 0 lastName:
AssocRec User: 10 name: ibm
--acct: 2 user: 10 supId: 9
---user_id: 3 lastName:
--acct: 2 user: 10 supId: 9 <--dup
---user_id: 3 lastName:
--acct: 2 user: 10 supId: 9 <--dup
---user_id: 3 lastName:
AssocRec User: 12 name: Putnam2
--acct: 3 user: 12 supId: 9
---user_id: 0 lastName:
AssocRec User: 13 name: putnam3
--acct: 3 user: 13 supId: 9
---user_id: 0 lastName:
AssocRec User: 14 name: putnam4
--acct: 3 user: 14 supId: 9
---user_id: 0 lastName:
AssocRec User: 15 name: putnam5
The table values for the association
userId AccountId supId
9 1 9
9 10 9
10 2 9
10 3 9
10 4 9
12 3 9
13 3 9
14 3 9
An interesting cunnudrum , lets see if anyone can solve this. I do have the foreign keys in place correctly I think.
Thanks,
---John Putnam
Login
FAQ
Search
