NHibernate insert new record with a null many-to-one mapping

Question

I have a Companies table mapped to a Company class and a Websites table mapped to a Website class. My Companies table is basically non-moving, however I'm trying to insert a new Website record with the following:

var website = new Website
{
    Active = true,
    SiteRoot = "example.com",
    CompanyId = 100     
};
var repo = new WebsiteRepository();
repo.Add(website);

The website class has a CompanyId property which is what maps back to the ID column in the Companies table, but because the Website class has a many-to-one mapping, it's trying to insert the website.Company.Id property into the table(which is null) instead of taking the website.Id property.

There are no foreign keys involved as the tables are in 2 separate databases.

This is the error I'm receiving:

NHibernate.Exceptions.GenericADOException: could not insert: [Models.Website][SQL: INSERT INTO Database1.dbo.Websites (CompanyID, SiteRoot, Active) VALUES (?, ?, ?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'CompanyID', table 'Database1.dbo.Websites'; column does not allow nulls. INSERT fails.

NHibernate: INSERT INTO Database1.dbo.Websites (CompanyID, SiteRoot, Active) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY();@p0 = NULL [Type: Int32 (0)], @p1 = 'example.com' [Type: String (4000)], @p2 = True [Type: Boolean (0)]

Here are the mappings:

<hibernate-mapping assembly="Models" namespace="Models" xmlns="urn:nhibernate-mapping-2.2" schema="Database1.dbo">
  <class name="Website" table="Websites" lazy="true" >
    <id name="Id" column="ID">
      <generator class="identity" />
    </id>
    <many-to-one name="Company" column="CompanyID" fetch="join"/>
    <property name="SiteRoot">
      <column name="SiteRoot" sql-type="nvarchar" length="50" not-null="false" />
    </property>
    <property name="Active">
      <column name="Active" sql-type="bit" not-null="true" />
    </property>
  </class>
</hibernate-mapping>

<hibernate-mapping assembly="Models" namespace="Models" xmlns="urn:nhibernate-mapping-2.2" schema="Database2.dbo">
  <class name="Company" table="Companies" lazy="true" >
    <id name="Id" column="ID">
      <generator class="identity" />
    </id>
    <property name="CompanyId">
      <column name="CompanyID" sql-type="int" not-null="true" />
    </property>
    <property name="CompanyName">
      <column name="CompanyName" sql-type="nchar" not-null="false" />
    </property>
    <set name="Websites" table="Websites" schema="Database1.dbo" lazy="false">
      <key column="CID"/>
      <one-to-many class="Models.Website, Models" />
    </set>
  </class>
</hibernate-mapping>

Website class has CompanyId property with int type i guess, so in your mapping you have CompanyID with type Company, CompanyId property must be Company type instead of int type — DanielVorph
If I did that I wouldn't be able to map the company to the website because the website would have no CompanyId field from the DB? There must be a flag or something when saving to say "Take X from property why instead of class Z" ? — Siada
Is there any code in the Website class? What is behind this dubious CompanyId property? It is not mapped to the database. There is most probably something wrong in the way you handle references. — Stefan Steinegger

Stefan Steinegger Stefan Steinegger · Accepted Answer · 2016-06-03T10:56:17

You need to assign a company to the Company property to implement the many-to-one relation. Something like this:

var website = new Website
{
    Active = true,
    SiteRoot = "example.com",
    Company = session.Load(100);
};

Or, if you like, use a repository:

var website = new Website
{
    Active = true,
    SiteRoot = "example.com",
    Company = CompanyRepository.Get(100);
};

Note:

session.Load doesn't load anything from the database if lazy loading is enabled. It only creates a proxy. This is very efficient. At the end it only creates an INSERT statement for the Website with a foreign key of 100, without loosing the object oriented programming style in your code.
Never access foreign keys in the class model. Foreign keys are managed by NHibernate and do not exist in an object oriented model.

NHibernate insert new record with a null many-to-one mapping

2 Answers