Click here to Skip to main content
Click here to Skip to main content

Tagged as

Localization with Entity Framework

, 19 Apr 2012
Rate this:
Please Sign up or sign in to vote.
How to replace textual properties of EF Objects with translations

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’), (2, ‘Rejected’).

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 20 Apr 2012
Article Copyright 2012 by Federico Colombo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid