Localization (L10n) in the DB

[markdav-is]

@Rolyac and I were talking about English/Spanish requirement for LHB and how it's a combination of strings in the UI and strings in the database. He shared a link to the UI guidance here https://learn.microsoft.com/en-us/aspnet/core/fundamentals/localization?view=aspnetcore-7.0 and I recognized that's how Oqtane implements things in the UI using IStringLocalizer. Now I'm starting to think about the best way to deal with strings in the data using a similar method. RESX files are fine for the app UI but the format is not human readable so is a poor choice for storing in the database.

Here is a JSON localization implementation that might be useful https://github.com/AlexTeixeira/Askmethat-Aspnet-JsonLocalizer My thought being that each row in the database would have English as the default and then a i18n column with JSON for each column where locatization is available. Here's an example:

CREATE TABLE [dbo].[LocalizedTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Description] [nvarchar](4000) NULL,
	[L10n] [nvarchar](4000) NULL,

the L10n column holds JSON that looks like this:

[
  {
    "Name": {
    "Value" : {
      "fr-FR": "Bonjour",
      "en-US": "Hello"
    }
  },
  {
    "Description": {
    "Value" : {
      "fr-FR": "Bonjour là humain",
      "en-US": "Hello there human"
    }
  }

]

Borrowing some code from the AskMeThat repo (link above) we should be able to have UI that looks similar to the following:

<Pager Items="@_LHBs">
            <Header>
                <th style="width: 1px;">&nbsp;</th>
                <th style="width: 1px;">&nbsp;</th>
                <th>@Localizer["Name"]</th>
            </Header>
            <Row>
                <td><ActionLink Action="Edit" Parameters="@($"id=" + context.LHBId.ToString())" ResourceKey="Edit"  /></td>
       
                <td>@context.Localizer["Name"]</td> <!-- the goods! -->
            </Row>
        </Pager> 

To accomplish the context. Localizer[] magic we'll need to extend the EF models with an IStringLocalizer implementation that digs into the Localized column JSON to fetch the proper value. Again, the AskMeThat repo (linked above) should have examples.

The added value to this approach is that it would be trivial to create SQL localized views using this method. A French view for example would look like this:

CREATE VIEW [dbo].[LocalizedTable_fr_FR]
AS
SELECT
 Id
,JSON_VALUE([L10n], '$.Name.Value.fr-FR') as Name
,JSON_VALUE([L10n], '$.Description.Value.fr-FR') as Description
FROM LocalizedTable;