NHibernate QueryOver - collection with too many results

Question

Dear NHibernate users,

I've been trying, reading and whatnot for 2 days now, but still can't figure this out, even though I presumed it to be an easy task with QueryOver API.

These are my two entities:

ATTRIBUTE
---------------------------
public int Id { get; set; }
public int LanguageId { get; set; }
public string Title { get; set; }
public IList<Option> Options { get; set; }


OPTION
---------------------------
public int Id { get; set; }
public Attribute Attribute { get; set; }
public int LanguageId { get; set; }
public string Title { get; set; }

The mapping is as follows:

<class name="Attribute" lazy="false" table="attribute" dynamic-insert="true" dynamic-update="true" select-before-update="true">
    <id name="Id" column="attribute_id" type="System.Int32">
        <generator class="native"/>
    </id>

    <bag name="Options" lazy="false" cascade="all">
        <key column="attribute_id" not-null="true" />
        <one-to-many class="Option" not-found="ignore" />
    </bag>

    <join table="attribute_i18n" inverse="true" fetch="join">
        <key column="attribute_id" not-null="true"/>
        <property name="LanguageId" column="language_id" type="System.Int32" not-null="true" />
        <property name="Title" column="title" type="System.String" length="255" not-null="true" />
    </join>
</class>

<class name="Option" lazy="false" table="options" dynamic-insert="true" dynamic-update="true" select-before-update="true">
    <id name="Id" column="option_id" type="System.Int32">
        <generator class="native"/>
    </id>

    <many-to-one name="Attribute" class="Attribute" column="attribute_id" not-null="true" />

    <join table="option_i18n" inverse="true" fetch="join">
        <key column="option_id" not-null="true"/>
        <property name="LanguageId" column="language_id" type="System.Int32" not-null="true" />
        <property name="Title" column="title" type="System.String" length="255" not-null="true" />
    </join>
</class>

Note that both tables join towards its own "i18n"-table (support of multilingual entries), and refer to its language_id column.

Furthermore, I try to use the QueryOver API, and query the Options-property for those options with LanguageId set to 1.

After a lot of hair pulling, my query is back to what I started with:

Attribute attribute = null;
Option option = null;

result = Session.QueryOver(() => attribute)
                .Where(() => attribute.LanguageId == 1)
                .Left.JoinAlias(i => i.Options, () => option)
                .Where(() => option.LanguageId == 1)
                .TransformUsing(Transformers.DistinctRootEntity)
                .List();

So, to my problem: This query keeps on giving me a double set of options in my option list (for language_id 1 and 2). I want to select only language_id = 1, but to my frustration the API doesn't understand me (..or was it the other way around?).

Any help on how to make my query work against my bag-collection of OPTIONs is greatly appreciated! :-) Thanks!

Mikal

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

I am afraid, that his won't work. By design and the nature of the <join>. As stated in the documentation:

5.1.19. join

Using the <join> element, it is possible to map properties of one class to several tables, when there's a 1-to-1 relationship between the tables.

The way how to (I do) solve this issue is a bit different. I have an object Language, the Option has colleciton of languages

public virtual IList<Language> Languages {get; set;}

The mapping is for example <bag>

<bag name="Languages"  batch-size="25"
  cascade="all-delete-orphan" inverse="true" >
  <key column="option_id" />
  <one-to-many class="Language" />

  <filter name="LanguagFilter" condition=":languageId = LanguageId" />
</bag>

The trick is in the filter. This is a dynamic version of the where mapping-attribute (18.1. NHibernate filters)

<filter-def name="LanguageFilter" >
  <filter-param name="languageId" type="Int32" />
</filter-def>

Then, we can turn the filter on, for all operations of the current session. Only once per request (if web app), inside some AOP, where we know the language id:

var filter = session.EnableFilter("LanguageFilter");
filter.SetParameter("languageId", theCurrentLanguageIdFromUser);

And finally, we know, that the collection Languages contains only one record and we can always access .First(). No more multiple results with more languages

Also see: https://stackoverflow.com/a/16625867/1679310, https://stackoverflow.com/a/18479266/1679310

edited May 23 '17 at 12:06

Community

1
1

answered Sep 13 '13 at 13:47

Radim Köhler

122,561
47
239
335

Thank you for that. I will test it once back at work, but it looks promising. Two quick followup questions, if you dont mind: Would you use one Translation table for each entities, namely attribute_i18n and option_i18n , or would it be better (also performance vice) to use one translation table for all main classes that needs translation, and have an extra column you include in the where-claus. Whats your opinion? Secondly; I see you put batch-size in the mapping. What is the advantage of this property? Thank you! – mikal Sep 13 '13 at 14:52
batch-size is awesome setting, helping us to avoid the N+1 problem. I.e. if the Options are loaded, the Languages collections are loaded for them in batches... cool and efficient feature ;) The issue with table is opinion and project based. But what I do, is to reduce amount of tables as possible (like in this case) and use `where` attribute and `discriminator` column to distinguish to which object *table-row* belongs to. Good luck with NHibernate. PS: check this http://stackoverflow.com/a/14801256/1679310 – Radim Köhler Sep 13 '13 at 14:56
Seems like I dont have enough SO reputation to upvote your answer, but greatly appreciated. Thanks!:) – mikal Sep 13 '13 at 15:09
Radim: I've changed my table structure and entities to support this now. It almost work like a charm, except I have problems inserting new Language-entries. I guess the LanguageId-column key constraint fails on insert, as I get this error msg: Cannot add or update a child row: a foreign key constraint fails. As its related to your answer, may I ask how you solved this problem? Thanks:) – mikal Sep 14 '13 at 18:19
You should issue new question, there are many smart guys ready to help. In this case (during creation), we have to populate complete list of all Languages, which will later be filtered. And also, when filling them into collection, we have to assigne them reference to their holder. I am using the `Template method pattern`, and in there initiating all the stuff. – Radim Köhler Sep 15 '13 at 03:53

NHibernate QueryOver - collection with too many results

1 Answers1

Linked