Introduction
I've run into the problem of translation to different languages, of SQL database data for applications that use the .NET Entity Framework.
Here, I propose a simple solution that involves the least amount of changes to an existing database, so that it supports translations of their textual content into different languages, so the translation could be done overriding or replacing the content of the Entity Framework object properties.
For example: A Status
table where different statuses are stored, with columns Id
and Description
with the values: (1, ‘Approved’),<span> (2, ‘Rejected’)</span>
.
We need to show a different status description depending on the user culture: if the user is from Mexico, we want to show Aprobado
instead of Approved
, for German users Genehmigt
, and for users in United States the current database description Approved
is OK.
Background
Although localization is a broader term, it is used here as a synonym for translation, to refer to the need to translate a textual resource into different languages.
Design
The Language
table is the catalog for the available languages in the application.
Id
: The language id Code
: The language code (as defined by the BCP 47 i.e. en-US) Name
: The language name
The LocalizableEntity
table will have the references to the tables that are likely to have some translatable columns.
EntityName
: The table name (i.e. Status
) PrimaryKeyFieldName
: Name of the primary key column (i.e. Id
)
The LocalizableEntityTranslation
table will store the actual translations. Each row in this table will reference a column for a particular table row, translated to a particular language.
LocalizableEntityId
: The entity Id (i.e.: the Status table id) PrimaryKeyValue
: The record identification (i.e. 1
meaning the Status with Id=1
) FieldName
: The column name to translate within the entity (i.e. Description
) LanguageId
: The language Id (i.e. 2
meaning Spanish) Text
: The translated text (i.e. Aprobado
)
Using the Code
With this design, we can use reflection to implement a generic method to translate any entity.
This method could take an entity (for example an Entity Framework POCO object) and a language, and return the entity with its properties translated to the given language.
public static T Translate<T>(T entity, string languageCode)
{
string entityName = entity.GetType().BaseType.Name;
using (var ctx = new Entities())
{
var locEntity = ctx.LocalizableEntity.FirstOrDefault(le => le.EntityName.Equals(entityName));
if (null != locEntity)
{
int entityId = (int)entity.GetType().
GetProperty(locEntity.PrimaryKeyFieldName).GetValue(entity, null);
var ler = locEntity.LocalizableEntityTranslation
.Where(er => er.LocalizableEntity.EntityName.Equals(entityName)
&& er.PrimaryKeyValue.Equals(entityId)
&& er.Language.Code.Equals(languageCode));
foreach (var t in ler)
{
entity.GetType().GetProperty(t.FieldName).SetValue(entity, t.Text, null);
}
}
}
return entity;
}
The class Entities
is the EF object context.
Use example:
using (var ctx = new Entities())
{
var status = Localization.Translate(ctx.Status.First(), "es-MX");
var statuses = ctx.CreateObjectSet<Status>().AsEnumerable()
.Select(s => Localization.Translate
(s, Thread.CurrentThread.CurrentCulture.IetfLanguageTag));
}
Considerations
- The translatable tables must have a single numeric identification key.
- The Entity Framework classes must have the same name as the table.
- It is recommendable to store the translations in a cache to avoid an excessive database querying, since this approach needs to query the translation for each instance of each translatable entity.
History
- 2012-04-19: Document creation