How can I get a list of entities in NHibernate where the ParentId might refer to different tables

Question

I'm new to NHibernate so I'm sure that I'm just missing something fundamental.

I have an table called Issue that has a ParentId column. The ParentId can refer to different tables (i.e. - Project or Customer, etc.) How can I do that query in NHibernate so that I can show only the Issues that belong to Project. Here is what I've tried.

            DetachedCriteria dCriteria = DetachedCriteria.For<Issue>("issue")
            .SetProjection(Projections.Property("ParentId"))
            .CreateAlias("Project", "project", NHibernate.SqlCommand.JoinType.InnerJoin)
            .Add(Restrictions.EqProperty("issue.ParentId", "project.Id"))
            ;
        var issues = Session.CreateCriteria<Issue>("issue")
                .Add(Subqueries.Exists(dCriteria)).List<Issue>();
        return issues;

my mapping looks like this. Notice I don't have any reference to the parent object because I don't know what it will be.

    <class name="Issue" table="dbo.Issue" lazy="true">
    <id name="Id" column="Id">
        <generator class="assigned" />
    </id>

    <property name="ParentId" column="ParentId" />

    <property name="Name" />

    <property name="Description" />

I would appreciate any guidance.

perhaps I should explain a little more. I have a grid of all issues and I want to show a type column so we know what type of issue (project, etc.) the column doesn't serve any other purpose than this one display so I don't believe it's valid to add it to the database. In SQL it's easy enough to filter the data via the join or using Exists. There must be a similar method in NHibernate so I don't have to loop through every Project for all it's issues.

Answer 1

This is a horrible design. You should at least use different columns for the relations or an other column to identify the type or the parent object.

If you do that, you will be able to map it cleanly with NHibernate.

If you can't for some reason, maybe an any-mapping is what you are looking for.

Answer 2

1. You shouldn't be using Foreign Key Ids directly in your objects - That's a data centric approach. With NHibernate you work with real objets or collections of real objects to represent the relationships. Nhibernate takes care of the foreign Keys in your DB automatically

2. I would use an interface or base class to represent the different items that can be used as the parent if you really need to the Issue to have a link back to it's parent. But probably you don't as you'll most likely be fetching e.g. a 'customer' and then iterating through the issues.

3. Assigned Ids are generally a bad idea. Better to use something like HiLo or the the DBs Native generator.

For instance, your objects might look something like this:

public class Issue
{
   public int Id{ get; set; }
   public IHazIssues Parent { get; set; } //If you really need this
   public string Name { get; set; }
   public string Description { get; set; }
}

public class Customer : IHazIssues 
{
   public int Id{ get; set; }
   public IList<Issue> Issues { get; set; }
   public string NAme{ get; set; }
}

//If you don't need the parent mapping on Item you don't need this.
public interface IHazIssues 
{
   IList<Issue> Issues { get; set; }
}

etc...