Localization with Entity Framework

• Download sample solution - 33 KB

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())
  {
    // Get the entity info
    var locEntity = ctx.LocalizableEntity.FirstOrDefault(le => le.EntityName.Equals(entityName));
    if (null != locEntity)
    {
        // Get the entity id
        int entityId = (int)entity.GetType().
                           GetProperty(locEntity.PrimaryKeyFieldName).GetValue(entity, null);
        // Get the  fields that need to be translated for this entity
        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)
        {
            // Overwrite each field with the translated value
            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())
{
  // Get the first status in Spanish
  var status = Localization.Translate(ctx.Status.First(), "es-MX");
  // Get all the statuses in the current user culture
  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