many to many with association table (Linker)

longday · **Joined:** Fri Nov 04, 2005 4:13 pm **Posts:** 6

I have a database with 3 tables:

Code:

CREATE TABLE dbo.t_Users (
   UserID char (7)  Primary Key,
   Name varchar (50)  ,
   Added smalldatetime NOT NULL ,
   Deleted smalldatetime NULL ,
   Updated smalldatetime NULL ,
   Manual bit NOT NULL ,
   ManagedBy char (10)  ,
   Validated bit NOT NULL 
)
GO


--Linker table to map users to roles and inverse
CREATE TABLE dbo.t_UserRoles (
   UserRoleID uniqueidentifier NOT NULL Primary Key,
   UserID char (7)  ,
   RoleID varchar (20)  ,
   OfficerCode varchar (10)  ,
   Added smalldatetime NOT NULL ,
   Deleted smalldatetime NULL ,
   ManagedBy char (7)  ,
   Manual bigint NOT NULL 
)
GO

CREATE TABLE dbo.t_Roles (
   RoleID varchar (20)  Primary Key,
   ViewAllBranches bit NOT NULL ,
   ViewAllRoles bit NOT NULL ,
   UserTypeDescription varchar (50)  
)

(Platform VB.NET)
How do I setup the mapping a classes to end up with a user class that has a collection Roles where roles is derived by using t_userroles?

I would also like to have role class with users collection where users are derived from t_usersroles link.

I assume the main problem is that the association between users and roles has attrubutes.

Thank You
Eric

KPixel · **Posted:** Sun Nov 06, 2005 10:55 am

IMO, you should use <one-to-many> in User and Role (to UserRole) and put two <many-to-one> in UserRole (to User and Role)... (and don't forget to enable lazy loading...)

dfed · **Joined:** Tue Jun 14, 2005 9:23 am **Posts:** 9

Hi all, I have the same problem...

Can you post an example?

longday · **Joined:** Fri Nov 04, 2005 4:13 pm **Posts:** 6

I settled on loading 3 classes, User, UserRoles, Roles. Using the code below, but I still have a question. When I load user and select any userrole in the userroles colleciton, the userid and roleid properties are not populated. The userrole objects have all properties

When I load the user, the following happens

user.userroles(0).userid returns ""
user.userroles(0).user.userid returns "TestUserID"

Why is user.userroles(0).userid not populated?

Code:

Public Class User

#Region "Private Database Fields"
    Private _UserID As String
    Private _Name As String
    Private _Added As Date
    Private _Deleted As Date
    Private _Updated As Date
    Private _Manual As Boolean
    Private _ManagedBy As String
    Private _Validated As Boolean
#End Region

#Region "Public Database Fields"

    Public Property UserID() As String
        Get
            Return _UserID
        End Get
        Set(ByVal value As String)
            _UserID = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = Value
        End Set
    End Property

    Public Property Added() As Date
        Get
            Return _Added
        End Get
        Set(ByVal value As Date)
            _Added = Value
        End Set
    End Property

    Public Property Deleted() As Date
        Get
            Return _Deleted
        End Get
        Set(ByVal value As Date)
            _Deleted = Value
        End Set
    End Property

    Public Property Updated() As Date
        Get
            Return _Updated
        End Get
        Set(ByVal value As Date)
            _Updated = Value
        End Set
    End Property

    Public Property Manual() As Boolean
        Get
            Return _Manual
        End Get
        Set(ByVal value As Boolean)
            _Manual = Value
        End Set
    End Property

    Public Property ManagedBy() As String
        Get
            Return _ManagedBy
        End Get
        Set(ByVal value As String)
            _ManagedBy = Value
        End Set
    End Property

    Public Property Validated() As Boolean
        Get
            Return _Validated
        End Get
        Set(ByVal value As Boolean)
            _Validated = Value
        End Set
    End Property

#End Region

#Region "Custom Items"

    Private _UserRoles As IList = New ArrayList

    Public Property UserRoles() As IList
        Get
            Return _UserRoles
        End Get
        Set(ByVal value As IList)
            _UserRoles = value
        End Set
    End Property

#End Region

    Public Sub New()
    End Sub

    Public Sub Load()
        Dim session As NHibernate.ISession = Configuration.GetSession
        session.Load(Me, Me.UserID)
    End Sub

End Class

________________________________________________

Code:

Public Class UserRole

#Region "Private Database Fields"
    Private _UserRoleID As Guid = Guid.NewGuid
    Private _UserID As String
    Private _RoleID As String
    Private _OfficerCode As String
    Private _Added As Date
    Private _Deleted As Date
    Private _ManagedBy As String
    Private _Manual As Boolean
#End Region

#Region "Public Database Fields"

    Public Property UserRoleID() As Guid
        Get
            Return _UserRoleID
        End Get
        Set(ByVal value As Guid)
            _UserRoleID = Value
        End Set
    End Property

    Public Property UserID() As String
        Get
            Return _UserID
        End Get
        Set(ByVal value As String)
            _UserID = Value
        End Set
    End Property

    Public Property RoleID() As String
        Get
            Return _RoleID
        End Get
        Set(ByVal value As String)
            _RoleID = Value
        End Set
    End Property

    Public Property OfficerCode() As String
        Get
            Return _OfficerCode
        End Get
        Set(ByVal value As String)
            _OfficerCode = Value
        End Set
    End Property

    Public Property Added() As Date
        Get
            Return _Added
        End Get
        Set(ByVal value As Date)
            _Added = Value
        End Set
    End Property

    Public Property Deleted() As Date
        Get
            Return _Deleted
        End Get
        Set(ByVal value As Date)
            _Deleted = Value
        End Set
    End Property

    Public Property ManagedBy() As String
        Get
            Return _ManagedBy
        End Get
        Set(ByVal value As String)
            _ManagedBy = Value
        End Set
    End Property

    Public Property Manual() As Boolean
        Get
            Return _Manual
        End Get
        Set(ByVal value As Boolean)
            _Manual = Value
        End Set
    End Property

#End Region

#Region "Custom Items"

    Private _User As New User
    Private _Role As New Role

    Public Property User() As User
        Get
            Return _User
        End Get
        Set(ByVal value As User)
            _User = value
        End Set
    End Property

    Public Property Role() As Role
        Get
            Return _Role
        End Get
        Set(ByVal value As Role)
            _Role = value
        End Set
    End Property


#End Region

    Public Sub New()
    End Sub

End Class

______________________________________

Code:

Public Class Role

#Region "Private Database Fields"
    Private _RoleID As String
    Private _ViewAllBranches As Boolean
    Private _ViewAllRoles As Boolean
    Private _UserTypeDescription As String
#End Region

#Region "Public Database Fields"

    Public Property RoleID() As String
        Get
            Return _RoleID
        End Get
        Set(ByVal value As String)
            _RoleID = Value
        End Set
    End Property

    Public Property ViewAllBranches() As Boolean
        Get
            Return _ViewAllBranches
        End Get
        Set(ByVal value As Boolean)
            _ViewAllBranches = Value
        End Set
    End Property

    Public Property ViewAllRoles() As Boolean
        Get
            Return _ViewAllRoles
        End Get
        Set(ByVal value As Boolean)
            _ViewAllRoles = Value
        End Set
    End Property

    Public Property UserTypeDescription() As String
        Get
            Return _UserTypeDescription
        End Get
        Set(ByVal value As String)
            _UserTypeDescription = Value
        End Set
    End Property

#End Region

#Region "Custom Items"

    Private _UserRoles As IList = New ArrayList

    Public Property UserRoles() As IList
        Get
            Return _UserRoles
        End Get
        Set(ByVal value As IList)
            _UserRoles = value
        End Set
    End Property

#End Region

    Public Sub New()
    End Sub

End Class

Code:

      <class name="BankInfo.Database.User, BankInfo.Database" table="t_Users">
         <id name="UserID" type="String" unsaved-value="null">
            <column name="UserID" length="7" sql-type="char" not-null="true" unique="true" index="PK_t_Users"/>
            <generator class="native" />
         </id>
         <property name="Name" type="String">
            <column name="Name" length="50" sql-type="varchar" not-null="true"/>
         </property>
         <property name="Added" type="DateTime">
            <column name="Added" sql-type="smalldatetime" not-null="true"/>
         </property>
         <property name="Deleted" type="DateTime">
            <column name="Deleted" sql-type="smalldatetime" not-null="false"/>
         </property>
         <property name="Updated" type="DateTime">
            <column name="Updated" sql-type="smalldatetime" not-null="false"/>
         </property>
         <property name="Manual" type="Boolean">
            <column name="Manual" sql-type="bit" not-null="true"/>
         </property>
         <property name="ManagedBy" type="String">
            <column name="ManagedBy" length="10" sql-type="char" not-null="true"/>
         </property>
         <property name="Validated" type="Boolean">
            <column name="Validated" sql-type="bit" not-null="true"/>
         </property>
         <bag name="UserRoles" inverse="true" lazy="true" cascade="all">
            <key column="UserID"/>
            <one-to-many class="BankInfo.Database.UserRole, BankInfo.Database"/>
         </bag>
      </class>

      <class name="BankInfo.Database.UserRole, BankInfo.Database" table="t_UserRoles">
         <id name="UserRoleID" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000">
            <column name="UserRoleID" sql-type="uniqueidentifier" not-null="true" unique="true" index="PK_t_UserRoles"/>
            <generator class="guid" />
         </id>
         <property name="OfficerCode" type="String">
            <column name="OfficerCode" length="10" sql-type="varchar" not-null="true"/>
         </property>
         <property name="Added" type="DateTime">
            <column name="Added" sql-type="smalldatetime" not-null="true"/>
         </property>
         <property name="Deleted" type="DateTime">
            <column name="Deleted" sql-type="smalldatetime" not-null="false"/>
         </property>
         <property name="Manual" type="Boolean">
            <column name="Manual" sql-type="bit" not-null="true"/>
         </property>
         <many-to-one name="User" class="BankInfo.Database.User, BankInfo.Database">
            <column name="UserID" length="7" sql-type="char" not-null="true" index="IX_t_UserRoles"/>
         </many-to-one>
         <many-to-one name="Role" class="BankInfo.Database.Role, BankInfo.Database">
            <column name="RoleID" length="20" sql-type="varchar" not-null="true" index="IX_t_UserRoles"/>
         </many-to-one>
      </class>

      <class name="BankInfo.Database.Role, BankInfo.Database" table="t_Roles">
         <id name="RoleID" type="String" unsaved-value="null">
            <column name="RoleID" length="20" sql-type="varchar" not-null="true" unique="true" index="PK_t_Roles"/>
            <generator class="native" />
         </id>
         <property name="ViewAllBranches" type="Boolean">
            <column name="ViewAllBranches" sql-type="bit" not-null="true"/>
         </property>
         <property name="ViewAllRoles" type="Boolean">
            <column name="ViewAllRoles" sql-type="bit" not-null="true"/>
         </property>
         <property name="UserTypeDescription" type="String">
            <column name="UserTypeDescription" length="50" sql-type="varchar" not-null="true"/>
         </property>
         <bag name="UserRoles" inverse="true" lazy="true" cascade="all">
            <key column="RoleID"/>
            <one-to-many class="BankInfo.Database.UserRole, BankInfo.Database"/>
         </bag>
      </class>

longday · **Joined:** Fri Nov 04, 2005 4:13 pm **Posts:** 6

I found my problem. I am using codesmith to generate the mapping and it did not map the UserID and RoleID properties properly. I had to add the property / column mapping on the userrole class and add the following attributes: insert="false" update="false" to the property element.

Looks like I working now.

Thanks KPixel for you advice.

Thanks
Eric Brasher