Multilingual Data: Storing Names in Turkish and English — Translation Tables, JSON Columns and Fallback

Version note: The C# code in this post was tested with .NET 10 and EF Core 10, and the SQL examples with SQL Server 2025.

Guess First: What Does a User Searching for "iletişim" Find?

The HR Portal has expense categories, and the Turkish name of each category lives in a table. There is definitely a category called "İletişim" (communication) in that table. A user types "iletişim" in lowercase into the search box, and the backend runs this query:

string term = "iletişim";
int count = await db.ExpenseCategoryTranslations
    .CountAsync(t => t.Culture == "tr" && t.Name.Contains(term));

The database is SQL Server, and the column uses the server default collation, SQL_Latin1_General_CP1_CI_AS, which is case-insensitive. What do you think count will be? A second question: when the same table is sorted with ORDER BY Name, does "Işık ve enerji" come first, or "İletişim"? The answers are in the middle of the post, and both carry the sneakiest trap of multilingual data.

Context and Problem

In its first year the HR Portal was sold only to companies in Turkey, and everything was in Turkish. Then two customers opened offices abroad: one a 40-person team in London, the other 15 people in Berlin. Translating the UI was the easy part; i18n files on the Angular side and resx on the backend took care of buttons and menus. The real problem turned out to be the data itself: expense categories ("Transport", "Accommodation"), leave types, department names and product names in the purchasing catalog all had to appear in the user's language.

This is a different problem from UI translation. A resx file arrives with a deploy and is owned by developers; an expense category, on the other hand, is added, renamed and deactivated by the customer's HR manager through a screen. Its translations have to follow the same lifecycle: the Turkish name is required when the category is created, the English name is optional, and German may never be entered at all. The 120 tenants have on average 35 categories, 18 leave types and 40 departments; the number of rows to translate is a few thousand, but those rows are read in every list, every report and every search.

Three questions shape the design: where translations are stored, what to show when there is no translation in the user's language (fallback), and whose language rules apply to searching and sorting.

Symptoms

You know you have this problem when the table has columns like NameTr and NameEn, and the first step for a new language is writing a migration. The pattern lang == "en" ? x.NameEn : x.NameTr is repeated in dozens of places, and when someone forgets it a Turkish name appears on an English screen. A category without an English name shows up as an empty row in a dropdown. The reporting team can't answer "which categories are missing translations?", because a missing translation can't be told apart from a translation entered as an empty string. Turkish users report that "Işık" and "İletişim" are in the wrong order, and that searches don't find records starting with a capital İ.

The Naive Solution and Why It Breaks

The first version took the shortest path: one column per language.

public class ExpenseCategory
{
    public int Id { get; set; }
    public string Code { get; set; } = "";
    public string NameTr { get; set; } = "";
    public string NameEn { get; set; } = "";
}

string lang = "en";
var items = await db.ExpenseCategories
    .Select(c => new { c.Code, Name = lang == "en" ? c.NameEn : c.NameTr })
    .ToListAsync();
foreach (var i in items) Console.WriteLine($"{i.Code}: '{i.Name}'");

The columns are NOT NULL and the properties start as "", because someone decided "we don't want nulls". For the "Işık ve enerji" (utilities) category, whose English name hasn't been entered yet, the output is:

TRAVEL: 'Transport'
ENERGY: ''

This is the breaking point: a missing translation was stored as an empty value, and the user saw an empty row. To write a fallback you have to say NameEn != "" ? NameEn : NameTr in every query. When the Berlin office asks for German, you need a migration for a NameDe column, an entity change and a new branch in every one of those ternaries. The number of languages becomes limited by the schema rather than by the data.

Options

A) One Column per Language

The disciplined version of the naive solution: NameTr NOT NULL, NameEn NULL, and the fallback collected in a single projection helper. Acceptable if two languages are certain and a third isn't on the roadmap. Queries are simple, indexing is easy, and each language column can have its own collation (Turkish_100_CI_AS for NameTr). The cost: adding a language is a schema change.

B) Translation Table

Translations live as rows in a separate table: (CategoryId, Culture, Name). Adding a language is adding data; a missing translation means "no row" and can be queried. The cost is a join on every read and moving the fallback logic into SQL.

public class ExpenseCategory
{
    public int Id { get; set; }
    public string Code { get; set; } = "";
    public bool IsActive { get; set; } = true;
    public List<ExpenseCategoryTranslation> Translations { get; set; } = [];
}

public class ExpenseCategoryTranslation
{
    public int CategoryId { get; set; }
    public string Culture { get; set; } = "";
    public string Name { get; set; } = "";
}

C) JSON Column

Translations live as a JSON document in the entity's own row. EF Core 10 can map complex types to JSON and uses the new json data type on SQL Server 2025. For that you need to tell EF the compatibility level; without UseCompatibilityLevel(170) or UseAzureSql, the same model produces an nvarchar(max) column:

public class Department
{
    public int Id { get; set; }
    public LocalizedText Name { get; set; } = new();
}

public class LocalizedText
{
    public string Tr { get; set; } = "";
    public string? En { get; set; }
}

modelBuilder.Entity<Department>().ComplexProperty(d => d.Name, n => n.ToJson());

options.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(170));

CREATE TABLE [Departments] (
    [Id] int NOT NULL IDENTITY,
    [Name] json NOT NULL,
    CONSTRAINT [PK_Departments] PRIMARY KEY ([Id])
);

Queries are translated with JSON_VALUE ... RETURNING, and the fallback fits on one line:

SELECT COALESCE(JSON_VALUE([d].[Name], '$.En' RETURNING nvarchar(max)), JSON_VALUE([d].[Name], '$.Tr' RETURNING nvarchar(max)))
FROM [Departments] AS [d]
ORDER BY [d].[Id]

Watch out: because the complex type's properties are fixed, adding a language is still a code change in this form. If you really want languages to be data, you have to store the JSON as a {"tr": "...", "en": "..."} dictionary and query it yourself; EF Core 10 has no built-in support for mapping a Dictionary property to JSON. Then there's indexing: a json column can't be the key column of a regular index; when we tried, SQL Server returned: Column 'Name' in table 'dbo.Departments' is of a type that is invalid for use as a key column in an index or statistics. SQL Server 2025's CREATE JSON INDEX ... FOR ('$.En', '$.Tr') closes that gap, but you still can't have a unique constraint or a language-specific collation.

D) A Separate Translation Service

Translations live not in the database but in a translation management system or a separate service; the application asks by keys such as category.TRAVEL.name. That makes sense for content like a product catalog, where professional translators work and there's an approval flow. It's heavy for data users enter on screen: it means consistency across two systems for every record and an extra network call.

In the HR Portal we choose B for user-managed reference data such as categories and leave types; for fields that are only displayed and never searched, such as department names, C is acceptable.

Implementation: Schema and the Fallback Chain

The rules of the translation table live in the schema: at most one translation per category per language (composite key), no two categories with the same name in the same language (unique index), and an empty string doesn't count as a translation (check constraint).

modelBuilder.Entity<ExpenseCategoryTranslation>(b =>
{
    b.HasKey(t => new { t.CategoryId, t.Culture });
    b.Property(t => t.Culture).HasMaxLength(10).IsUnicode(false);
    b.Property(t => t.Name).HasMaxLength(100);
    b.HasIndex(t => new { t.Culture, t.Name }).IsUnique();
    b.ToTable(t => t.HasCheckConstraint("CK_ExpenseCategoryTranslations_Name", "LEN([Name]) > 0"));
});

The answer to "which language does the unique constraint apply to?" is "each language within itself": (Culture, Name). There can't be two "Yemek" categories in Turkish, but "Meals" in English and "Yemek" in Turkish can belong to the same category. The messages when the rules are violated:

The INSERT statement conflicted with the CHECK constraint "CK_ExpenseCategoryTranslations_Name". The conflict occurred in database "HrPortal", table "dbo.ExpenseCategoryTranslations", column 'Name'.
Cannot insert duplicate key row in object 'dbo.ExpenseCategoryTranslations' with unique index 'IX_ExpenseCategoryTranslations_Culture_Name'. The duplicate key value is (en, Transport).

The fallback chain starts at the user's language, goes down to the neutral language and from there to the tenant's default language. .NET has a ready-made model for this: CultureInfo.Parent.

public static class CultureChain
{
    public static string[] Build(string? requested, string fallback)
    {
        List<string> chain = [];
        if (!string.IsNullOrWhiteSpace(requested))
        {
            for (CultureInfo c = CultureInfo.GetCultureInfo(requested); c.Name.Length > 0; c = c.Parent)
            {
                if (!chain.Contains(c.Name)) chain.Add(c.Name);
            }
        }
        if (!chain.Contains(fallback)) chain.Add(fallback);
        return [.. chain];
    }
}

en-GB  -> en-GB -> en -> tr
de-DE  -> de-DE -> de -> tr
tr-TR  -> tr-TR -> tr
(none) -> tr

There are two ways to move the chain into SQL: pick the "best" translation per category with a subquery, or do one LEFT JOIN per link and combine them with COALESCE. The second produces more readable SQL, and each join seeks on the primary key. EF Core 10 translates the LeftJoin operator that arrived with .NET 10 directly:

public static IQueryable<CategoryDto> Localized(
    this IQueryable<ExpenseCategory> categories,
    IQueryable<ExpenseCategoryTranslation> translations,
    string[] chain)
{
    // The chain has at most three links: en-GB -> en -> tr
    string c0 = chain[0];
    string c1 = chain.Length > 1 ? chain[1] : chain[0];
    string c2 = chain[^1];
    string collation = SqlCollation.For(c0);

    return categories
        .LeftJoin(translations.Where(t => t.Culture == c0),
            c => c.Id, t => t.CategoryId, (c, t0) => new { c, t0 })
        .LeftJoin(translations.Where(t => t.Culture == c1),
            x => x.c.Id, t => t.CategoryId, (x, t1) => new { x.c, x.t0, t1 })
        .LeftJoin(translations.Where(t => t.Culture == c2),
            x => x.c.Id, t => t.CategoryId, (x, t2) => new { x.c, x.t0, x.t1, t2 })
        .Select(x => new
        {
            x.c.Id,
            x.c.Code,
            Name = x.t0!.Name ?? x.t1!.Name ?? x.t2!.Name,
            Culture = x.t0 != null ? c0 : x.t1 != null ? c1 : x.t2 != null ? c2 : null
        })
        .OrderBy(x => EF.Functions.Collate(x.Name ?? x.Code, collation))
        .Select(x => new CategoryDto(x.Id, x.Code, x.Name ?? x.Code, x.Culture ?? "-"));
}

CategoryDto carries not only the name but also which language the name came from. The UI can show fallback rows differently, and the admin screen can build its "missing translations" list from this field. The SQL generated for en-GB:

SELECT [e].[Id], [e].[Code], COALESCE([e1].[Name], [e3].[Name], [e5].[Name], [e].[Code]), ISNULL(CASE
    WHEN [e1].[CategoryId] IS NOT NULL AND [e1].[Culture] IS NOT NULL THEN @c00
    WHEN [e3].[CategoryId] IS NOT NULL AND [e3].[Culture] IS NOT NULL THEN @c10
    WHEN [e5].[CategoryId] IS NOT NULL AND [e5].[Culture] IS NOT NULL THEN @c20
END, N'-')
FROM [ExpenseCategories] AS [e]
LEFT JOIN (
    SELECT [e0].[CategoryId], [e0].[Culture], [e0].[Name]
    FROM [ExpenseCategoryTranslations] AS [e0]
    WHERE [e0].[Culture] = @c0
) AS [e1] ON [e].[Id] = [e1].[CategoryId]
LEFT JOIN (
    SELECT [e2].[CategoryId], [e2].[Culture], [e2].[Name]
    FROM [ExpenseCategoryTranslations] AS [e2]
    WHERE [e2].[Culture] = @c1
) AS [e3] ON [e].[Id] = [e3].[CategoryId]
LEFT JOIN (
    SELECT [e4].[CategoryId], [e4].[Culture], [e4].[Name]
    FROM [ExpenseCategoryTranslations] AS [e4]
    WHERE [e4].[Culture] = @c2
) AS [e5] ON [e].[Id] = [e5].[CategoryId]
WHERE [e].[IsActive] = CAST(1 AS bit)
ORDER BY COALESCE([e1].[Name], [e3].[Name], [e5].[Name], [e].[Code]) COLLATE Latin1_General_100_CI_AS

The endpoint takes the language from ASP.NET Core's request localization middleware. The supported languages are tr, en and en-GB; if a language outside that list arrives, the middleware falls back to the default tr:

builder.Services.Configure<RequestLocalizationOptions>(o =>
{
    o.SetDefaultCulture("tr").AddSupportedCultures("tr", "en", "en-GB").AddSupportedUICultures("tr", "en", "en-GB");
});

app.UseRequestLocalization();

app.MapGet("/api/expense-categories", async (HttpContext http, HrPortalDbContext db, CancellationToken ct) =>
{
    string culture = http.Features.Get<IRequestCultureFeature>()!.RequestCulture.UICulture.Name;
    string[] chain = CultureChain.Build(culture, "tr");

    List<CategoryDto> items = await db.ExpenseCategories
        .Where(c => c.IsActive)
        .Localized(db.ExpenseCategoryTranslations, chain)
        .ToListAsync(ct);

    http.Response.Headers.ContentLanguage = culture;
    http.Response.Headers.Vary = "Accept-Language";
    return Results.Ok(items);
});

A request from a user in London and one from a user in Berlin:

Accept-Language: en-GB,en;q=0.9
Content-Language: en-GB
[{"id":4,"code":"LODGING","name":"Accommodation","culture":"en"},{"id":3,"code":"COMMS","name":"Communication","culture":"en"},{"id":5,"code":"ENERGY","name":"Işık ve enerji","culture":"tr"},{"id":6,"code":"REFRESH","name":"Refreshments","culture":"en"},{"id":2,"code":"MEALS","name":"Subsistence","culture":"en-GB"},{"id":1,"code":"TRAVEL","name":"Transport","culture":"en"}]
Accept-Language: de-DE,de;q=0.9
Content-Language: tr
[{"id":6,"code":"REFRESH","name":"Çay ve ikram","culture":"tr"},{"id":5,"code":"ENERGY","name":"Işık ve enerji","culture":"tr"},{"id":3,"code":"COMMS","name":"İletişim","culture":"tr"},{"id":4,"code":"LODGING","name":"Konaklama","culture":"tr"},{"id":1,"code":"TRAVEL","name":"Ulaşım","culture":"tr"},{"id":2,"code":"MEALS","name":"Yemek","culture":"tr"}]

All three links work: "Subsistence" came from the en-GB-specific translation, "Transport" from neutral en, and "Işık ve enerji" from the Turkish default. The Vary: Accept-Language header matters: if this response goes into a cache, the language has to be part of the key, otherwise the London user sees Berlin's Turkish list. The same principle applies on the Angular side: when the user switches language, an HTTP interceptor should send the new Accept-Language value and reference data lists such as dropdowns should be reloaded; a list kept in memory in the old language is the most common bug.

Implementation: Search, Sorting and Collation

Now back to the opening question. Searching for "iletişim" while the table contains "İletişim" gave this result:

Contains: 0, Turkish collation: 1

SQL_Latin1_General_CP1_CI_AS is case-insensitive, but it doesn't know Turkish rules: for it, the uppercase of a lowercase i is a dotless I, and İ is a separate letter. Match counts for the LIKE 'term%' queries we ran on the same table with different terms:

Term     Latin1 Latin1_100 Turkish Turkish_100
iletişim 0      0          1       1
ILETIŞIM 0      0          0       0
İLETİŞİM 0      0          1       1
ışık     1      1          1       1
IŞIK     0      0          1       1

In the Turkish collation i matches İ and ı matches I; in the Latin collations, i matches I instead. This is the database counterpart of the Turkish I trap in C# (the "Strings in C#: Interpolation, StringBuilder and the Turkish I Trap" post in the C# series). The fix is to choose the collation by the language of the query. EF.Functions.Collate does this per query:

public static class SqlCollation
{
    public static string For(string culture) =>
        culture.StartsWith("tr", StringComparison.OrdinalIgnoreCase)
            ? "Turkish_100_CI_AS"
            : "Latin1_General_100_CI_AS";
}

int found = await db.ExpenseCategoryTranslations
    .CountAsync(t => t.Culture == "tr"
        && EF.Functions.Like(EF.Functions.Collate(t.Name, SqlCollation.For("tr")), "%" + term + "%"));

SELECT [e].[CategoryId], [e].[Culture], [e].[Name]
FROM [ExpenseCategoryTranslations] AS [e]
WHERE [e].[Culture] = 'tr' AND [e].[Name] COLLATE Turkish_100_CI_AS LIKE @p

The answer to the sorting question comes from the same place. In the Turkish alphabet I (ı) comes before İ (i):

Default: Çay ve ikram, İletişim, Işık ve enerji, Konaklama, Ulaşım, Yemek
Turkish: Çay ve ikram, Işık ve enerji, İletişim, Konaklama, Ulaşım, Yemek

The cost: an expression that applies COLLATE to a column can't use an index built with the column's own collation for a seek. For a few thousand rows of reference data the difference is too small to measure. For a table with millions of rows, such as a product catalog, you should consider a computed column with an index in each language's collation, or full-text search.

With a JSON column (option C) the same question goes one level deeper: SQL Server 2025's json type stores strings internally as Latin1_General_100_BIN2_UTF8. In our test, the JSON_VALUE result came back with the database's default collation, and Turkish sorting still required an explicit COLLATE. In short, the JSON option doesn't solve the collation problem; it only changes how the data is stored.

Trade-off Table

When Not to Use It

Don't build a translation table if there really are exactly two fixed languages; NameTr, a nullable NameEn and a single projection helper are enough and faster. If only the UI text is multilingual, meaning the data stays in one language, you don't need this pattern at all; resx and Angular i18n will do. Free text entered by users (the description in a leave request, a note on an invoice) isn't translated; you store it in one language, the one it was written in. Writing machine translations into the record and presenting them as real translations is a separate mistake: if you store them without marking their source, nobody knows which text is approved.

Checklist

• A missing translation is expressed as "no row" or NULL; empty strings are blocked by a check constraint.
• The default language (usually the tenant's language) is required; other languages are optional.
• The fallback chain is built in one place: en-GB → en → tr.
• The response carries which language the name came from; the missing translations report is built from that field.
• The unique constraint is within a language: (Culture, Name).
• Search and sorting use a collation that fits the query's language; there's a test for the Turkish I.
• Responses that vary by language send Vary: Accept-Language, and the cache key includes the language.
• With the JSON option, UseCompatibilityLevel(170) was chosen deliberately and a JSON index is planned for searches.

Related Patterns and Sources

This pattern continues the series post "Enum or Lookup Table? Keeping Reference Data in Code and in the Database": there we moved a code list into the database; here we make that list's names multilingual. In a setup where each tenant manages its own categories, the translation table has to work together with tenant isolation, the subject of the next post. For the mechanics, see "What's New in EF Core 10: Named Query Filters, LeftJoin and Complex Types in JSON" and "What's New in EF Core 8: Complex Types, Primitive Collections and SqlQuery<T>" in the EF Core series, and the Turkish I post in the C# series for character behavior. Caching the response is a topic for the planned Caching series. Sources: on Microsoft Learn, "What's New in EF Core 10" (JSON type support and the compatibility level 170 note), "JSON data type" (the SQL Server 2025 json type, UTF-8 BIN2 storage, index restrictions), "CREATE JSON INDEX" and the ASP.NET Core "Globalization and localization" docs; the list of Windows collations in the SQL Server collation docs. For telling the options apart, the EAV discussion in Bill Karwin's "SQL Antipatterns" is also a good warning: as long as the translation table stays narrow and typed, it doesn't turn into EAV.

A Small Experiment

★☆☆ Add a de translation for one category to the translation table and watch the culture field change in the result of a de-DE request. Then print the chain CultureChain builds for de.

★★☆ Write the "missing translations" query for the admin screen: list every active category that has no en translation. Write one version with LeftJoin and one with NOT EXISTS, and compare the generated SQL.

★★★ Store department names in a json column as a {"tr": "...", "en": "..."} dictionary (instead of a complex type), write a query with fallback using JSON_VALUE as raw SQL, and index the $.en path with CREATE JSON INDEX.

The translations now live in the data, but these tables hold the data of 120 tenants in the same database. If company A's "Transport" category shows up in company B's list, that's not a translation bug, it's a security breach. In the next post we'll discuss data isolation in multi-tenancy: database per tenant, shared schema and SQL Server row-level security.