Nhibernate: Stop it from joining to a table that is not needed

Question

I have two tables (tbArea, tbPost) that relate to the following classes.

class Area
{
    int ID
    string Name
    ...
}

class Post
{
    int ID
    string Title
    Area Area
    ...
}

These two classes map up with Fluent Nhibernate. Below is the post mapping.

public class PostMapping : ClassMap<Post>
{
    public PostMapping()
    {
        Cache.NonStrictReadWrite();

        this.Table("tbPost");

        Id(x => x.ID)
            .Column("PostID")
            .GeneratedBy
            .Identity();

        References(x => x.Area)
            .ForeignKey("AreaID")
            .Column("AreaID");
        ...
    }
}

Any time I perform a query on the Post table "where AreaID = 1(any AreaId)", nhibernate will join to the area table.

(What Nhibernate generates for a query)

SELECT
    post fields
,   area fields (automatically added)
FROM tbPost p
LEFT JOIN tbArea a on 
        p.areaid = a.areaid
where
    p.areaid = 1

I have tried setting Area to LazyLoad, to Fetch.Select, ReadOnly, and any other setting on the reference and still it will always join to Area.

I am trying to optimize the backend database queries, and since I don't need the area object loaded just filtered I would like to eliminate the unnecessary join to Area each time I Query post.

What configurations do I need to change or mappings to get area to still be related to post in my objects, but not query it when I filter on AreaID?

Answer 1

It may not be a duplicate, but it might help you:

http://stackoverflow.com/questions/476405/nhibernate-cant-successfully-set-lazy-loading

Answer 2

What is the query code? This should work.

s.CreateCriteria<Post>()
    .Add(Restrictions.Eq("Area.ID", 1))
    .List<Post>();

You do not need to create an alias when using a many-to-one's id property. Since, the value is already in the Post table. If you attempted this on any other Area property, you would need to CreateAlias().

Answer 3

After continuing to search, I found that the problem with lies with the Nhibernate Linq library rather than my mappings or queries. I imagine the reason why I wasn't able to find more on this problem is because most people using an ORM don't look at the queries that are generated. Anyway, here is the link confirming my problem.

http://codeofrob.com/archive/2009/10/22/why-linq2nhibernate-isnt-ready-for-production-use.aspx

There are two solutions I came up with. While using CreateCriteria did in fact create the correct queries, it didn't work with how I am implementing my access layer. I still needed to perform where statements based on Expressions<Func<T,bool>> statements passed in. The two possible solutions I found are:

1. Use this library for creating criteria out of lambda statements.

   http://code.google.com/p/nhlambdaextensions/

   The lambda extensions didn't function as well as I had hoped, but might work for others with the same problem.

2. In the end the best solution to using Linq with my queries was to download and build the development version of Nhibernate 3. The new Linq provider in NH3 works perfectly and the queries looked just as I would expect.

   http://sourceforge.net/projects/nhibernate/develop

Answer 4

Not familiar with NHibernate, but from what I know it's similar to LINQ.

For example, I have 2 tables:

Customer Order

1 customer has 0-many orders.

Updating an order through LINQ will update an attached customer. In my instance it was inserting a duplicate customer into the table.

I had to implement a Detach() method for in the order class.

pseudo code:

public void Detach()
{
   this._ReferenceToCustomer = emptyCustomerReference;
}

Idea is to detach customer from the order. Is this is what you are after?