Hi,

First of all I'm pretty new to nHibernate so any help is appreciated.
Second of all I'm using a legacy database so I'm limited to do any changes in the db-design.

Background:

I have 3 tables in my database: employee, employeegroup, employeegroupinfo

- An employee may have one or many employeegroups
- An employeegroup may have one or many employees
- An employeegroup have one employeegroupinfo

I.e a many-to-many relationship.

- PK of employee is employeeid
- PK of employeegroup is employeeid, empgroupid, startdate
- PK of employeegroupinfo is employeegroupid

As I only would like one entity class to represent an EmployeeGroup I
figured a join would be good in my mapping file for employeegroup like so:

(EmployeeGroup mapping file)

<class name="EmployeeGroup" table="employeegroup">

<composite-id>
<key-property name="EmployeeGroupId" column="empgroupid"/>
<key-property name="EmployeeId" column="employeeid"/>
<key-property name="StartDate" column="startdate"/>
</composite-id>

<bag name="Employees" table="employee" lazy="true">
<key column="employeeid" foreign-key="employeeid" not-null="true" unique="true"/>
<many-to-many class="Employee" foreign-key="employeeid" unique="true" column="employeeid"/>
</bag>


<join table="employeegroupinfo">
<key column="empgroupid" unique="true" foreign-key="empgroupid"/>
<property name="Name" column="empgroup_name"/>
</join>


------------------------------------------------------------------------------------------

(Employee mapping file)

<class name="Employee" table="employee" lazy="false">
<id name="Id" column="employeeid" unsaved-value="-1">
<generator class="assigned" />
</id>

<property name="UserName" column="username"/>
<property name="FirstName" column="surname"/>
<property name="LastName" column="lastname"/>

<bag name="EmployeeGroups" table="employeegroup" lazy="false">
<key column="employeeid" foreign-key="employeeid" not-null="true" unique="false"/>
<many-to-many class="EmployeeGroup" foreign-key="employeeid" unique="false" column="employeeid"/>
</bag>

</class>

---------------------------------------------------------------------------------------------

My entity class for Employee looks like this:

public class Employee
{
public virtual int Id { get; set; }
public virtual string UserName { get; set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }

private IList<EmployeeGroup> _employeeGroups = new List<EmployeeGroup>();
public virtual IList<EmployeeGroup> EmployeeGroups { get { return _employeeGroups; } set { _employeeGroups = value; } }
}

---------------------------------------------------------------------------------------------

And my entity class for EmployeeGroup looks like this:

public class EmployeeGroup
{
public virtual int EmployeeGroupId { get; set; }
public virtual int EmployeeId { get; set; }
public virtual DateTime StartDate { get; set; }

public virtual string Name { get; set; }

private IList<Employee> _employees = new List<Employee>();
public virtual IList<Employee> Employees { get { return _employees; } set { _employees = value; } }

public override bool Equals(object obj)
{
if (this.GetType() != obj.GetType()) return false;
if (this.EmployeeGroupId == ((EmployeeGroup)obj).EmployeeGroupId &&
this.EmployeeId == ((EmployeeGroup)obj).EmployeeId &&
this.StartDate == ((EmployeeGroup)obj).StartDate) return true;
return false;
}

public override int GetHashCode()
{
int hash = 13;
hash = hash + (null == this.EmployeeGroupId ? 0 : this.EmployeeGroupId.GetHashCode());
hash = hash + (null == this.EmployeeId ? 0 : this.EmployeeId.GetHashCode());
hash = hash + (null == this.StartDate ? 0 : this.StartDate.GetHashCode());
return hash;
}
}

---------------------------------------------------------------------------------------------

The problem is when I try to fetch some employees I get this error:

Foreign key (employeeid:employeegroup [employeeid]))
must have same number of columns as the referenced primary key
(employeegroup [empgroup_id, employeeid, startdate])

Is it not possible to create assosiations if I don't have all the PK fileds in Employee?
Or am I doing something wrong?

I would appreciate any help since I've been stuck with this the last few days.
If I've been unclear or missed any vital information please let me know.

Regards
sahlen