nhibernate newbe question ... easy one here

Question

I'm new to nhibernate so this should be easy. I have a mapping file as below although I deleted some fields that aren't relevant to this question. The streamfields class contains a bag of fieldmappings. I want the join to be on field_no column but the sql that is sent is on the id field (str_fld_id") as seen below.

I see what the below sql is doing but it's not what I wanted. It's trying to query the field_mappings table based on the values found in the id column str_fld_id in the StreamFields class when I thought it was clear I wanted the field_no to be used on both ends. I say I thought it was clear because the mapping for the field_mapping class has the below attribute and they both have the same named field Below is in my FieldMappings mapping file.

<many-to-one name="FieldNo" cascade="none" column="`Field_No`" not-null="true">

Sql sent

NHibernate: SELECT fkfieldmap0_.[field_no] as field5_1_, fkfieldmap0_.[Mapping_Id] as Mapping1_1_, fkfieldmap0_.[Mapping_Id] as Mapping1_3_0_, fkfieldmap0_.[Std_fld_Id] as Std2_3_0_, fkfieldmap0_.[Field_Position] as Field3_3_0_, fkfieldmap0_.[Field_No] as Field4_3_0_ FROM [Field_Mappings] fkfieldmap0_ WHERE fkfieldmap0_.[field_no]=@p0; @p0 = '20'

StreamFields mapping

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="DataTransfer.StreamFields,DataTransfer" table="`stream_fields`"   lazy="true">
<id name="StrFldId" column="`str_fld_id`" type="int">
<generator class="native" />
</id>
<property type="int" not-null="true" name="FieldNo" column="`field_no`" />
<many-to-one name="StreamId" cascade="none" column="`stream_Id`" />
<bag name="FkFieldMappingsStreamFields" inverse="true" lazy="false" cascade="all">
<key column="`field_no`" />
<one-to-many class="DataTransfer.FieldMappings,DataTransfer"/>
</bag>
</class>

Answer 1

[Edited - with old comments]

Okay, i think i finally got you right and i might admit the problems i had understanding what you want took me a while and result of the lack of information you provided. In the future please provide the mapping of both tables clarify on the point wheather it is a mapping or a query issue. Thx.

IMO you have misunderstood the idea of a parent/child-relation. The bag you mentioned like to have within the StreamFields class shouldn't be a bag but a direct association. Like this:

<class name="DataTransfer.StreamFields,DataTransfer" table="stream_fields" >
  <id name="StrFldId" column="str_fld_id" type="int">
    <generator class="native" />
  </id>
  <property type="int" not-null="true" name="FieldNo" column="field_no" />
  <many-to-one name="FieldMapping" class="FiueldMapping" column="Field_No" />
</class>

This of course will only work if you have a property of type FiledMapping in your class.

You want to map FieldMapping to the column Field_No within StreamFields class. There can only be one value within this column, so a bag makes no sense at all. If you want to have a bag of course you can keep it the way it already worked but be aware that the 'key-column' within the bag refers to the child table - in an other way it makes no sense cause a ForeignKey has to map to a PrimaryKey on its parent table. This ensures it is unique and set. I really don't want to rant but would strongly encourage you to review the hibernate reference about collection mapping to get a deeper clue however.

Hopely this will solve your problem.

Answer 2

Below are the mappings for the classes.

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="DataTransfer.StreamFields,DataTransfer" table="`stream_fields`" lazy="true">
<id name="StrFldId" column="`str_fld_id`" type="int">
  <generator class="native" />
</id>
<property type="string" length="50" name="FieldName" column="`field_name`" />
<property type="int" name="InputFieldPosition" column="`input_field_position`" />
<property type="int" name="Start" column="`start`" />
<property type="int" name="Width" column="`width`" />
<property type="string" length="50" name="Datatype" column="`datatype`" />
<property type="int" not-null="true" name="FieldNo" column="`field_no`" />
<property type="int" name="FieldOrder" column="`field_order`" />
<property type="int" name="StdId" column="`Std_Id`" />
<many-to-one name="StreamId" cascade="none" column="`stream_Id`" />
<bag name="FkFieldMappingsStreamFields" inverse="true" lazy="false" cascade="all">
  <key column="`field_no`" />
  <one-to-many class="DataTransfer.FieldMappings,DataTransfer"/>
</bag>
</class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="DataTransfer.FieldMappings,DataTransfer" table="`Field_Mappings`" lazy="false">
<id name="MappingId" column="`Mapping_Id`" type="int">
  <generator class="native" />
</id>
<property type="int" name="StdFldId" column="`Std_fld_Id`" />
<property type="int" name="FieldPosition" column="`Field_Position`" />
<many-to-one name="FieldNo" cascade="none" column="`Field_No`" not-null="true"  property-ref="FieldNo" />
</class>
</hibernate-mapping>

To make things easy on myself, there is one record in stream_fields and the field_no value is 1 and 20 is the value in StrFldId.

SELECT fkfieldmap0_.[field_no] as field5_1_, fkfieldmap0_.[Mapping_Id] as Mapping1_1_, fkfieldmap0_.[Mapping_Id] as Mapping1_3_0_, fkfieldmap0_.[Std_fld_Id] as Std2_3_0_, fkfieldmap0_.[Field_Position] as Field3_3_0_, fkfieldmap0_.[Field_No] as Field4_3_0_ FROM [Field_Mappings] fkfieldmap0_ WHERE fkfieldmap0_.[field_no]=@p0; @p0 = '20' –