Localizable Entities With Nhibernate Part 3

Now that the Entity object works as expected the only issue remaining is the integration with NHibernate. First Step is creating mapping file to store the object in the database. Database structure is very simple, just a table for the Entity class plus another table for localized resources.

Against this database schema we can map the Entity class with this mapping file.

<?xml version=1.0 encoding=utf-8 ?>
<hibernate-mapping xmlns=urn:nhibernate-mapping-2.2 
   <class name=Entity table=Entity>
      <id name=Id unsaved-value=0>
         <generator class=native />
      <property name=SomeCode column=somecode type=Int32 />
      <map name=Resources table=EntityResources lazy=false fetch=join>
         <key column=entityid />
         <composite-index class=Registry.LangCode, Registry>
            <key-property name=Code column=langcode access=property />
         <composite-element class=EntityResources>
            <property name=Name column=name />
            <property name=Description column=description />

Dictionary properties are mapped with the element <map> on the database table EntityResources, then we needs to specify the key tag that contains the foreign key, in our model the column entityid. Keys of the dictionary are instances of LangCode class, so we need to specify a <composite-index> tag with class attribute, inside the composite-index nhibernate we need to specify which columns of the table are used to build the key part of the dictionary with the element <key-property>. In this example the LangCode is composed only by language code, so it maps to the single column langcode. For the element we needs also to specify a <composite-element>, but now this class maps to the two column Name and Description of the table. This completes the mapping.

To conclude this tutorial it is interesting to show some HQL queries that can be used to manage this particular entity. A main disadvantage of this mapping scheme is that when we load a list of Entity objects used only to show data to the user, each instance of the Entity class will contains all resources for each language supported, this waste a lot of memory and also retrieve too much data from database. If we are sure that all the objects have resources for current language, and the current language will not change for the lifetime of the object, we can do a HQL query to retrieve only the resource for current language, this avoid to load localization data for language that will be not necessary.

from Entity as E left join fetch E.Resources as res where INDEX(res) = ‘it’

This query uses the special syntax INDEX() that permits to specify criteria for the key of a dictionary. The purpose of this query is to retrieve all entities but only with the ‘it’ resource. The left join fetch is used to use an eager fetch strategy, if a simple join would be used, NHibernate would have done a first select to retrieve the entity list, and subsequently a single select for each entity to retrieve the resource corresponding to ‘it’ language.

As usual you can search inside the description or name property, but you must pay attention. A query like this

select distinct from Entity as E join E.Resources as res where res.Description like ‘des%’

actually search for entities that have one of its localization resource with a value like des%. Suppose that an entity has two resources: the ‘it’ has Description property equal to “Descrizione” and ‘de’ has Description property equal to “Schilderung”. If current language is de and you run the previous query, the previous entity is returned, because it has the ‘it’ resource that satisfy the match, but when you show the Description property to screen it shows “Shilderung” because ‘de’ is the current language. This kind of experience can be frustrating for the user that really does not understand why this object had matched the filter. To avoid this problem the INDEX(res) = :langcode filter should be included for each query that does a filtering on localizable properties of the entity.

For those interested in performances, the previous HQL query produce this SQL (Captured with profiler)

select distinct entity0_.Id as Id0_, entity0_.somecode as somecode0_ 

from Entity entity0_ inner join EntityResources resources1_ on entity0_.Id=resources1_.entityid 

where (resources1_.description like ‘des%’)

As you can see the query is efficient because it join directly the two tables and does not suffer for the n-select problem. Please do not forget to use select distinct on HQL query because if it’s not specified and an entity has more than one resource that matches the filter, the query returns duplicate results.

I hope this little tutorial can help anyone that needs to use localizable entities with NHibernate.


Localizable entities with Nhibernate Part 1

Localizable entities with Nhibernate Part 2

Example Code.


Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

6 thoughts on “Localizable Entities With Nhibernate Part 3”

  1. This tutorial is great. Much better than the documentation and available books for the collection mapping. Your examples saved me a lot of time.

    If I use a , how can I query?

    If your mapping was

    How would you then query for Code of ‘it’ regardless of country?

  2. Hi! excellent tutorial!…

    i’ve just have one question… i haven’t been able to create a equivalent query to the hql ‘from Entity as E left join fetch E.Resources as res where INDEX(res) = ‘it’’ using Criterias o DetachedCriterias… is it possible to use criterias with this kind of localizable data?

    thanks for your input!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.