1

I am new to NHibernate and I am finding problem mapping two table relationships. I have two tables cafe_table and cafe_table_group in SQL Server database. Both of them have auto-increment and Identity columns for their unique ID's .

Although foreign key is shown in the table, I will not have it.

This is my cafe_table.hbm.xml file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="CafePOS" namespace="CafePOS" xmlns="urn:nhibernate-mapping-2.2">
  <class name="CafePOS.CafeTable" table="cafe_table" lazy="true" >
    <id name="cafe_table_id" column="cafe_table_id">
      <generator class="identity" />
    </id>
    <property name="cafe_table_group_id">
      <column name="cafe_table_group_id" sql-type="decimal" not-null="false" />
    <many-to-one name="cafe_table_group" class="CafePOS.CafeTableGroup" column="cafe_table_group_id" fetch="select"/>
  </class>
</hibernate-mapping>

My cafe_table.hbm.xml file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="CafePOS" namespace="CafePOS" xmlns="urn:nhibernate-mapping-2.2">
  <class name="CafePOS.CafeTableGroup" table="cafe_table_group" lazy="true" >
    <id name="cafe_table_group_id" column="cafe_table_group_id">
      <generator class="identity" />
    </id>
    <set name="cafe_table" table="`cafe_table`">
      <key column="cafe_table_group_id"/>
      <one-to-many class="CafePOS.CafeTable"  fetch="select"/>
    </set>
  </class>
</hibernate-mapping>

cafe_table will have many-to-one relationship with cafe_table_group on cafe_table_group_id and cafe_table_group will have one-to-many relationship with cafe_table.

My model CafeTable class: namespace CafePOS {

    public class CafeTable {
        public virtual decimal cafe_table_id { get; set; }
        public virtual decimal cafe_table_group_id { get; set; }
        public virtual CafeTableGroup cafe_table_group { get; set; }
    }
}

My CafeTableGroup model class:

namespace CafePOS {
    public class CafeTableGroup {
        public virtual decimal cafe_table_group_id { get; set; }
        public virtual string cafe_table_group_name { get; set; }
        public virtual IList<CafeTable> cafe_table { get; set; }
    }
}

I just want this relationship for SELECT statement similar to INNER JOIN in SQL query.

Dot Net developer
  • 436
  • 1
  • 5
  • 19
  • OT: If its not an requirement, then avoid hbm files. They are error prone and difficult to read. You can use the inbuilt FluentMapping or FluentNHibernate to map your entities. And it's common in C# to name your Properties in UpperCamelCase and without underscores. – Rabban Nov 07 '17 at 10:31

1 Answers1

1

In a child table, we should map reference as editable, and its ID just as readonly

<class name="CafePOS.CafeTable" table="cafe_table" lazy="true" >
    <id name="cafe_table_id" column="cafe_table_id">
      <generator class="identity" />
    </id>
    <property name="cafe_table_group_id" insert="false" update="false">
      <column name="cafe_table_group_id" sql-type="decimal" not-null="false" />
    </property>
    <many-to-one name="cafe_table_group" 
         class="CafePOS.CafeTableGroup" 
         column="cafe_table_group_id" fetch="select"/>
  ...

And parent should have collection without fetch mode - and also, cascade should be used...

<class name="CafePOS.CafeTableGroup" table="cafe_table_group" lazy="true" >
    <id name="cafe_table_group_id" column="cafe_table_group_id">
      <generator class="identity" />
    </id>
    ...
    <!--<set name="cafe_table" table="`cafe_table`">-->
    <set name="cafe_table" table="`cafe_table`" cascade="all-delete-orphan" >
      <key column="cafe_table_group_id"/>
      <!--<one-to-many class="CafePOS.CafeTable"  fetch="select"/>-->
      <one-to-many class="CafePOS.CafeTable" />
    </set>

Now with assigning both sides

var parent = ...;
var child = ...;
parent.cafe_table.Add(child);
child.cafe_table_group = parent;
// this will be enough.. because cascade will do the rest in proper order
session.SaveOrUpdate(parent);

Here is much more detailed explanation how to handle this:

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • sir but untill I do transaction.commit() while adding a new CafeTable, cafe_table_group_id is available . After I perform commit, I find 'cafe_table_group_id' null in database. I have a foreign key as in the above figure. – Dot Net developer Nov 07 '17 at 10:49
  • No.. you are lucky guy! That is why you should keep the reference as editable.. and even more.. we have to ask NHibernate to do the cascade... check the update... – Radim Köhler Nov 07 '17 at 11:00
  • Sorry sir, I am having a problem . This is what I did: ** var parent = new CafeTableGroup(); var child = _table; parent.cafe_table.Add(child); child.cafe_table_group = parent; session.SaveOrUpdate(parent); ** I get object reference not set to instance of an object error from ** parent.cafe_table.Add(child);** . I just wanted to add **cafe_table_group_id** in **cafe_table table** What should my code be to save. Please update your answer once – Dot Net developer Nov 08 '17 at 06:33
  • object reference not set... which reference? Is parent created? is child properly created? There is not so much magic in my code.. it is really a standard. so, I need to know more about your latest exception - http://nhibernate.info/doc/nh/en/index.html#collections there is the SAME example – Radim Köhler Nov 08 '17 at 06:36
  • I used var parent = session.Get(Convert.ToDecimal(_table.cafe_table_group_id)); too instead of creating new instance of CafeTableGroup – Dot Net developer Nov 08 '17 at 06:36
  • but it seems, that the RESULT of your query is NULL. right? just adjust that query (that is not part of the question) simply be sure you loaded parent... then create child.. assign both sides and with cascade mapping .. all should work – Radim Köhler Nov 08 '17 at 06:37
  • 1
    Thank you very much sir. – Dot Net developer Nov 08 '17 at 07:00