how to use SQL localization in ASP.NET Core using an SQL database. -iaspnetcore.com

This article shows how to use SQL localization in ASP.NET Core using an SQL database. The SQL localization in the demo uses Entity Framework Core to access a SQLite database. This can be configured to use any EF core provider.

Code: https://github.com/damienbod/AspNetCoreLocalization

Library:https://github.com/damienbod/AspNetCoreLocalization/tree/master/src/Localization.SqlLocalizer

NuGet package: https://www.nuget.org/packages/Localization.SqlLocalizer/

Documentation: http://localizationsqllocalizer.readthedocs.io/en/latest/

Posts in this series

Using the SQL Localization

To use the SQL localization, the library needs to be added to the dependencies in the project.json file. The library is called Localization.SqlLocalizer.

{
    "dependencies": {
        "Localization.SqlLocalizer": "2.0.0",

This can then be added in the Startup class ConfigureServices method. The AddSqlLocalization method requires that the LocalizationModelContext class is configured in an Entity Framework service extension. In this example, SQLite is used as the provider for the LocalizationModelContext context class. The rest of the localization can be configured as required. The example supports en-US, de-CH, fr-CH, it-CH.

public void ConfigureServices(IServiceCollection services)
{
    // services.AddLocalization(options => options.ResourcesPath = "Resources");
 
    // init database for localization
    var sqlConnectionString = Configuration["DbStringLocalizer:ConnectionString"];
 
    services.AddDbContext<LocalizationModelContext>(options =>
        options.UseSqlite(
            sqlConnectionString, 
            b => b.MigrationsAssembly("AspNet5Localization")
        )
    );
 
    // Requires that LocalizationModelContext is defined
    // services.AddSqlLocalization(options =>  options.UseTypeFullNames = true);
    services.AddSqlLocalization();
 
    services.AddMvc()
        .AddViewLocalization()
        .AddDataAnnotationsLocalization();
 
    services.AddScoped<LanguageActionFilter>();
 
    services.Configure<RequestLocalizationOptions>(
        options =>
            {
                var supportedCultures = new List<CultureInfo>
                {
                    new CultureInfo("en-US"),
                    new CultureInfo("de-CH"),
                    new CultureInfo("fr-CH"),
                    new CultureInfo("it-CH")
                };
 
                options.DefaultRequestCulture = new RequestCulture(culture: "en-US", uiCulture: "en-US");
                options.SupportedCultures = supportedCultures;
                options.SupportedUICultures = supportedCultures;
            });
}

The localization also needs to be added in the Configure method.

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
    loggerFactory.AddConsole();
    loggerFactory.AddDebug();
 
    var locOptions = app.ApplicationServices.GetService<IOptions<RequestLocalizationOptions>>();
    app.UseRequestLocalization(locOptions.Value);
 
    app.UseStaticFiles();
 
    app.UseMvc();
}

The database now needs to be created. If using SQLite, a creation sql script is provided in the SqliteCreateLocalizationRecord.sql file. If using a different database, this needs to be created. I have provided no migration scripts. The SQLite script can be executed in Firefox using the SQLite Manager.

CREATE TABLE "LocalizationRecords" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_DataEventRecord" PRIMARY KEY AUTOINCREMENT,
    "Key" TEXT,
    "ResourceKey" TEXT,
    "Text" TEXT,
    "LocalizationCulture" TEXT,
    "UpdatedTimestamp" TEXT NOT NULL
)

The database should now exist. I have added some basic demo rows.

aspnetcore_loc_sql_01

The default configuration for the SQL Localization uses the name of the resource, then the resource key (which could be the default language text if you follow the recommendations from Microsoft), and then the culture. A separate field in the database exists for each of these properties. If the localization is not found, the searched key is returned. Here’s an example of a localization which was not found and returned to the UI.

ASP.NET Core MVC controller:

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Localization;
 
namespace AspNet5Localization.Controllers
{
 
    [ServiceFilter(typeof(LanguageActionFilter))]
    [Route("api/{culture}/[controller]")]
    public class AboutWithCultureInRouteController : Controller
    {
        // http://localhost:5000/api/it-CH/AboutWithCultureInRoute
        // http://localhost:5000/api/fr-CH/AboutWithCultureInRoute
 
        private readonly IStringLocalizer<SharedResource> _localizer;
 
 
        public AboutWithCultureInRouteController(IStringLocalizer<SharedResource> localizer)
        {
            _localizer = localizer;
        }
 
        [HttpGet]
        public string Get()
        {
            return _localizer["Name"];
        }
    }
}

URL used:

1	`http://localhost:5000/api/fr-CH/AboutWithCultureInRoute`

Result: ResoureKey: SharedResource, Key: Name, LocalizationCulture: fr-CH

1	`SharedResource.Name.fr-CH`

This should make it easy to find and add a missing localization.

Configuring the SQL Localization

The SQL Localization can also be configured to use different keys to search for the localization in the database. This can be configured in the Startup class using the services.AddSqlLocalization and adding the options parameter.

The SqlLocalizationOptions has two properties, UseTypeFullNames and UseOnlyPropertyNames. If the UseOnlyPropertyNames is true, only the property name is used in the database as the key with a ResourceKey global. You could also configure it to use FullNames as a key by setting the UseTypeFullNames. If this is set, the full type name is required in the ResourceKey property in the database.

public class SqlLocalizationOptions
{
    /// <summary>
    /// If UseOnlyPropertyNames is false, this property can be used to define keys with full type names or just the name of the class
    /// </summary>
    public bool UseTypeFullNames { get; set; }
 
    /// <summary>
    /// This can be used to use only property names to find the keys
    /// </summary>
    public bool UseOnlyPropertyNames { get; set; }
}

Example using options in the Startup class:

var sqlConnectionString = Configuration["DbStringLocalizer:ConnectionString"];
 
services.AddEntityFramework()
     .AddSqlite()
     .AddDbContext<LocalizationModelContext>(
         options => options.UseSqlite(sqlConnectionString));
 
// Requires that LocalizationModelContext is defined
services.AddSqlLocalization(options =>  options.UseTypeFullNames = true);

Used Controller for the HTTP request:

using System.Globalization;
using System.Threading;
 
namespace AspNet5Localization.Controllers
{
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.AspNetCore.Mvc.Localization;
    using Microsoft.Extensions.Localization;
 
    [Route("api/[controller]")]
    public class AboutController : Controller
    {
        private readonly IStringLocalizer<SharedResource> _localizer;
        private readonly IStringLocalizer<AboutController> _aboutLocalizerizer;
 
        public AboutController(IStringLocalizer<SharedResource> localizer, IStringLocalizer<AboutController> aboutLocalizerizer)
        {
            _localizer = localizer;
            _aboutLocalizerizer = aboutLocalizerizer;
        }
 
        [HttpGet]
        public string Get()
        {
            // _localizer["Name"] 
            return _aboutLocalizerizer["AboutTitle"];
        }
    }
}

Url:

1	`http://localhost:5000/api/about?culture=it-CH`

Result: You can see from the result, that the SQL localization searched for the localization using the FullName. ResoureKey: AspNet5Localization.Controllers.AboutController, Key: AboutTitle, LocalizationCulture: it-CH

1	`AspNet5Localization.Controllers.AboutController.AboutTitle.it-CH`

SQL Localization in detail

The SQL localization library uses extension methods to provide its service which can be used in the Startup class of your application. The library depends on Entity Framework Core. Any database provider can be used for this.

using System;
using Microsoft.Extensions.DependencyInjection.Extensions;
using Microsoft.Extensions.Localization;
 
namespace Microsoft.Extensions.DependencyInjection
{
    using global::Localization.SqlLocalizer;
    using global::Localization.SqlLocalizer.DbStringLocalizer;
 
    /// <summary>
    /// Extension methods for adding localization servics to the DI container.
    /// </summary>
    public static class SqlLocalizationServiceCollectionExtensions
    {
        /// <summary>
        /// Adds services required for application localization.
        /// </summary>
        /// <param name="services">The <see cref="IServiceCollection"/> to add the services to.</param>
        /// <returns>The <see cref="IServiceCollection"/>.</returns>
        public static IServiceCollection AddSqlLocalization(this IServiceCollection services)
        {
            if (services == null)
            {
                throw new ArgumentNullException(nameof(services));
            }
 
            return AddSqlLocalization(services, setupAction: null);
        }
 
        /// <summary>
        /// Adds services required for application localization.
        /// </summary>
        /// <param name="services">The <see cref="IServiceCollection"/> to add the services to.</param>
        /// <param name="setupAction">An action to configure the <see cref="LocalizationOptions"/>.</param>
        /// <returns>The <see cref="IServiceCollection"/>.</returns>
        public static IServiceCollection AddSqlLocalization(
            this IServiceCollection services,
            Action<SqlLocalizationOptions> setupAction)
        {
            if (services == null)
            {
                throw new ArgumentNullException(nameof(services));
            }
 
            services.TryAdd(new ServiceDescriptor(
                typeof(IStringLocalizerFactory),
                typeof(SqlStringLocalizerFactory),
                ServiceLifetime.Singleton));
            services.TryAdd(new ServiceDescriptor(
                typeof(IStringLocalizer),
                typeof(SqlStringLocalizer),
                ServiceLifetime.Singleton));
 
            if (setupAction != null)
            {
                services.Configure(setupAction);
            }
            return services;
        }
    }
}

The SqlStringLocalizerFactory class implements the IStringLocalizerFactory which is responsible for the database access. The database is only used for the first request of each resource. This means better performance after this, but new translations are read only after an application restart.

namespace Localization.SqlLocalizer.DbStringLocalizer
{
    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Linq;
    using Microsoft.Extensions.Localization;
    using Microsoft.Extensions.Options;
    using Microsoft.Extensions.PlatformAbstractions;
 
    public class SqlStringLocalizerFactory : IStringLocalizerFactory
    {
        private readonly LocalizationModelContext _context;
        private readonly ConcurrentDictionary<string, IStringLocalizer> _resourceLocalizations = new ConcurrentDictionary<string, IStringLocalizer>();
        private readonly IOptions<SqlLocalizationOptions> _options;
        private const string Global = "global";
 
        public SqlStringLocalizerFactory(
           LocalizationModelContext context,
           IApplicationEnvironment applicationEnvironment,
           IOptions<SqlLocalizationOptions> localizationOptions)
        {
            if (context == null)
            {
                throw new ArgumentNullException(nameof(LocalizationModelContext));
            }
 
            if (applicationEnvironment == null)
            {
                throw new ArgumentNullException(nameof(applicationEnvironment));
            }
 
            if (localizationOptions == null)
            {
                throw new ArgumentNullException(nameof(localizationOptions));
            }
 
            _options = localizationOptions;
            _context = context;
        }
 
        public IStringLocalizer Create(Type resourceSource)
        {
            SqlStringLocalizer sqlStringLocalizer;
 
            if (_options.Value.UseOnlyPropertyNames)
            {
                if (_resourceLocalizations.Keys.Contains(Global))
                {
                    return _resourceLocalizations[Global];
                }
 
                sqlStringLocalizer = new SqlStringLocalizer(GetAllFromDatabaseForResource(Global), Global);
                return _resourceLocalizations.GetOrAdd(Global, sqlStringLocalizer);
                 
            }
 
            if (_options.Value.UseTypeFullNames)
            {
                if (_resourceLocalizations.Keys.Contains(resourceSource.FullName))
                {
                    return _resourceLocalizations[resourceSource.FullName];
                }
 
                sqlStringLocalizer = new SqlStringLocalizer(GetAllFromDatabaseForResource(resourceSource.FullName), resourceSource.FullName);
                _resourceLocalizations.GetOrAdd(resourceSource.FullName, sqlStringLocalizer);
            }
 
 
            if (_resourceLocalizations.Keys.Contains(resourceSource.Name))
            {
                return _resourceLocalizations[resourceSource.Name];
            }
 
            sqlStringLocalizer = new SqlStringLocalizer(GetAllFromDatabaseForResource(resourceSource.Name), resourceSource.Name);
            return _resourceLocalizations.GetOrAdd(resourceSource.Name, sqlStringLocalizer);
        }
 
        public IStringLocalizer Create(string baseName, string location)
        {
            if (_resourceLocalizations.Keys.Contains(baseName + location))
            {
                return _resourceLocalizations[baseName + location];
            }
 
            var sqlStringLocalizer = new SqlStringLocalizer(GetAllFromDatabaseForResource(baseName + location), baseName + location);
            return _resourceLocalizations.GetOrAdd(baseName + location, sqlStringLocalizer);
        }
 
        private Dictionary<string, string> GetAllFromDatabaseForResource(string resourceKey)
        {
            return _context.LocalizationRecords.Where(data => data.ResourceKey == resourceKey).ToDictionary(kvp => (kvp.Key + "." + kvp.LocalizationCulture), kvp => kvp.Text);
        }
    }
}

The SqlStringLocalizer implements the IStringLocalizer. This is used as a singleton for the application as it is only GET resources.

namespace Localization.SqlLocalizer.DbStringLocalizer
{
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using Microsoft.Extensions.Localization;
 
    public class SqlStringLocalizer : IStringLocalizer
    {
        private readonly Dictionary<string, string> _localizations;
 
        private readonly string _resourceKey;
        public SqlStringLocalizer(Dictionary<string, string> localizations, string resourceKey)
        {
            _localizations = localizations;
            _resourceKey = resourceKey;
        }
        public LocalizedString this[string name]
        {
            get
            {
                return new LocalizedString(name, GetText(name));
            }
        }
 
        public LocalizedString this[string name, params object[] arguments]
        {
            get
            {
                return new LocalizedString(name, GetText(name));
            }
        }
 
        public IEnumerable<LocalizedString> GetAllStrings(bool includeParentCultures)
        {
            throw new NotImplementedException();
        }
 
        public IStringLocalizer WithCulture(CultureInfo culture)
        {
            throw new NotImplementedException();
        }
 
        private string GetText(string key)
        {
 
#if NET451
            var culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();
#elif NET46
            var culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();
#else
            var culture = CultureInfo.CurrentCulture.ToString();
#endif
            string computedKey = $"{key}.{culture}";
 
            string result;
            if (_localizations.TryGetValue(computedKey, out result))
            {
                return result;
            }
            else
            {
                return _resourceKey + "." + computedKey;
            }
        }
    }
}

The LocalizationModelContext class is the Entity Framework Core DbContext implementation. This uses the LocalizationRecord model class for data access. A shadow property is used for the database UpdatedTimestamp which will be used when updating with localization database imports.

namespace Localization.SqlLocalizer.DbStringLocalizer
{
    using System;
    using System.Linq;
 
    using Microsoft.EntityFrameworkCore;
 
    // >dotnet ef migrations add LocalizationMigration
    public class LocalizationModelContext : DbContext
    {
        public LocalizationModelContext(DbContextOptions<LocalizationModelContext> options) :base(options)
        { }
 
        public DbSet<LocalizationRecord> LocalizationRecords { get; set; }
 
        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<LocalizationRecord>().HasKey(m => m.Id);
            //builder.Entity<LocalizationRecord>().HasKey(m => m.LocalizationCulture + m.Key);
 
            // shadow properties
            builder.Entity<LocalizationRecord>().Property<DateTime>("UpdatedTimestamp");
 
            base.OnModelCreating(builder);
        }
 
        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();
            updateUpdatedProperty<LocalizationRecord>();
            return base.SaveChanges();
        }
 
        private void updateUpdatedProperty<T>() where T : class
        {
            var modifiedSourceInfo =
                ChangeTracker.Entries<T>()
                    .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);
 
            foreach (var entry in modifiedSourceInfo)
            {
                entry.Property("UpdatedTimestamp").CurrentValue = DateTime.UtcNow;
            }
        }
    }
}

Next Steps

The application could be packed as a NuGet and added to the NuGet server. The database could be optimized. I could also add some extra configuration options. I would like to implement an import, export function for SPA json files and also csv files which can be translated by external companys.

I’m open to feedback and would be grateful for tips on how I could improve this. Maybe this could be added to the ASP.NET Core.

Links:

Microsoft Docs: Globalization and localization

https://github.com/aspnet/Localization

https://damienbod.com/2015/10/24/using-dataannotations-and-localization-in-asp-net-5-mvc-6/

https://damienbod.com/2015/10/21/asp-net-5-mvc-6-localization/

https://github.com/aspnet/Tooling/issues/236

http://www.jerriepelser.com/blog/how-aspnet5-determines-culture-info-for-localization