C# – NHibernate mapping with strings, no real foreign keys

cnhibernatesql

I've got a class "Lab" which represents a Department of a company that uses my program. It's got a child collection called "Employees" which is meant to contain all the Employees that belong to that Lab. The employee table contains a string field called "Department" which represents which department they belong to, and the Lab table contains a string field called "Department" which represents which department the Lab is. An employee belongs to a Lab when the employee's department matches the lab's department.

I realize that having the employee table contain a LabID field would be the preferable approach, however the employee table is not under my control and I do not want to modify it. I would prefer to do this without adding a mapping table of employee to lab, since department against department provides the same result.

This is the abridged mapping that I'm trying to use.

  <class name="Lab">
    <id name="LabID">
      <generator class="native" />
    </id>
    <property name="Department" />
    <!--- other properties --->
    <set name="Employees" table="Employee_copy" generic="true" inverse="true">
      <key column="Department" property-ref="Department" />      
      <one-to-many class="Employee" />
    </set>
  </class>

the error that is returned is

Identifier type mismatch; Found:<System.Int32> Expected:<System.String> Parameter name: identifier

The generated query runs fine.

SELECT employees0_.Department as Department1_, employees0_.EmployeeID as EmployeeID1_, employees0_.EmployeeID as EmployeeID30_0_, employees0_.CommonName as CommonName30_0_, employees0_.LastName as LastName30_0_, employees0_.NetUserID as NetUserID30_0_, employees0_.CompanyID as CompanyID30_0_, employees0_.Department as Department30_0_ FROM Employee employees0_ WHERE employees0_.Department='IS'

so what am I doing wrong?

EDIT:

Note also that the reverse association works fine.

<many-to-one name="Lab" column="Department" property-ref="Department" class="Lab" />

The associated properties for both objects-
Lab:

public virtual int LabID { get; set; }
public virtual string Department { get; set; }
public virtual ISet<Employee> Employees { get; set; }

Employee:

public virtual int EmployeeID { get; protected set; }     
public virtual string Department { get; protected set; } 
public virtual Lab Lab { get; protected set; }  

Best Answer

Since the Employee table was intended to be immutable, I ended up creating a view that got the Lab ID based on the department, and linking to that instead of the employee table directly.

Meh. Works.