Localisation/I18n of database data in LINQ to SQL

Question

I have status tables in my database, and "localised" tables that contain language-specific versions of those statuses. The point of the main status table is to define the status ID values, and other meta-data about the status. The "localised" table is to display the text representation in a UI, according to the users' preferred language. Here is an example schema:

create table [Language]
(
    ID smallint primary key,
    ISOName varchar(12)
)

create table EmployeeStatus
(
    ID smallint primary key,
    Code varchar(50)
)

create table EmployeeStatusLocalised
(
    EmployeeStatusID smallint,
    LanguageID smallint,
    Description varchar(50),
    constraint PK_EmployeeStatusLocalised primary key
        (EmployeeStatusID, LanguageID),
    constraint FK_EmployeeStatusLocalised_EmployeeStatus foreign key 
        (EmployeeStatusID) references EmployeeStatus (ID),
    constraint FK_EmployeeStatusLocalised_Language foreign key
        (LanguageID) references [Language] (ID)
)

create table Employee
(
    ID int identity(1,1) primary key,
    EmployeeName varchar(50) not null,
    EmployeeStatusID smallint not null,
    constraint FK_Employee_EmployeeStatus foreign key
        (EmployeeStatusID) references EmployeeStatus (ID)
)

This is how I'd typically access that data:

select e.EmployeeName, esl.Description as EmployeeStatus
from Employee e
inner join EmployeeStatusLocalised esl on
    e.EmployeeStatusID = esl.EmployeeStatusID and esl.LanguageID = 1

I'm not really happy that my LINQ to SQL is doing things in the most efficient way, though. Here's an example:

using (var context = new MyDbDataContext())
{
    var item = (from record in context.Employees
                select record).Take(1).SingleOrDefault();

    Console.WriteLine("{0}: {1}", item.EmployeeName,
        item.EmployeeStatus.EmployeeStatusLocaliseds.
            Where(esl => esl.LanguageID == 1).Single().Description);
}

Answer 1

One option could be to maintain a cache of the localised data, using something like Caching Application Block or ASP.NET caching, then just refer to that cache in the view.

This would limit the amount of database calls, because LINQ might not need to load the status records in order to get the localised description.

Answer 2

You can use LoadOptions in the DataContext, so it loads the data in the initial query. Something around the lines:

var options = new DataLoadOptions();
options.AssociateWith<Employee>(e=>
    e.EmployeeStatus.EmployeeStatusLocaliseds
    .Where(esl => esl.LanguageID == 1)
    );
options.LoadWith<Employee>(e=>e.EmployeeStatus.EmployeeStatusLocaliseds);

    using (var context = new MyDbDataContext())
    {
        context.LoadOptions = options;
        var item = (from record in context.Employees
                    select record).Take(1).SingleOrDefault();

        Console.WriteLine("{0}: {1}", item.EmployeeName,
            item.EmployeeStatus.EmployeeStatusLocaliseds
            .Single().Description
        );
    }

On the other hand, statuses are probably pretty much static data, so caching them would be pretty effective. If you are sticking to the generated entities, you can define a property on the partial class of Employee that uses the cache.

Answer 3

Aside: in this scenario, I would consider treating the status code (rather than id) as a primary key, and have the Code denormalized in the Employee; this retains the foreign key, but reduces the number of joins and navigations needed. It also allows you potentially to map the Code to an enum in your .NET code.

I would probably use a lazy (on demand) cache of the i18n text values; this:

• minimises the query complexity
• minimises data-throughput/IO
• minimises object identity/change tracking overhead
• minimises the number of "materializations" that are happening constantly
• allows you to program just against an enum in the object model, but display the i18n text
• abstracts the i18n implementation (so you could switch to resx or similar if you needed to, or an automated translation service)
• allows you to obtain i18n values without being tied to an existing data query - i.e. to populate a drop-down box for a search screen (which has nothing to do with individual employees, so the example query doesn't help)

I'm guessing the i18n data is slow changing, so the cache approach is ideal.

I would load all the related strings a language at a time - so the first time a status is needed in Welsh (for example), I'd load all the status strings for Welsh, and cache against the standard code (cy) for the language.

To make the view code simpler, consider using an extension method on the employee (at the UI level):

public static class EmployeeExtensions {
    public static string GetStatusText(this Employee emp) {
         /* do your funky thing, presumably using the HttpContext or
         some other thread-static value to resolve the current culture */
    }
}

then in your view you can use:

<%=emp.GetStatusText()%>

etc. Unfortunately there are no extension properties, but with a method you also have the option of passing the language-code into the method (after adding a parameter):

<%=emp.GetStatusText(lang)%>

Answer 4

I found quite a good solution for our scenario.

The 'design' goes like:

1. Create a table called 'GlobalizedString' with an PK ID.
2. Create a table called 'LocalizedString' with a reference to above and the following fields:
   • CultureId (reference to some culture lookup table)
   • Content (a string that will be in the language above)

On GlobalizedString, add a property called 'Content' (note this is not queryable via LINQ2SQL, but works in LINQ2Objects), that looks like:

public string Content
{
  get { return LocalizedStrings.Single( 
           x => x.Culture.CultureCode == 'my current CultureInfo's code'); }
  set { /* exercise for reader */ }
}

So, instead of having nvarchar columns, you point to the GlobalizedString table instead.

Now instead you normal 'logic' (eg binding), you simply refer to the Content property of the GlobalizedString to get the Content for the current language :)

As said previously, this Content property does not work on Linq2SQL, and I am still looking for an easy way to make that possible (suggestions welcome).

Otherwise, the system is serving us well :)

Answer 5

Personally, I'd probably leave the EmployeeStatus codes in the DB and move all localization logic into the client. If this is a web app (ASP.NET or ASP.NET MVC) then you'd use the the EmployeeStatus code as a key into a resource file, and then use UICulture="Auto" and Culture="Auto" to tell ASP.NET to pick up the right resources based upon the "Accept-Language" HTTP Header.

You'd provide default (culture insensitive) resources embedded in your app, and allow satalite assemblies to override the defaults where they needed.

The problem, for me, with adding localization into the DB is that you firstly end up with much more complicated queries, you have to keep pumping the locale into each of these queries, and you can't cache the outputs of the queries so widely. Secondly, you have a mixure of tables that hold entities and tables that hold localization. Finally, a DBA is required to do the localization.

Ideally you want someone who understands how to translate text to do the localization, and for them to use some tool that they're comfortable with. There are plenty of .resx tools out there, and apps that allow language experts to "do their thing".

If you're stuck with DB tables for localization because "that's how it is" then perhaps you should query the lookups seperately to the real data, and join the two at the UI. This would at least give you an "upgrade path" to .RESX in the future.

You should check out Guy Smith-Ferrier's book on i18n if you're interested in this area:

http://www.amazon.co.uk/NET-Internationalization-Developers-Guide-Building/dp/0321341384/ref=sr_1_1?ie=UTF8&s=books&qid=1239106912&sr=8-1