1

Some quick nhibernate problem:

I have sql tables:

Item { Id, Name }
ItemRange { Id, Name }
ItemHasItemRange { Id, ItemId, ItemRangeId }

Mappings are simple, so I will not paste them, the ItemId and ItemRangeId are foreign keys, Item class has ItemHasItemRanges collection mapped as lazy bag.

I want all items which are in particular ItemRange, but I do not want to retrieve associated ItemRangeObjects, I just want to do inner join to narrow results.

When I do it like that:

c.CreateCriteria("Item", "i")
  .CreateAlias("ItemHasItemRanges", "ihpr", JoinType.InnerJoin)
  .Add(Restrictions.Eq("ihpr.ItemRange.Id", I18nHelper.CurrentItemRange.Id));

It works fine, but all ItemHasItemRange objects are fetched as well to the Item.ItemHasItemRanges collections (which is mapped as lazy)

I do not want to fetch Item.ItemHasItemRanges, because it takes time. I just want to do inner join to limit result set. It is possible in NHibernate?

Adam
  • 227
  • 2
  • 11
  • What happens when you use `CreateCriteria` instead of `CreateAlias`? – Stefan Steinegger Mar 14 '11 at 09:14
  • IMO, it should work as you expect. You could try to explicitly set the FetchMode in the query (using `SetFetchMode`). – Stefan Steinegger Mar 14 '11 at 09:31
  • I have tried with `.SetFetchMode("ItemHasItemRanges",FetchMode.Lazy);` - generated query is the same. Same thing with `CreateCriteria` - as far I know CreateCriteria differs from CreateAlias only by returning object rooted in new criteria instead of criteria we started with. All fields are still in select list, even those which should be lazy. – Adam Mar 14 '11 at 10:12
  • Wait a second, what do you mean by "all fields (...) even those which should be lazy"? Lazy loading of fields is something special and doesn't work like this, eager loading of lists is implemented by subsequent selects. Do you have a fetch="join" somewhere? – Stefan Steinegger Mar 14 '11 at 10:30
  • Yes, Item class has fetch join with translation objects, but they do not take part in the restrictions, they only have to be fetched. By lazy, I meant that if I mark object field as lazy, it should not be fetched by additional join or sub-select and it's fields should not be in the select list. Maybe I mistunderstood how NHibernate work. – Adam Mar 14 '11 at 10:38
  • The list isn't in the select list (except when using join). That's why I'm confused. How should they be in the select list? (cit: "All fields are still in select list") – Stefan Steinegger Mar 14 '11 at 13:56
  • Still waiting for an answer. Or in other words: how do you determine that the list doesn't get lazily loaded? – Stefan Steinegger Mar 15 '11 at 10:32
  • I meant that NHibernate still loads all ItemRange objects, and initialize theirs collections. I just need to do join by ID. I do not want ItemRange objects, but NHibernate in generated SQL puts `select ... ,item_range.name, ...` and also other fields from item_range table which i didn't list here. – Adam Mar 21 '11 at 11:23
  • In a single query or in subsequent queries? – Stefan Steinegger Mar 21 '11 at 14:44

2 Answers2

0

So I think that you just want to retrieve those objects in order to show an overview / list, and you are not going to actually 'do' something with those objects (unless perhaps loading one of them) ?

In that case, I think that it is better for you to work with 'projections'. Here's the scenario:

  • You'll have to create a (simple) class that just contains the properties that you want to show (where you're interested in).

  • You'll have to 'import' that class into NHibernate, so that NHibernate knows of its existence.

  • Next, you can create your Criteria statement like you have it now. (Working with your domain classes).

  • Then, you should specify how the projection should look like. That is, how the properties of your Item entity map to the properties of your 'DTO'/View class (= the simple class you just created).

  • Specify that an AliasToBean ResultTransformer should be used.

Then, execute your Criteria query. NHibernate will be able to produce the simplest possible query that is needed in order to retrieve all the data that is necessary.

I've explained something similar here

Community
  • 1
  • 1
Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
  • This is good idea, but Item class has some additional other which have to be loaded (joined translations etc). So I want all objects to be normally loaded as they are defined in mappings, but I do not want to load lazy objects, even if I join them in criteria. I tried to force FetchMode to lazy on ItemHasItemRanges, but it has no effect - generated query is the same. – Adam Mar 14 '11 at 09:22
0

I find out the problem was somewhere else. ItemHasItemRange table did not have multiple index on ItemId and ItemRangeId - id only had separate indexes on each field. Thats why performance was so poor.

But NHibernate question is still valid - is it possible to create inner join for criteria only to narrow results and not to fetch all joined objects which normally are lazy.

Adam
  • 227
  • 2
  • 11