Re: [nhusers] Running a specific query with Criteria API without NHibernate issuing auto SQL queries

[Oskar Berggren]

Have you disabled lazy loading? If so, why?

/Oskar

2012/11/7 Denis Lanza <denis...@gmail.com>

  I am trying to figure out a way to issue a specific query via QueryOver<T>. Basically I have to select a collection of the top level objects based on the bottom-most object. This is what I wrote:

 

var meetings = NHibernateDataContext.QueryOver<Meeting>()
                .Where(meeting => meeting.Id.EventId == roadshowId)
                .JoinQueryOver(m => m.Investors)
                .JoinQueryOver(i => i.Contacts)
                .Where(contact => contact.Id.ContactId == 1 )
                .List();

 

When I view the SQL in the console I see the SQL corresponding to this query issued. After that the SQL for the entities' mapping files is generated and executed automatically. So if this brought back two meetings, it will auto query for Investors for that meeting and Contacts for those Investors. I need to know how to stop NHibernate from issuing that SQL automatically and only issue the SQL for the specified query. I looked into Event Listeners and tried to intercept at PreLoad and cancel the query from the Session object but it should only be cancelled when called from this specific method not every time that entity type is encountered. This also has to work in Sybase. I've been looking for a solution but I can't find one. It could be that I'm just missing something fundamental that I'm doing wrong but I've been banging my head against the wall with this for over a week so I am hoping someone has encountered this situation before. Thanks.

 

Sincerely,

Denis J. Lanza

Technical Architect

Dealogic

New York City

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/sr2IfPuo7JcJ.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Ted P]

I always assumed that this was the default behavior of the QueryOver api.. (include joined entities in the select), whereas the LINQ & HQL api does not, unless requested to do so. 

i.e doing "session.Query<Meeting>().Where(m => m.Investors.Any(i => i.Contacts.Any(c => c.Id == 1))).ToList()" would only fetch the Meeting entities (correctly!).

Enabling Lazy or lazy extra does not help in the case with the QueryOver api, as it will select any joined entities.

I guess we're missing some api to define what entities to include in the select (without resorting to selecting each property), or?

Cheers,

Ted

[xanatos]

You could convert your query to use a subquery. Something like:

            Meeting meeting = null;

            Session.QueryOver<Meeting>(() => meeting)

                .Where(meeting => meeting.Id.EventId == roadshowId)

                .WithSubquery.WhereExists(QueryOver.Of<Investor>().Where(p => p.MeetingId == meeting.Id).JoinQueryOver(i => i.Contacts).Where(contact => contact.Id.ContactId == 1).Select(p => p.Id))

                .List();

You have to join the Investor table with the Meeting table "manually" (so the first Where)