USER
login          name
-----------------------------
dummy      Dummy User


APPLICATIONS
applicationid        name
---------------------------------------------
app1                   Application 1
app2                   Application 2

USER_APPS
login                  applicationid
---------------------------------------------
dummy              app1
DuMmY              app2

Public Class CaseInsensitiveStringType
    Implements IUserType

    Private SQL_TYPES() As NHibernate.SqlTypes.SqlType = {New NHibernate.SqlTypes.StringSqlType()}

    Public Function DeepCopy(ByVal value As Object) As Object Implements NHibernate.IUserType.DeepCopy
        Return value
    End Function

    Public Function Equals1(ByVal x As Object, ByVal y As Object) As Boolean Implements NHibernate.IUserType.Equals
        'are they both strings?
        If Not (TypeOf (x) Is String AndAlso TypeOf (y) Is String) Then
            Return False
        End If

        'are they equal (case insensitive)
        Return x.ToString().ToLower().Equals(y.ToString.ToLower())
    End Function

    Public ReadOnly Property IsMutable() As Boolean Implements NHibernate.IUserType.IsMutable
        Get
            Return False
        End Get
    End Property

    Public Function NullSafeGet(ByVal rs As System.Data.IDataReader, ByVal names() As String, ByVal owner As Object) As Object Implements NHibernate.IUserType.NullSafeGet
        'get the value, if it is null, set the string to be emtpy
        Dim idx As Int16 = rs.GetOrdinal(names(0))
        Dim val As String

        If rs.IsDBNull(idx) Then
            val = String.Empty
        Else
            val = rs.GetString(idx)
        End If

        Return val
    End Function

    Public Sub NullSafeSet(ByVal cmd As System.Data.IDbCommand, ByVal value As Object, ByVal index As Integer) Implements NHibernate.IUserType.NullSafeSet
        'we don't care about nulls here
        Dim param As IDataParameter = cmd.Parameters(index)
        param.Value = value
    End Sub

    Public ReadOnly Property ReturnedType() As System.Type Implements NHibernate.IUserType.ReturnedType
        Get
            Return GetType(String)
        End Get
    End Property

    Public ReadOnly Property SqlTypes() As NHibernate.SqlTypes.SqlType() Implements NHibernate.IUserType.SqlTypes
        Get
            Return SQL_TYPES
        End Get
    End Property
End Class

<class name="User" table="[User]">
    <id name="LoginId" column="LoginId" type="MyProject.CaseInsensitiveStringType, MyProject" length="50" access="nosetter.camelcase-underscore">
      <generator class="assigned" />     
    </id>

    <!-- main properties -->
    <property name="FirstName" column="firstName" type="String" length="50" />
    <property name="LastName" column="lastName" type="String" length="50" />
   
    <set name="applications" lazy="true" access="NHibernate.Generics.GenericAccessor, NHibernate.Generics" table="AppUserXRef">
      <key column="LoginId" />
      <many-to-many class="Application" column="ApplicationId" />
    </set>

  </class>

<class name="Application" table="Application" proxy="Application" lazy="true">

    <id name="ApplicationId" type="MyProject.CaseInsensitiveStringType, MyProject" length="50" column="ApplicationId" access="nosetter.camelcase-underscore">
      <column name="ApplicationId" />
      <generator class="assigned" />
    </id>
   
    <property name="Name" type="String" length="100" not-null="true" column="Description" />
    <property name="Url" type="String" length="100" not-null="false" column="Url" />

  </class>

<Test()> _
    Public Sub TestLoginIdCaseDoesntMatter()

        Using (New TransactionScope())

            Dim conn As IDbConnection = TestHelper.GetDbConnection()

            Try
                conn.Open()       
                Dim cmd As IDbCommand = conn.CreateCommand()

                'insert dummy user
                cmd.CommandText = "INSERT INTO [User](LoginId, FirstName, LastName, Email, CreatedBy) VALUES('test_user_with_case', 'test', 'user', 'user@email.com', 'test');"
                cmd.ExecuteNonQuery()

                'insert a couple applications
                cmd.CommandText = "INSERT INTO Application(ApplicationId, Description, CreatedBy) VALUES('TEST_APP', 'testapplication', 'test');"
                cmd.ExecuteNonQuery()

                cmd.CommandText = "INSERT INTO Application(ApplicationId, Description, CreatedBy) VALUES('TEST2_APP', 'testapplication2', 'test');"
                cmd.ExecuteNonQuery()

                'add app references to user (1 with diferring case for loginid)
                cmd.CommandText = "INSERT INTO [AppUserXRef](ApplicationId, LoginId) VALUES('TEST_APP', 'TesT_UsEr_WiTh_CaSE');"
                cmd.ExecuteNonQuery()

                cmd.CommandText = "INSERT INTO [AppUserXRef](ApplicationId, LoginId) VALUES('TEST2_APP', 'test_user_with_case');"
                cmd.ExecuteNonQuery()

                Dim session As NHibernate.ISession = NHibernateUtil.GetSession(conn)

                Dim u As User = New UserRepository(session).Load("test_user_with_case")
                Assert.AreEqual(2, u.Applications.Count) //fails

            Finally
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
            End Try

        End Using

    End Sub