.Net Core 6.0 Web API - How to implement postgresql database(eg: Product Table -> Description column) localization for English and French?

Question

I am developing a Web API using Core 6.0 with localization. Localization should be supported for both static (e.g., basic strings like greeting) and dynamic content (e.g., Values of the Product Instance).

I have implemented the localization for static content using JsonStringLocalizerFactory as discussed in this article - https://christian-schou.dk/how-to-add-localization-in-asp-net-core-web-api/.

public class LocalizerController : ControllerBase
{
    private readonly IStringLocalizer<LocalizerController> _stringLocalizer;

    public LocalizerController(IStringLocalizer<LocalizerController> stringLocalizer)
    {
        _stringLocalizer = stringLocalizer;
    }

    [HttpGet]
    public IActionResult Get()
    {
        var message = _stringLocalizer["hi"].ToString();
        return Ok(message);
    }

    [HttpGet("{name}")]
    public IActionResult Get(string name)
    {
        var message = string.Format(_stringLocalizer["welcome"], name);
        return Ok(message);
    }

    [HttpGet("all")]
    public IActionResult GetAll()
    {
        var message = _stringLocalizer.GetAllStrings();
        return Ok(message);
    }
}

Next, I would like to implement localization for dynamic content (e.g., Details of the Product which will be sent to the WEB API and stored in the postgresql database table).

A possible approach is to duplicate the postgresql database table for each language (English and French). Could there be a better approach to avoid duplicate data and additional manual work?

Answer 1

You can create language table for each multi-language entity.

Langugage model;

public class Language
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string IsoCode { get; set; }
}

Static language list;

public class Constant
{
    public static List<Language> Languages { get; set; } = new()
    {
        new Language
        {
            Id = 1,
            Name = "English(United States)",
            IsoCode = "en-US"
        },
        new Language
        {
            Id = 2,
            Name = "Turkish",
            IsoCode = "tr-TR"
        }
    };
}

---

Entities;

public class Product 
{
    public int Id { get; set; }

    public decimal Price { get; set; }
   
    public virtual ICollection<ProductLang> ProductLangs { get; set; }
}

public class ProductLang
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

    [ForeignKey("Products")]
    public Guid ProductId { get; set; }

    public virtual Product Product { get; set; }

    public int LanguageId { get; set; }
}

You can change the LanguageId property name. If you want to store languages in database, you can create a Languages table and create a relationship with that table from entity language tables. This can reduce duplication.

After include the language table to the entity, you can write an extension method to easily get the requested language data.

public static string GetLang<TEntity>(this IEnumerable<TEntity> langs, Expression<Func<TEntity, string>> propertyExpression, int defaultLangId)
{
    var languageIdPropName = nameof(ProductLang.LanguageId);

    var requestedLangId = GetCurrentOrDefaultLanguageId(defaultLangId);

    if (langs.IsNullOrEmpty())
        return string.Empty;

    var propName = GetPropertyName(propertyExpression);

    TEntity requestedLang;

    if (requestedLangId != defaultLangId)
        requestedLang = langs.FirstOrDefault(lang => (int)lang.GetType()
                                                              .GetProperty(languageIdPropName)
                                                              .GetValue(lang) == requestedLangId)
                                                     ?? langs.FirstOrDefault(lang => (int)lang.GetType()
                                                                                              .GetProperty(languageIdPropName)
                                                                                              .GetValue(lang) == defaultLangId);
    else requestedLang = langs.FirstOrDefault(lang => (int)lang.GetType().GetProperty(languageIdPropName).GetValue(lang) == defaultLangId);

    requestedLang ??= langs.FirstOrDefault();

    return requestedLang.GetType().GetProperty(propName).GetValue(requestedLang, null)?.ToString();


    static int GetCurrentOrDefaultLanguageId(int defaultLanguageId)
    {
        var culture = CultureInfo.CurrentCulture;

        var currentLanguage = Constant.Languages.FirstOrDefault(i => i.IsoCode == culture.Name);

        if (currentLanguage != null)
            return currentLanguage.Id;
        else
            return defaultLanguageId;
    }

    static string GetPropertyName<T, TPropertyType>(Expression<Func<T, TPropertyType>> expression)
    {
        if (expression.Body is MemberExpression tempExpression)
        {
            return tempExpression.Member.Name;
        }
        else
        {
            var op = ((UnaryExpression)expression.Body).Operand;
            return ((MemberExpression)op).Member.Name;
        }
    }

}

This extension method checks for 3 conditions;

• If there is data in the requsted language, it returns this data,
• If there is no data in the requsted language, it checks if there is data in the default language. If the data is available in the default language, it will return the data,
• Returns the first available language data if there is no data in the default language

---

Usage;

    var defaultLangId = 1;

    Product someProduct = await _dbContext.Set<Product>().Include(i => i.ProductLangs).FirstOrDefaultAsync();

    var productName = someProduct.ProductLangs.GetLang(i => i.Name, defaultLangId);

It is up to you to modify this extension method according to your own situation. I gave you an example scenario where languages are kept in a static list.