I have a problem with using a subselect when I have a composite-id.

I have a class, ClassA, which has as one of its properties a IList<ClassB>

ClassB is a child of ClassA with two keys to the parent. I added a component-id to my mapping file but when I do so it causes my subselect query to be invalid. I can get it to work fine if I use a select, but it is really slow and the subselect was fast when i use it with just one key column.

Here is my data mapping file:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property">

<class name="ClassA" table="tblA">

<composite-id>
<key-property name="Id" column="Instrument_Number"/>
<key-property name="MicrofilmCode" column="Multi_Seq"/>
</composite-id>

<property name ="MicrofilmCode" column="Multi_Seq"/>


<bag name="classB" table="tblB" cascade="none" fetch="subselect" lazy="false" inverse="true">
<key>
<column name="Instrument_Number"/>
<column name="Multi_Seq"/>
</key>
<one-to-many class="ClassB"/>
</bag>
</class>


<class name ="ClassB" table="tblB">
<id name="Id" column="Instrument_Number" access="nosetter.camelcase" unsaved-value="0">
<generator class="assigned"/>
</id>
<property name ="StreetAddress" column="Address1"/>
<property name="FirstName" column="FirstName"/>
<property name="LastName" column="LastName"/>
</class>

</hibernate-mapping>

The sql i get back is invalid because it tries to do something like this: select StreetAddress, FirstName, LastName from tblB where tblB.Instrument_Number, tblB.MultiSeq in (select instrument_number, multi_seq from tblA)


Obviously this is not well-formed sql.

BTW I am using latest version NHibernate and sql 2005

I am new to NHibernate and I tried looking in the documentation so sorry if this is something I should have found there.

Hmm, what should the well-formed SQL look like in this case?

select StreetAddress, FirstName, LastName from tblB where tblB.Instrument_Number in (select instrument_number from tblA where ...***original parent query***) and
tblB.Multi_Seq in (select Multi_Seq from tblA where ...***original parent query***)

I take that back, that will not get me the results I want.

In my case the records could look like this:
Instrument_Number = 1234 Multi_Seq = 0
Instrument_Number = 1234 Multi_Seq = 1
Instrument_Number = 1234 Multi_Seq = 2

Instrument_Number = 4567 Multi_Seq = 0
Instrument_Number = 4567 Multi_Seq = 1
Instrument_Number = 4567 Multi_Seq = 2

so using a subselect will not get me what I want becuase I would always need to pass in the instrument_number when doing the subselect for the multi_seq

select StreetAddress, FirstName, LastName from tblB where tblB.Instrument_Number in (select instrument_number from tblA where ...***original parent query***) and
tblB.Multi_Seq in (select Multi_Seq from tblA where ...***original parent query*** AND INSTRUMENT_NUMBER = '1234')

Can anyone think of another way to solve my problem?