Loading a collection of union-subclass entities polymorphically - column specified multiple times

Question

Take the following entities:

public class Company : Entity<Guid>
{
    public virtual string Name { get; set; }
    public virtual IList<IEmployee> Employees { get; set; }

    public Company()
    {
        Id = Guid.NewGuid();
        Employees = new List<IEmployee>();
    }
}

public interface IEmployee
{
    Guid? Id { get; set; }
    string Name { get; set; }
    void Work();
    Company Company { get; set; }
}

public class ProductionEmployee : Entity<Guid>, IEmployee
{
    public virtual string Name { get; set; }
    public virtual Company Company { get; set; }

    public ProductionEmployee()
    {
        Id = Guid.NewGuid();
    }

    public virtual void Work()
    {
        Console.WriteLine("I'm making the stuff.");
    }
}

public class SalesEmployee : Entity<Guid>, IEmployee
{
    public virtual string Name { get; set; }
    public virtual Company Company { get; set; }

    public SalesEmployee()
    {
        Id = Guid.NewGuid();
    }

    public virtual void Work()
    {
        Console.WriteLine("I'm selling the stuff.");
    }
}

Mapped in the following way in NHibernate:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               namespace="PolymorphicUnionSubclass.Domain.Entities"
               assembly="PolymorphicUnionSubclass.Domain">
  <class name="Company" table="`Company`">
    <id name="Id" column="Id" type="guid">
      <generator class="assigned"/>
    </id>
    <property name="Name" column="`Name`"/>

    <bag name="Employees" inverse="true" cascade="save-update">
      <key column="CompanyId"></key>
      <one-to-many class="IEmployee" />
    </bag>
  </class>
</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               namespace="PolymorphicUnionSubclass.Domain.Entities"
               assembly="PolymorphicUnionSubclass.Domain">
  <class name="IEmployee" abstract="true">
    <id name="Id" column="Id" type="guid">
      <generator class="assigned"/>
    </id>

    <many-to-one name="Company" column="`CompanyId`" cascade="save-update"/>

    <union-subclass name="ProductionEmployee" table ="`ProductionEmployee`" >
    </union-subclass>

    <union-subclass name="SalesEmployee" table ="`SalesEmployee`">
    </union-subclass>

  </class>
</hibernate-mapping>

If I create a Company entity and add IEmployee entities to its collection (also setting the Company property of the IEmployee entity to create the bi-directional relationship), Then when I save the company, everything goes into the database as expected. The companyId is set correctly on the PoductionEmployee and SalesEmlpoyee records.

But when I come to load it, I get the following error:

The column 'CompanyId' was specified multiple times for 'employees0_'

The generated SQL looks like this:

SELECT employees0_.CompanyId as CompanyId1_, employees0_.Id as Id1_, employees0_.Id as Id9_0_, employees0_.[CompanyId] as CompanyId2_9_0_, employees0_.clazz_ as clazz_0_ 
FROM ( select Id, CompanyId, CompanyId, 1 as clazz_ from [ProductionEmployee] union all select Id, CompanyId, CompanyId, 2 as clazz_ from [SalesEmployee] ) employees0_ 
WHERE employees0_.CompanyId=?

Why is it generating the CopmanyId column twice and how do I prevent this?

Answer 1

Nothing to do with union-subclass in the end. The problem was in the one-to-many collection I had specified column="CompanyId", and in the many-to-one I had specified column="`CompanyId`". Including the backticks in one and not the other had caused NHibernate to think they were different column. Never come across this in all my time using NHibernate.