R – CreateQuery and CreateCriteria generating different SQL queries

fluent-nhibernatenhibernatenhibernate-mapping

Client has a Report, Configuration etc properties. A client can have only one of each. Also, each report can belong to only one Client. This is in a way a one-to-one relationship. Therefore my Report table has a foreignkey column clientID. Same for the Configuration and other tables.

Now as per the definition of one-to-one that i read on the nhibernate site, it means that both the primary keys of Report and Client should be the same. Lets just assume that I cannot implement it that way. Hence to simulate the structure that I have in the database, I have the following mappings:

ReportMap

References(x => x.Client, "clientID").Unique().Not.Nullable();

ClientMap

HasOne(x => x.Report).PropertyRef(x => x.Client).LazyLoad().Cascade.SaveUpdate();

Now the problem I am facing is that when I query for a Client, NHibernate is also generating queries to get the Report, Configuration etc… Also, depending on whether I use Criteria or HQL, the generated queries vary wildly.

var client = session.CreateQuery("from Client as c where c.Id = :clientId")
                    .SetParameter("clientId", 1L)
                    .UniqueResult<Client>();

generates one query for the client followed by one query for each property that I mapped as HasOne. ie 2 more queries. One for Report and one for Configuration. HQL generates a total of 3 queries.

However, if I use the Load method or Criteria, it generates one query which joins all the concerned tables.

Despite mapping these collections to be lazy loaded, why is NHibernate fetching them? I really only want information from the Client table.

Whats the logical explanation to this?

From the Nhibernate documentaion on fetching strategies, i understand that single associations are lazy proxy fetched. and that the default fetch strategy of select is executed only when the association is accessed. In my case i am not accessing the association. I am simply reading properties that belong to the Client.

All this is so confusing…

Edit1: I have mapped my one to one relation as mentioned in the nhibernate documentation.
http://nhibernate.info/doc/nh/en/index.html#mapping-declaration-onetoone

There are two varieties of one-to-one
association:

* primary key associations
* unique foreign key associations

Alternatively, a foreign key with a unique constraint, from Employee to Person, may be > expressed as:

<many-to-one name="Person" class="Person" column="PERSON_ID" unique="true"/>

And this association may be made bidirectional by adding the following to the Person >mapping: 

<one-to-one name="Employee" class="Employee" property-ref="Person"/>

So technically as i understand, i am not doing anything wrong. This scenario is supposed to be supported by nhibernate.

Best Answer

I'm not sure what the problem is with your query, but I suggest you change your mapping. You have is a one-to-many relationship between Client-Report and a business rule that a client can have only one report. You should map it as such. The reports collection can be mapped as a private member and you can expose a Report property on Client to enforce the business rule. I expect that mapping it this way will resolve your query problem.

You could also map it in the other direction witht Client on the many side if that makes more sense.

Related Topic