Join on tables using Fluent NHibernate

I started using Fluent NHibernate about a year ago as I was experimenting with ORMs in general when a good friend introduced me to the framework.

First of all, I felt that learning Fluent NHibernate in the first place wasn't that easy. I eventually found everything I needed on the internet, but for some reason, it wasn't as easy for me as other frameworks I've dealt with in the past.

Enough said, there were some things that I couldn't find how to do on the internet, like how to join two different tables using Fluent NHibernate mappings. I even spotted some people thirsty for answers...

Check out this question on stackoverflow.com that I found as I was looking for the answer myself, and eventually ended up coming back to answer the question myself! :)

I'll show this using a simple example.

I have two entities:

public class FormStructure
{
    public virtual Int32 FormId { get; private set; }
    public virtual Int32 FormType { get; set; }
    public virtual FormField FieldId { get; set; }
}

public class FormField
{
    public virtual int FieldId { get; private set; }
    public virtual String FieldName { get; set; }
    public virtual int? FieldType { get; set; }
    public virtual int? DisplayOrder { get; set; }
}

Now, I want to be able to create a query on the FormStructure entity, and have the results ordered by the matching DisplayOrder field in the FormField entity. I also want the DisplayOrder field to be available to me as a property of the FormStructure entity.

In order to accomplish this, I will need to create a Join between these two tables. The first step is to add the DisplayOrder field as a property in the FormStructure entity:

public virtual int? DisplayOrder { get; set; }

Then, all I needed to do was use the Join method on my mapping class like this:

public class FormStructureMap : ClassMap<formstructure>
{
    public FormStructureMap()
    {
        Table("FormStructure");

        Id(x => x.Id);
        Map(x => x.FormType);
        References(x => x.Schedule).Column("ScheduleId");
        References(x => x.Field).Column("FieldId");
        Map(x => x.IsMandatory).Nullable();

        Join("FormFields", m =>
        {
            m.Fetch.Join();
            m.KeyColumn("FieldId");
            m.Map(t => t.DisplayOrder).Nullable();
        });
    }
}

Now the query will automatically be created as a join behind the scenes, and I will have the DisplayOrder column available for me in every row.

This might remove some of the rows from the result, if they have NULL values in the added column from the join. To avoid this, add 'm.Optional()' inside the Join method.

In order to query the table with the join like I defined, all I need to do is something like this:

return session.CreateCriteria<formstructure>()
              .Add(Expression.Eq("FieldName", fieldName))
              .AddOrder(Order.Asc("DisplayOrder"))
              .List<formstructure>();

That's all for now.
I think I'll be blogging more about Fluent NHinernate in future posts as well, as I have started using it more heavily lately...