HQL equivalent of SetFetchMode and CreateAlias query

dimitrod · **Joined:** Tue Jun 10, 2008 10:22 am **Posts:** 4

Hello,

I have been using NHibernate for some time and I am very satisfied with it. Recently I have come to a problem for which I couldn't find a solution. I have a User class with a collection of Accounts. I would like to build a query which would return all users (with their Accounts collection eagerly loaded), that have an account opened before some date.

The following query would work but it returns all users:

Code:

ISession session = factory.OpenSession();
IList<User> users = session.CreateCriteria(typeof(User))
  .SetFetchMode("Accounts", FetchMode.Join)
  .List<User>();
session.Close();
int accountsNumber = users[0].Accounts.Count; // works here but I don't know how to perform restrictions on the Accounts collection in my query.

The following works inside the session but once the session is closed I get a LazyInitializationException:

Code:

ISession session = factory.OpenSession();
IList<User> users = session.CreateCriteria(typeof(User))
  .SetFetchMode("Accounts", FetchMode.Join)
  .CreateAlias("Accounts", "a")
  .Add(Expression.Le("a.OpenDate", date))
  .List<User>();
session.Close();
int accountsNumber = users[0].Accounts.Count; // get LazyInitializationException here

From what I have seen so far, it seems that it is impossible to use SetFetchMode and CreateAlias on the same query, because CreateAlias always overrides the SetFetchMode behavior. If I define the Accounts collection as with lazy="false" in the mapping file, then everything works as expected, but I don't want to load the Accounts collection every time, only for this particular query. Is it possible to perform this query with HQL instead of using the criteria API? Can I eagerly load the collection and perform restrictions on it? A similar issue has been discused in this post. To resolve the issue it was suggested to remove the CreateAlias and use a long notation, but I couldn't understand what this long notation would be. Any help would be greatly appreciated.

Regards,
Darin

wolli · **Posted:** Tue Jun 10, 2008 12:49 pm

I'm not sure either, but I think CreateCriteria instead of CreateAlias is meant:

Code:

IList<User> users = session.CreateCriteria(typeof(User))
  .SetFetchMode("Accounts", FetchMode.Join)
  .CreateCriteria("Accounts", "a")
  .Add(Expression.Le("a.OpenDate", date))
  .List<User>();

When you want to use HQL:

Code:

from User u join fetch u.Accounts a where a.OpenDate >= :date

dimitrod · **Joined:** Tue Jun 10, 2008 10:22 am **Posts:** 4

wolli,

Thanks for your reply. Actually CreateCriteria behaves the same as CreateAlias, I have tried both. I also tried the suggested HQL query:

Code:

from User u join fetch u.Accounts a where a.OpenDate >= :date

and it actually loaded the Accounts collection, but it contained only the accounts that have OpenDate >= :date, whereas I would like to have all the accounts (and at least one account satisfies the condition). Here's an example:

Code:

users table:
user_id
1
2
3

Code:

accounts table:
account_id   user_id   account_open_date
1                 1           2008-06-01
2                 1           2008-05-01
3                 2           2008-06-10

I would like to have all users that poses an account opened before 2008-06-01 and all the users that satisfy this condition have its account collection eagerly loaded.

After the query the "users" variable should look like this:

Code:

users.Count = 1
users[0].Id = 1
users[0].Accounts.Count = 2
users[0].Accounts[0].Id = 1
users[0].Accounts[1].Id = 2

I hope my example was clear.

wolli · **Posted:** Tue Jun 10, 2008 2:14 pm

Then try

Code:

from User u join fetch u.Accounts where
where :date >= some elements(u.Accounts)

dimitrod · **Joined:** Tue Jun 10, 2008 10:22 am **Posts:** 4

Code:

from User u join fetch u.Accounts where
where :date >= some elements(u.Accounts)

This HQL did exactly what I was looking for. Thank you very much for your suggestion. Is this HQL possible with the criteria API?

dimitrod · **Joined:** Tue Jun 10, 2008 10:22 am **Posts:** 4

Code:

from User u join fetch u.Accounts where
where :date >= some elements(u.Accounts)

In fact I realized that the OpenDate property is never used in this query and the restriction is not correctly made.

wolli · **Posted:** Wed Jun 11, 2008 2:22 am

Sorry, the HQL was stupid, what I really meant was this:

Code:

from User u join fetch u.Accounts where (from accounts a where a.OpenDate <= :date) in some elements(u.Accounts)

You can use a subquery for that in the criteria API, but I really wonder why your initial approach didn't work. According to the documentation (chapter 12.5) and my understandings that should definitely work ! Which version of NHibernate do you use ?

Quote:

Note that the kittens collections held by the Cat instances returned by the previous two queries are not prefiltered
by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResult-
Transformer(CriteriaUtil.AliasToEntityMap).

Can you try:

- use List() instead of List<User>()
- session.CreateCriteria(typeof(User),"u").SetFetchMode("u.Accounts",FetchMode.Join)

I suggest, you open an JIRA issue about that.