Localization approach

[Guillermo]

Hi.  We have started developing an aspnet 4.51. mvc application with Telerik DataAccess as ORM.   With are doing some research and want to know what DataAccess recomends for localization. 

I have a table (named MyTable):
IdMyTable           int   PK identity
Name   nvarchar(150)
Code nvarchar(20)
Status   tinyint

We need to localize Name and Code columns.   We thought having a localization table for every table in the database.  Look at table named MyTableLoc:
IdMyTableLoc      int PK identity
IdMyTable  int FK from MyTable
Name   nvarchar(150)
Code  nvarchar(20)
Language nvarchar(2)   <- We will store two letter iso name for the language.


In the "admin" area of our application, admin user will store in MyTable the values for Name and Code for the company's main language (defined somewhere).  In MyTableLoc, admin user will add translations for each record in MyTable.

For this admin area we have no problem at all for showing/editing data with telerik DataAccess. 

The problem is when standard users surf the application in different languages.   A user that requests a page that shows MyTable records, the Name and Code columns should match users language.  User may also do searches for Name or Code columns, so the searches must be done against MyTableLoc table for those 2 fields, but against MyTable for other fields.

We have like 50 localizable tables.  We would like to use linq as follow:


var myRecords= context.MyTable.Where(c=>c.NameLoc==myVariable);

Where somewhere we have defined NameLoc as the value for Name field in the current thread language, so the query will know which field (myTable.Name or myTableLoc.Name) to use.

Currently we are showing records (we have not yet try to do searching) by using

var lng=System.Threading.Thread.CurrentThread.CurrentCulture.TwoLetterISOLanguageName;
   var myRecords= context.MyTable
                .Select(c => new
                {

                    Name = c.MyTableLoc.Where(f => f.Language == lng).Select(g => g.Name).FirstOrDefault() ?? c.Name,
                    Status= c.Status,

                })
                 ;


Do you recommend another  approach?  


Hope I've been clear enough.

Regards,

Guillermo

[Boris Georgiev]

Hello Guillermo,

It seems that your approach is good enough. The only thing that bothers me is the LINQ that you are using to take the localized column values. Maybe you could try to use sql views or stored procedure to execute this faster. 

In case you are using fluent mapping, you could try to use dictionaries to store the localization. It will be faster and easier to search for localized values. Here you can find more information about mapping Dictionaries.

I hope that helps.

Regards,
Boris Georgiev
Telerik