ASP.NET Core 1.0 using SQL Localization

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/AspNet5Localization

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

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

2016.11.22: Updated to .NET Core 1.1
2016.06.28: Updated to dotnet RTM version.
2016.05.14: Updated to dotnet RC2 version.
2016.01.29: Note This application is using ASP.NET Core 1.0 rc2. At present this has not been released, so you need to get the unstable version from MyGet, if you want to run it.

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": "1.0.4",


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:

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

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

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:

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

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

Using DataAnnotations and Localization in ASP.NET Core 1.0 MVC 6

ASP.NET Core 1.0 MVC Localization

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

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

Localization in ASP.NET 5 & MVC 6

https://github.com/aspnet/Mvc/tree/dev/test/WebSites/LocalizationWebSite

Example of localization middleware for culture in route

http://weblogs.asp.net/jeff/beating-localization-into-submission-on-asp-net-5

7 comments

  1. […] ASP.NET Core 1.0 using SQL Localization – Damien Bowden takes a look at using the SQL Localization features of ASP.NET Core and Entity Framework Core […]

  2. can’t bring this to life with the current implementation of asp.net core😦

    1. Hi Nils

      This is implemented with ASP.NET Core RC2. Localization has many changes, which are not included in RC1

      Greetings Damien

    2. Shahzad Hassan · · Reply

      @Nils: You can bring this to life in rc1 by including the code above in your codebase. You may need to adjust some namespaces and references. We needed the SqlLocalization support in our Ecommerce Application, so I did exactly the same and it works. Thanks to Damien for sharing it. There may be some feature missing, but it fulfils our requirements until RC2 is out.

      Let me know if you need more details

  3. Thanks Damien for a very valuable post.
    You say “…new translations are read only after an application restart…”, how can we reload strings based on some event, e.g. application is multilingual and selected language changes?

  4. Hi Damien ! Great resource, I’m using the code downloaded, I was looking for this solution. I’ve not been able to add your libto my project via nuget due to a Package Manager Console Error:

    PM> Add-Migration AggiuntaLocalizzazione -context LocalizationModelContext
    “Your target project ‘WebApp’ doesn’t match your migrations assembly ‘blablabla’. Either change your target project or change your migrations assembly. Change your migrations assembly by using DbContextOptionsBuilder. E.g. options.UseSqlServer(connection, b => b.MigrationsAssembly(“WebApp”)). By default, the migrations assembly is the assembly containing the DbContext.
    Change your target project to the migrations project by using the Package Manager Console’s Default project drop-down list, or by executing “dotnet ef” from the directory containing the migrations project. ”

    So, I downloaded and integrated sources in asp.net solution and now it works.
    I want to ask you a thing. Is there any way to mimic the classic localizer behaviour, returning the Not Found Key as the translated text ? This would be nice as library option.

    Thanks again for the code.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: