Just for record : I use C#, Fluent NHibernate, and MySQL 5.
I used to use NHibernate for this project, but recently I decided to use Fluent NHibernate instead. And I got problem in mapping the many to many relationship that references to self with additional column.
I have table Item. Item can has many component which is also from table Item. These components can be used by other item as well. For example, Item A needs two components, which are Item B and Item C. Item B is also used for Item D and Item E, and so on.
I used associative table and I need additional column for their relationship. Because of that, there will be 2 One - Many relations. This is my database structure.
- Item
- ID
- Name
- Prerequisite [ Associative table ]
- ID
- Item_ID [ FK_Item1 ] // item to be made.
- Component_ID [ FK_Item2 ] // component for this item.
- Weight // additional column.
This is my mapping for Prerequisite Table :
public PrerequisiteMap()
{
Id(x => x.ID).GeneratedBy.Native();
References(x => x.Item).Column("Item_ID");
References(x => x.Component).Column("Component_ID");
Map(x => x.Need);
}
This is my mapping for Item table :
public ItemMap()
{
Id(x => x.ID).GeneratedBy.Native();
HasMany(x => x.PrerequisitesParent).KeyColumn("Item_ID").Cascade.All();
HasMany(x => x.PrerequisitesComponent).KeyColumn("Component_ID").Cascade.All ;
Map(x => x.Name);
}
This is my prerequisite class :
public virtual UInt64 ID { get; set; }
// item.
public virtual UInt64 Item_ID { get; set; }
public virtual Item Item { get; set; }
// Component.
public virtual UInt64 Component_ID { get; set; }
public virtual Item Component { get; set; }
// prerequisite properties.
public virtual float Need { get; set; }
And this is my item class :
// Item properties.
public virtual UInt64 ID { get; protected set; }
public virtual string Name { get; set; }
public virtual IList<Prerequisite> PrerequisitesComponent { get; set; }
public virtual IList<Prerequisite> PrerequisitesParent { get; set; }
The problem I have is, whenever I try to save/update Item with prerequisite, the Component_ID and Item_ID always have the same value. I create new Item X, with components Item Y and Item Z, in prerequisite table, I got these :
ID|Item_ID|Component_ID|Need
1 | X | X | 10
2 | X | X | 20
instead of, the expected result
1 | X | Y | 10
2 | X | Z | 20
This is my piece of code when saving :
using (var session = SessionFactoryProvider.OpenSession())
{
using (var trans = session.BeginTransaction())
{
var item = session.Get<Item>((UInt64)1); // Item to be updated.
var item2 = session.Get<Item>((UInt64)2); // Component 1
var item3 = session.Get<Item>((UInt64)3); // Component 2
item.PrerequisitesComponent.Add(new Prerequisite() { Item = item, Component = item2, Need = 100f}); // adding new prerequisite from component 1 (item2)
item.PrerequisitesComponent.Add(new Prerequisite() { Item = item, Component = item3, Need = 100f }); // adding new prerequisite from component 2 (item3)
session.SaveOrUpdate(item);
try
{
trans.Commit();
}
catch(GenericADOException ex)
{
MessageBox.Show(ex.InnerException.ToString());
}
}
}
What could be the problem here? Is it because I reference the same table? or maybe I mapped it wrongly?
I read other similar questions many to many from product to product and many to many self referencing but it seems like they didn't use additional column in their associative table?
Any help is very appreciated, Thank You.
