How to map a group entity class from a "group name" column in NHibernate?

Question

I have a table in my database looking roughly like this:

create table Foo (
    Id             int identity       not null,
    Name           varchar(100)       not null,
    GroupName      varchar(100)       not null,
    constraint PK_Foo primary key (Id)
)

Now I want to map this table into two entity classes like this:

class Foo {
    public virtual in Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual Group Group { get; set; }
}

class FooGroup {
    public virtual string Name { get; set; }
    public virtual ISet<Foo> Foos { get; private set; }
}

Is this possible with NHibernate? I have tried to search the net and NH docs, but I really don't know what to look for - ideas for search queries would be appreciated.

Maybe I can make a mapping for FooGroup that uses a custom HQL/SQL query to select the distinct group names, and another custom query to select the appropriate set of contained items?

Answer 1

I don't think it's possible to do what you want purely in the NHibernate mapping ... although I could be proved wrong by someone else :-)

OTOH it's pretty easy to define a straightforward mapping from your table into:

class RawFooData 
{
    public virtual in Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual string GroupName { get; set; }
}

and then process this to get out the values you want:

IEnumerable<FooGroup> groups = allRawFooData.Select(data => data.GroupName)
                                        .Distinct()
                                        .Select(name => new FooGroup() 
                                                        {
                                                           Name = name
                                                        });

foreach(var group in groups)
{
    group.Foos = allRawFooData.Where(data => data.GroupName == group.Name);
}

I think you will only find performance issues here if you are dealing with very large quantities of data (e.g. 10K rows or more).