I'm working on an ASP.NET Core 3.1 (w/EF Core 3.1) application that contains entities such as Country and State, whose field Description needs to be localized. The app will be available in several countries, and of course in each language the countries and states are written in a different way (United States in english, Estados Unidos in spanish).
Now, the challenge comes in the way to localize this data. Because this data (and many other entities in our model) will need to be sorted and filtered through the API, and if we, for example, save a reference key in the Description field of Country table and then we query that table with LINQ and sort the data, it will only be sorting by the keys in those records, and not the actual data that will be translated and saved in another place (table or resource file). For example, United States will be near the bottom if we sort the countries list in english, but Estados Unidos will be somewhere at the beginning/middle in spanish.
What would be a best practice or the best approach to implement this functionality?
So far in an old version of this app, the implementation was to keep translations in a separate table, and a key from the Description columns on those localized tables were used to bring the localized strings by using a SQL Server Function that queried the table with the translations and replaced the data during the query. On our team we thought this created a really heavy load on all queries because there could be hundreds or thousands of records on a single query that needed to be translated and applying a function on each of those columns on each record slows down performance a lot.