NHibernate - "In" query with composite id

Question

I'm trying to perform an "in" query on a collection of id objects (implemented as a simple class with two integer id members) which are mapped as composite-keys and I'm seeing some strange results when I query using the criteria api using Restrictions.In and using NHibernate.Linq using idList.Contains

Here is a sample usage:

    Public Function GetByMultipleIds(ByVal ids As ICollection(Of QualificationKey)) As IList(Of Qualification) Implements IQualificationRepository.GetByMultipleIds
        Dim query = Session.CreateCriteria(Of Qualification)()
        query.Add(Restrictions.In("Id", ids.ToArray()))
        Return query.List(Of Qualification)()
    End Function

Here is the mapping for my key:

    <composite-id name="Id" class="QualificationKey">
        <key-property name="QualificationAreaId" column="QualificationAreaId"/>
        <key-property name="QualificationLevelId" column="QualificationLevelId"/>
    </composite-id>

Here is the resulting SQL that's generated:

SELECT this_.QualificationAreaId                                                                  as Qualific1_6_2_,
       this_.QualificationLevelId                                                                 as Qualific2_6_2_,
       this_.Version                                                                              as Version6_2_,
       this_.Rank                                                                                 as Rank6_2_,
       (SELECT QualificationArea.QualificationAreaTypeId
        FROM   QualificationArea
        WHERE  QualificationArea.QualificationAreaId = this_.QualificationAreaId) as clazz_2_,
       qualificat2_.QualificationAreaId                                                           as Qualific1_7_0_,
       qualificat2_.Name                                                                          as Name7_0_,
       qualificat2_.QualificationAreaTypeId                                                       as Qualific2_7_0_,
       qualificat2_.QualificationAreaPermissionId                                                 as Qualific4_7_0_,
       qualificat2_.Description                                                                   as Descript5_7_0_,
       qualificat2_.QualificationAreaExpirySettingId                                              as Qualific6_7_0_,
       qualificat2_.DisplayOrder                                                                  as DisplayO7_7_0_,
       qualificat2_.DateCreated                                                                   as DateCrea8_7_0_,
       qualificat2_.DateUpdated                                                                   as DateUpda9_7_0_,
       qualificat2_.ShowOnSignupForm1                                                             as ShowOnS10_7_0_,
       qualificat2_.ShowOnSignupForm2                                                             as ShowOnS11_7_0_,
       qualificat2_.ShowOnSignupForm3                                                             as ShowOnS12_7_0_,
       qualificat2_.AgencyId                                                                      as AgencyId7_0_,
       qualificat3_.QualificationLevelId                                                          as Qualific1_47_1_,
       qualificat3_.Name                                                                          as Name47_1_,
       qualificat3_.Description                                                                   as Descript3_47_1_,
       qualificat3_.DateCreated                                                                   as DateCrea4_47_1_,
       qualificat3_.DateUpdated                                                                   as DateUpda5_47_1_,
       qualificat3_.AgencyId                                                                      as AgencyId47_1_,
       dbo.IsQualificationLevelAssociatedWithAnyQualifications(qualificat3_.QualificationLevelId) as formula21_1_
FROM   Qualification this_
       inner join QualificationArea qualificat2_
         on this_.QualificationAreaId = qualificat2_.QualificationAreaId
       inner join QualificationLevel qualificat3_
         on this_.QualificationLevelId = qualificat3_.QualificationLevelId
WHERE  this_.QualificationAreaId in (1 /* @p0 */,2 /* @p1 */,3 /* @p2 */)
       and this_.QualificationLevelId in (1 /* @p3 */,2 /* @p4 */,3 /* @p5 */)

To me, this logic seems flawed, it's performing seperate "In" queries for each of the composite key ids; wouldn't this return incorrect results?

For reference, I have .Equals and .GetHashCode properly implemented on my key class, so I'm sure that's not the issue.

Answer 1

yes it will return wrong results, any of the pairs defined the composite-id values passed can evaluate to true, so while you request for example [ [1,1], [2,2], [3,3] ] this query will also fetch [ [1,2], [1,3], [2,1] [2,3], [3,1], [3,2] etc]..

the only solution i can think now is a disjunction of the pairs... eg

(this_.QualificationAreaId = 1 AND this_.QualificationLevelId = 1) OR

(this_.QualificationAreaId = 2 AND this_.QualificationLevelId = 2) OR

(this_.QualificationAreaId = 3 AND this_.QualificationLevelId = 3)

etc....