Querying localized values in NHibernate

Posted by Siim on March 30th, 2010

In my previous post I talked about how to create model for localized values. Now it’s time to show how I’m querying against that model.

I display language selection to the user. So all localizable data is preferably shown in the language the user chose. When displaying persons list, for example, person name is shown in the language the user chose or the first available localized value if the localization for chosen language was not found. I’ll use HQL queries for that, which return a projection from entity to DTO to contain only data I need. HQL allows better (and easier) optimization than using criteria and it’s easier to write.

So HQL that returns persons list where first name and last name are localized, looks like this:

select distinct new PersonDataContract(person.Id, firstName.Value, lastName.Value, person.ContactCard.Phone, person.ContactCard.Mobile,
	person.ContactCard.Email.Address, l.id, firstName.Language.id, lastName.Language.id)
from Language l, Person as person
	join person.FirstName tfname
	left outer join tfname.Localizations firstName
	join person.LastName tlname
	left outer join tlname.Localizations lastName
where l.id = :langId
	and (firstName.Language.id = l.id or (index(firstName) = 0 and l.id not in (select loc.Language.id from Localization loc where loc.Translation = tfname)))
	and (lastName.Language.id = l.id or (index(lastName) = 0 and l.id not in (select loc.Language.id from Localization loc where loc.Translation = tlname)))
order by lastName.Value, firstName.Value 

I also fetch the language id for localized values because I want to show to the user if returned value was in language he requested.

And matching SQL from NHProf:


When an object has many localizable properties then such a query could become too complex, too many joins. In that case it may be better to use different strategy for fetching objects. We can use NHibernate’s multi queries or Futures. I’ll show you similar example, but in a bit different context.

In some situations it would be better to show to user all translations for selected language in a single list. For example when there is a language specialist to translate all values from one language to another. In that case we need to load all translations (for specified context, eg. person names) with localizations. In this case I need also to use paging to limit the result set.

I use multi queries approach here. First I fetch translations for the selected context and languages list. And for each language I load localizations for translations in separate queries. So I make total of two roundtrips to the database.

 public IEnumerable<Translation> GetTranslationsBy(Type translationType, int startItemIndex, int numberOfItems)
	// Select IDs first
	var idQuery =
		Session.CreateQuery("select t.id from Translation t where t.class = " + translationType.FullName)
	var temporaryList = Session.CreateMultiQuery()
		.Add("from Language").List();

	const string queryFormat = "from Translation t left join fetch t.Localizations l where t.class = {0} and l.Language.id = :{1} and t.id in (:translationIds)";

	var multiQuery = Session.CreateMultiQuery();
	foreach (Language language in (ArrayList)temporaryList[1])
		var langParamName = string.Format("lang{0}Id", language.Id);
		var query = Session.CreateQuery(string.Format(queryFormat, translationType, langParamName))
			.SetInt32(langParamName, language.Id);
	multiQuery.SetParameterList("translationIds", (ArrayList) temporaryList[0]);

	IEnumerable<Translation> result = new List<Translation>();
	var list = multiQuery.List();
	if (list.Count > 0)
		result = (IEnumerable<Translation>)((ArrayList)list[0]).ToArray(translationType);
	return result;

repository.GetTranslationsBy(typeof (PartnerNameTranslation), 0, 10); 

With results:




The third query is similar to the first two, only for different language.

I’m using only the first result set from the second batch. Other queries are only for fetching all available localizations for translations and actual results they return, will be discarded. That way I pre-load all the values and therefore there is no need for lazy loading them which in turn would result in executing multiple queries to the database.

As I mentioned, the last approach may also be used when loading DTO’s, only in that case mapping to DTO should be done by the user (or use AutoMapper in some extent).

Mapping translations in NHibernate

Posted by Siim on February 24th, 2010

Some time ago I wrote about translations. Now was the time to actually implement that feature. Because I don’t have any default language which is always present on an object, I had somehow create it virtually, so in case of translation is not found in current language, the default one (for a given object) will be used. I decided that the first language for an object (that is, the language in which the object was created), is used as a default one.

So my data model looks like this. Firstname_id and lastname_id both refer to the translation table as foreign key relations.


All the objects were mapped as entities and I was trying to map relation between Translation and it’s Localizations as an ordered list. But I soon discovered that ordered collections don’t support bi-directional associations natively. I had a bi-directional relation between Translation and Localizations. So I had to do index handling myself. For that I created a Index property to Localization which is mapped to order_index column. Property looks like this:

public virtual int Index
        return Translation.IndexOf(this);
    private set
    { }

And Translation object looks like this:

public abstract class Translation
    private IList<Localization> _localizations;
    public Translation()
        _localizations = new List<Localization>();
    public abstract string Context { get; }
    public virtual IEnumerable<Localization> Localizations
        get { return _localizations; }
    public virtual int IndexOf(Localization localization)
        return _localizations.IndexOf(localization);
    public virtual string DefaultValue
            var loc = _localizations.FirstOrDefault();
            return loc != null ? loc.Value : null;
    public virtual string GetCurrentValue(Language language)
        return this[language] ?? DefaultValue;
    public virtual string this[Language language]
            var localization = _localizations.SingleOrDefault(x => x.Language.Locale == language.Locale);
            return localization == null ? null : localization.Value;
            var localization = _localizations.SingleOrDefault(x => x.Language.Equals(language));
            if (localization != null && !string.IsNullOrEmpty(value))
                localization.Value = value;
            else if (localization != null)
            else if (!string.IsNullOrEmpty(value))
                AddLocalization(language, value);
    private void RemoveLocalization(Localization localization)
    private void AddLocalization(Language language, string value)
        var localization = new Localization(language, value) { Translation = this };

As you can see, I added indexer property to Translation to manipulate with localizations conveniently. Although I’m not sure if using an object as an indexer has any drawbacks later on… Thoughts welcome.

You may have also noticed abstract Context property on Translation. It isn’t strictly required, but I found it useful in my implementation. By using this I can conveniently ask all the translations for person name or for product name, for example. This is useful when users need to translate all product names in a batch, so I can display them on a single form.

And here are example NHibernate mappings:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<!-- Translation -->
<class abstract="true" name="Translation" table="[translation]">
    <id name="Id" access="property" column="translation_id">
        <generator class="identity" />
    <discriminator column="context"/>
    <list name="Localizations" cascade="all-delete-orphan" fetch="join" access="field.camelcase-underscore" inverse="true">
        <key column="translation_id" />
        <index column="order_index" />
        <one-to-many class="Localization" />
    <subclass discriminator-value="PersonFirstName" name="PersonFirstNameTranslation">
    <subclass discriminator-value="ProductName" name="ProductNameTranslation">
    <!-- etc -->
<!-- Localization -->
<class name="Localization" table="localization">
    <id name="Id" access="property" column="localization_id">
        <generator class="identity" />
    <many-to-one name="Translation" class="Translation" column="translation_id" not-null="true" />
    <many-to-one name="Language" class="Language" column="language_id" not-null="true" />
    <property name="Value" column="value"/>
    <property name="Index" column="order_index" type="int" update="true" insert="true" />
<!-- Person -->
<joined-subclass name="Person" extends="Party" table="person">
    <key column="party_id" />
    <property name="PersonalCode" column="personal_code" />
    <property name="DateOfBirth" column="birth_date" access="field.camelcase-underscore" />
    <many-to-one name="FirstName" column="firstname_id" class="Translation" fetch="join"
        access="property" cascade="all-delete-orphan" not-null="true"/>
    <many-to-one name="LastName" column="lastname_id" class="Translation" fetch="join"
        access="property" cascade="all-delete-orphan" not-null="true"/>

And storing a new object with translations is simple as this:

var person = new Person();
person.PersonalCode = "12345670";
// languages is IList<Language>
foreach (var language in languages)
    person.FirstName[language] = "First name: " + language.Name;
    person.LastName[language] = "Last name: " + language.Name;

This solution has it’s own drawbacks also, but I found that it’s best for my needs. Currently translations are not reusable. By that I mean when one term is used in multiple places, it has to be maintained separately on each instance. Of course, I can make user to choose from existing translations, but it seems to me that this makes things overly complicated. But again, it depends on the exact context :)

Copyright © 2007 Siim Viikman's blog.