sql alias to dictionary returns duplicate results

I have following class

<class name="Product" table="Product">
<id name="ID" />
...
<map name="CustomFields" table="CustomFieldView">
     <key column="RECORDID" />
     <map-key column="CFName" type="String" />
     <element column="CFValue" type="String" />
</map>
</class>

and SP to select product with CustomFields dictionary

 <sql-query name="GetProducts">
        <return alias="p" class="Product" />
        <return-join alias="cf" pr开发者_运维问答operty="p.CustomFields" />

        SELECT {p.*}, {cf.*}

        FROM Product p
            INNER JOIN CustomFieldView cf ON p.ID = cf.RECORDID

        // WHERE
    </sql-query>

when I select single product like WHERE ID = 1234, then it works as expected - returns one Product with populated CustomFields Dictionary property. But when I select not single Product like WHERE ID IN (18780, 21642) or other criterias then I get Products duplicated 'CustomFields.Count' times, e.g. 2 Products and each has 20 Custom Fields, then 40 Products and each has 20 valid custom fields.

Do I missed something in mapping ?

You are returning a Cartesian product and therefore your product is being returned x times for every custom field.

To get around this problem you will need to use something like:-

var query = Session
    .GetNamedQuery("GetProducts")
    .SetResultTransformer(new DistinctRootEntityResultTransformer());
    return query.List<Product>();

Please note that you will send all the data down the wire and NHibernate will perform the distinct transformer client (meaning web server or desktop app) side.

I am not 100% sure if the return join will be populated as I have never done things this way, you will need to test this.

edit I think you fetching strategy is not quite right. Do you really need a <sql-query...> Could you use another strategy e.g. HQL?