Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Join on tables using Fluent NHibernate

, 29 Jan 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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! Smile | :)

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...


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gilly Barr
Web Developer
Israel Israel
Started programming e-commerce sites with PHP & MySQL at the age of 14. Worked for me well for about 5 years.
 
Transfered to C# & asp.net, while serving in the IDF.
 
Currently working as a web developer for Sears Israel (SHC).
 
Check out my blog!

Comments and Discussions

 
GeneralManaging CRUD Operations in Fluent Nhibernate PinmemberPaul Brower8-Feb-11 9:13 
GeneralRe: Managing CRUD Operations in Fluent Nhibernate PinmemberGilly Barr9-Feb-11 8:32 
GeneralRe: Managing CRUD Operations in Fluent Nhibernate PinmemberSimon_Whitehead24-Mar-12 21:10 
GeneralRe: Managing CRUD Operations in Fluent Nhibernate PinmemberGilly Barr31-Mar-12 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 29 Jan 2011
Article Copyright 2011 by Gilly Barr
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid