NHibernate - how to map dictionary<value, object>?

Question

I am trying figure out how to set up the nhibernate mapping for the class BOM shown in the code sample below. the part i am having trouble with is how to map the property BOM.Components so that the mapping is equivalent to the SQL I have posted below.

Here is my mapping for BOM so far: :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Inventory.Core"
                   namespace="Inventory.Core.Entities">
  <class name="BOM" table="bom">
    <id name="Id" column="bom_id">
      <generator class="hilo" />
    </id>
    <many-to-one name="Product" class="Material" column="mtrl_id" foreign-key="fk_BOM_Material" unique="true" not-null="true" />    
    <map name="Components" table="bom_cmpnnts">
       <key column="bom_id" foreign-key="fk_BOMComponent_BOM" not-null="true" />
   </map>
  </class>
</hibernate-mapping>

Update:

Here is the Mapping for BOMComponent:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Inventory.Core"
                   namespace="Inventory.Core.Entities">
  <class name="BOMComponent" table="bom_cmpnnt">
    <id name="Id" column="bom_cmpnnt_id">
      <generator class="hilo" />
    </id>
    <version name="Version" column="bom_cmpnnt_vrsn" />
    <many-to-one name="Component"  class="Material" column="mtrl_id" foreign-key="fk_BOMComponent_Material" not-null="true" />
    <property name="Unit" column="bom_cmpnnt_unt" not-null="true" />
    <property name="Quantity" column="bom_cmpnnt_qntty" not-null="true" />
    <many-to-one name="User"  class="User" column="upsrt_usr_id" foreign-key="fk_BOMComponent_User" lazy="false" not-null="true" />
    <property name="Timestamp" column="upsrt_dttm" generated="always" />
  </class>
</hibernate-mapping>

Classes:

public class BOM
{
    int Id { get; set; }
    Material Product { get; set; }
    IDictionary<int, BOMComponent> Components { get; set; } 
    //    Key = BOMComponent.Material.Id
    //    for each BOM, materials should be unique

    //other properties ...
}
public class BOMComponent
{
    int Id { get; set; }
    Material Material { get; set; }

    //other properties ...
}
public class Material
{
    int Id { get; set; }

    //other properties
}

This is the what the SQL should look like:

delimiter $$

CREATE TABLE `mtrl` (
  `mtrl_id` int(11) NOT NULL,
  PRIMARY KEY (`mtrl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `bom` (
  `bom_id` int(11) NOT NULL,
  `mtrl_id` int(11) NOT NULL,
  PRIMARY KEY (`bom_id`),
  UNIQUE KEY `mtrl_id_UNIQUE` (`mtrl_id`),
  KEY `fk_BOM_Material` (`mtrl_id`),
  CONSTRAINT `fk_BOM_Material` FOREIGN KEY (`mtrl_id`) REFERENCES `mtrl` (`mtrl_id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `bom_cmpnnt` (
  `bom_cmpnnt_id` int(11) NOT NULL,
  `bom_id` int(11) NOT NULL,
  `mtrl_id` int(11) NOT NULL,
  PRIMARY KEY (`bom_cmpnnt_id`),
  UNIQUE KEY `IX_BOMComponent_UQ` (`bom_id`,`mtrl_id`),
  KEY `fk_BOMComponent_BOM` (`bom_id`),
  KEY `fk_BOMComponent_Material` (`mtrl_id`),
  CONSTRAINT `fk_BOMComponent_BOM` FOREIGN KEY (`bom_id`) REFERENCES `bom` (`bom_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_BOMComponent_Material` FOREIGN KEY (`mtrl_id`) REFERENCES `mtrl` (`mtrl_id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Answer 1

I hate to answer my own question but I got it to work the way I wanted by using these two mappings:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Inventory.Core"
                   namespace="Inventory.Core.Entities">
  <class name="BOM" table="bom">
    <id name="Id" column="bom_id">
      <generator class="hilo" />
    </id>
    <version name="Version" column="bom_vrsn" />
    <many-to-one name="Product" class="Material" column="mtrl_id" foreign-key="fk_BOM_Material" unique="true" not-null="true" />

    <map name="Components" table="bom_cmpnnt">
      <key column="bom_id" foreign-key="fk_BOMComponent_BOM" not-null="true" />
      <index column="mtrl_id" type="System.Int32" /> 
      <composite-element class="BOMComponent" />
    </map>
    <many-to-one name="User"  class="User" column="upsrt_usr_id" foreign-key="fk_BOM_User" lazy="false" not-null="true" />
    <property name="Timestamp" column="upsrt_dttm" generated="always" />
  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Inventory.Core"
                   namespace="Inventory.Core.Entities">
  <class name="BOMComponent" table="bom_cmpnnt">
    <id name="Id" column="bom_cmpnnt_id">
      <generator class="hilo" />
    </id>
    <version name="Version" column="bom_cmpnnt_vrsn" />
    <many-to-one name="Component"  class="Material" column="mtrl_id" foreign-key="fk_BOMComponent_Material" not-null="true" />
    <property name="Unit" column="bom_cmpnnt_unt" not-null="true" />
    <property name="Quantity" column="bom_cmpnnt_qntty" not-null="true" />
    <many-to-one name="User"  class="User" column="upsrt_usr_id" foreign-key="fk_BOMComponent_User" lazy="false" not-null="true" />
    <property name="Timestamp" column="upsrt_dttm" generated="always" />
  </class>
</hibernate-mapping>