Localization (SF only solution) Transparently localize content and fall back to standard value when no localization in selected UI language is available

Basic Building Blocks Series: Within the basic building blocks series over time I will add all the little things which someone who just starts with Serenity Framework would have to spend a lot of time figuring it out him/herself.

What you get with this article: As you may already know, Serenity Framework has excellent builtin multi-language capabilities. This not only includes UI translations but also content localization.

In this article, we are looking at content localization (translations for data content - e.g. Within a product table, there is a field called productName, which obviously only can contain the name of the product in one specific language). With content localization, there is builtin capability to register a different ProductName for each language, which is defined within your SF project's languages table.

While the registering part (e.g. a GUI, with which you can register different product names for different languages) is builtin functionality of SF, the dynamic retrieval and substitution of the original value of the ProductName field within the main table with a localized version for the currently active language is not included within SF.

So with the gracious help of @minhhungit (thank you!) , you find here now a solution which dynamically substitutes a localized value if there exists one for a given value in the main table. If there is no localized value, it takes the standard value from the field in the main table.

Please note: While this should work with any database system (e.g. MSSQL, MySQL, PostGreSQL, SQLite, etc) because it is purely based on C# logic and no SQL engine gimmicks, I have tested it only on SQLite.

---

Very important! The SF.only-solution here does not do the localization for every request which is coming from any part of your SF solution, querying the original field within the SQL table [xyz]. This currently only works for the one Module, where you have modified the xyzRow.cs and the xyzRepository.cs according to the steps below.

---

Here we go...

In xyzRow.cs (your main row class) on the ID field, add the following:

[ForeignKey("[<name of localization table>]", "<foreign key field which links to this ID field here>"), LeftJoin("<foreign table alias>")]

e.g:
[ForeignKey("[BooksLang]", "BookId"), LeftJoin("jBooksLang")]

Also add a foreign field which references the field within the localization table which holds the localization text:

        [DisplayName("Localization"), Expression("<Localization table alias>.<Real name of field which holds the localization text>"), LookupInclude]
        public String xxx { get { return xxx[this]; } set { xxx[this] = value; } }
        public partial class RowFields { public StringField xxx; }

xxx = <virtual FieldName under which the localization field should be accessed within SF>

e.g.

        [DisplayName("Localization"), Expression("jBooksLang.[BookName]"), LookupInclude]
        public String BookName_Localization { get { return Fields.BookName_Localization[this]; } set { Fields.BookName_Localization[this] = value; } }
        public partial class RowFields { public StringField BookName_Localization; }

Under /Modules/Common/Helpers/ create a new cs file named Extensions.cs with the following content:

using Serenity.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BLook.Modules.Common.Helpers
{
    public static class SqlQueryExtensions
    {
        public static SqlQuery ApplyCurrentLanguage(this SqlQuery query, Alias joinAlias, Criteria joinCriteria)
        {   
            query.LeftJoin(joinAlias, joinCriteria);
            return query;
        }
    }
}

Under /Modules/Common/Helpers/ create a new cs file named LanguageHelpers.cs with the following content:

using Serenity.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Web;
using myLanguageRow = <your namespace>.Administration.Entities.LanguageRow;

namespace <your namespace>.Modules.Common.Helpers
{
    public static class LanguageHelpers
    {
        public static int getCurrentUIlanguageID()
        {
            var UI_languageShortName = CultureInfo.CurrentUICulture.Name;

            var _myLanguageRow = new myLanguageRow();
            var Connection = SqlConnections.NewFor<myLanguageRow>();
            
            var lngQuery = new SqlQuery();
            lngQuery.From(_myLanguageRow);
            lngQuery.Select(myLanguageRow.Fields.Id);
            lngQuery.Where(myLanguageRow.Fields.LanguageId == UI_languageShortName);
            var Result = Connection.Query(lngQuery).First();

            int UI_languageID = 0;
            var data = (IDictionary<string, object>)Result;
            int.TryParse(data["Id"].ToString(), out UI_languageID);

            Connection.Close();

            return UI_languageID;
        }

    }
}

In your main xyzRepository.cs, add a using which points to your common/helpers namespace:

using <your namespace>.Modules.Common.Helpers;

Also in your main xyzRepository.cs, add the following 4 private properties (change values to your need - the property names explain for what they are used):

        private static string oriFieldNameWithinLocalizedTable = "BookName";
        private static string substituteFieldNameWithinMainTable = "BookName_Localization";
        private static string localizationTable = BooksLangRow.TableName;
        private static string localizationTableAlias = "jBooksLang";

Also In your main xyzRepository.cs, modify the two classes MyRetrieveHandler and MyListHandler as follows:

        private class MyRetrieveHandler : RetrieveRequestHandler<MyRow> {
            protected override SqlQuery CreateQuery()
            {
                var UI_languageID = LanguageHelpers.getCurrentUIlanguageID();      // *** ID of currently selected UI language of the user (selected within language dropdown in the upper right corner of the app) ***
                var joinAlias = new Alias(localizationTable, localizationTableAlias);
                var joinCriteria = new Criteria("jBooksLang.BookId = T0.BookId and jBooksLang.languageId = " + languageID);

                return base.CreateQuery().ApplyCurrentLanguage(joinAlias, joinCriteria);
            }

        }
        private class MyListHandler : ListRequestHandler<MyRow> {
            protected override SqlQuery CreateQuery()
            {
                var UI_languageID = LanguageHelpers.getCurrentUIlanguageID(); // *** ID of currently selected UI language of the user (selected within language dropdown in the upper right corner of the app) ***
                var joinAlias = new Alias(localizationTable, localizationTableAlias);
                var joinCriteria = new Criteria("jBooksLang.BookId = T0.BookId and jBooksLang.languageId = " + languageID);

                return base.CreateQuery().ApplyCurrentLanguage(joinAlias, joinCriteria);
            }

            protected override void PrepareQuery(SqlQuery query)
            {
                base.PrepareQuery(query);

                // If Localization field is not used within the the calling list requestor, it does not exist - so add it here always to make sure it exists ***
                query.Select( $"{localizationTableAlias}.{oriFieldNameWithinLocalizedTable} as {substituteFieldNameWithinMainTable}");

            }

            protected override MyRow ProcessEntity(MyRow row)
            {
                // *** Set the value of the localization virtual field dynamically according if there is a localizated value for the current language of the user or not ***
                row.BookName_Localization = row.BookName_Localization.IsEmptyOrNull() ? row.BookName : row.BookName_Localization;

                // *** If you don't want the value of the original field within the main table to be changed when returning the result (only temporarily changed in-memory for this request, not in table), comment this line out
                row.BookName = row.BookName_Localization.IsEmptyOrNull() ? row.BookName : row.BookName_Localization;
                
                return row;
            }
        }

That's it! This results in automatic, dynamic localization of content.

Here an example from a real-world private project: While the original values in the [books] table are in english, because there are localizations for these book names for the french language, they are dynamically changed to french.

Hint: There is no need to change anything in xyzColumns.cs. There is still only the main field listed. The BookName_Localization field is not even listed:

    [ColumnsScript("Administration.Books")]
    [BasedOnRow(typeof(Entities.BooksRow), CheckNames = true)]
    public class BooksColumns
    {
        [EditLink, DisplayName("Db.Shared.RecordId"), AlignRight]
        public Int32 BookId { get; set; }
        [EditLink]
        public String BookName { get; set; } // *** Original field of main table ***
        public String Otnt { get; set; }
    }

Hope this helps somebody.