How to join table in fluent nhibernate

Question

how do we do this but fluently? i know i can use 'References' but i don't need all the columns from the related table. i just need one property.

Answer 1

As far as I know, this is not supported in Fluent NHibernate, like many other legacy-database specific mappings. I'm afraid you have to switch back to hbm.xml or mix fluent mappings with hbm.xml

Answer 2

Legacy? Last time I checked, Fluent NHibernate was far from legacy.

Answer 3

What Paco said is not right. This can be done in Fluent NHibernate. I searched the web myself quite a while, couldn't find anyone talking about this option, so i just played around with FNHibernate a little and finally managed to do it.

This was my scenario :

I have two tables -

1. "FormFields" => Columns { "FieldId", "FieldName", "FieldType", "DisplayOrder" }
2. "FormStructure" => Columns { "FormId", "FormType", "FieldId" }

These were my 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; }
}

I have a couple of methods in my query that return a list of FormStructure objects. I wanted these methods to give me them ordered by the DisplayOrder field in the FormField object, and wanted the DisplayOrder available as a property in my FormStructure object for other reasons as well.

This basically means I needed to join the tables so that I would retrieve all the columns from the FormStructure table along with the DisplayOrder column from the FormField table, joining them on the matching FieldId columns.

What I did to solve this : 1. I added a property called DisplayOrder to my FormStructure object.

public virtual int? DisplayOrder { get; set; }

1. I added the Join method to my FormStructure mapping class so it looked like this.

   public class FormStructureMap : ClassMap<FormStructure>
   {
       public FormStructureMap()
       {
           Table("FormStructure");
   
   
       Id(x =&gt; x.Id);
       Map(x =&gt; x.FormType);
       References(x =&gt; x.Schedule).Column("ScheduleId");
       References(x =&gt; x.Field).Column("FieldId");
       Map(x =&gt; x.IsMandatory).Nullable();
   
   
       Join("FormFields", m =&gt;
       {
           m.Fetch.Join();
           m.KeyColumn("FieldId");
           m.Map(t =&gt; t.DisplayOrder).Nullable();
       });
   }
   
   
   }
   

Edit : The Join method will obviously join between the two tables on the column you defined in the KeyColumn method within the Join. This will also remove some of the rows that have null values. In order to avoid this (I ran into this recently) you can add m.Optional(); inside the Join method.

I could now retrieve a list of FormStructure objects, order them by DisplayOrder and even have DisplayOrder available as a property in the FormStructure object.

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

This couldn't have been done before, because it wouldn't have recognized the "DisplayOrder" column in the Order clause I have there.

Hope this helps! :)

Answer 4

I know this is obvious, but in gillyb's response, be aware that =&gt and semicolon should read "=>". The auto conversion just goofed it up.

Excellent post!!!