Fluent NHibernate / NHibernate Mappings

Question

I have a parent table and relationship table to find child parent relationship. I need to find child, parent and its siblings...

EMPLOYEE_RELATION has columns EMPLOYEE_ID and PARENT_ID that saves the relationship.

Here's what SQL looks like. How do I map this to Fluent NHibernate / NHibernate.

select 
   V1.PARENT_ID AS PARENT_ID, 
   V.EMPLOYEE_ID AS EMPLOYEE_ID,
   V2.EMPLOYEE_ID AS SIBLINGS_ID 
 FROM
    EMPLOYEE V
 INNER JOIN EMPLOYEE_RELATION  V1
   ON  V.EMPLOYEE_ID = V1.EMPLOYEE_ID
 INNER JOIN EMPLOYEE_RELATION V2
   ON V2.PARENT_ID = V1.PARENT_ID
 WHERE V.EMPLOYEE_ID = 6357

Answer 1

Disclaimer: I am a java Hibernate user; I have not used NHibernate but I assume it is very similar.

Some pseudocode: Assuming you have two entity objects, like:

public class EmployeeRelation {
 Employee Parent //parent property
 Employee Employee //child property
}

public class Employee {
 int EmployeeID
 ...
}

You'll want to map each property on the EmployeeRelation using a many-to-one relationship (many EmployeeRelations can point to the same Employee). The hibernate docs detail the config info for this kind of relationship.

Then you can do (this is Java-style code so it may be different for NH): Criteria c = session.createCriteria(Employee.class); c.add(Restrictions.eq("EmployeeID", 6357)); Employee myEmployee = (Employee)c.uniqueResult();

Then, to get the siblings (all relations for this parent): c = session.CreateCriteria(EmployeeRelation.class); c.add(Restrictions.eq("Parent", myEmployee)); List siblings = (List)c.uniqueResult();

This is off the top of my head; you may be able to find a more elegant way to do it all in one query using HQL for example. If each Employee can only have a single parent, you could also look at mapping the Employee with a collection of other Employee objects and a parent Employee reference, instead of using an EmployeeRelation object.

HTH!

Answer 2

Hello, you should be able to create graph with using many-to-many approach with your cross table EMPLOYEE_RELATION. In other words after you've fetched some entity you should be able to fetch all children attached to it as simple list of the same objects as parent is. And you should be able to traverse it further, but I'm not completely sure.

Here is how it might look in your mapping file:

        HasManyToMany(x => x.Employees)
            .WithTableName("EMPLOYEE_RELATION")
            .WithParentKeyColumn("PARENT_ID")
            .WithChildKeyColumn("EMPLOYEE_ID")
            .Cascade.AllDeleteOrphan()
            .LazyLoad();