Entity Framework Core - storing/querying multilingual records in the database efficiently

Question

I'm building an application that must support more than one language.

Therefore, some of the records in my database need to have multiple versions for each language.

I will first explain how I currently achieve this: consider an entity called Region which represents a geographical location and simply has a name.

I would design my entities like this:

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

    public List<RegionLanguage> Languages { get;set; }
}

public class RegionLanguage 
{
    public Region Region { get;set; } // Parent record this language applies to
    public string CultureCode { get; set; } // Will store culture code such as en-US or fr-CA

    // This column/property will be in the language specified by Culturecode
    [StringLength(255)]
    public string Name { get;set; }
}

From a database perspective, this works great because its infinitely scalable to any number of records. However, due to the way Entity Framework Core works, it becomes less scalable.

Using the above structure, I can query a Region and generate a view model based on specific culture information:

var region = _context.Regions.Where(e => e.Id == 34)
                     .Include(e => e.Languages)
                     .FirstOrDefault();

var viewModel = new RegionViewModel 
                    {
                         Name = region.Languages.FirstOrDefault(e => e.CultureCode == "en-US")?.Name // en-US would be dynamic based on the user's current language preference
                    }

You can see this becomes inefficient since I have to include ALL language records for the entity I'm fetching, when I actually only need one and then search for the correct language in memory. Of course this becomes even worse when I need to fetch a list of Regions which then has to return a large amount of unnecessary data.

Of course, this is possible using SQL directly simply by adding an extra clause on the join statement:

select * 
from Regions 
left join RegionLanguage on (RegionLanguage.Region = Regions.Id and RegionLanguage.CultureCode = 'en-US')

However, to my understanding, this is not possible to do natively from Entity Framework Core without using a RawQuery (EF: Include with where clause)

So that begs the question: is there a better way to achieve multilingual records in the database using EF Core? Or should I just continue with my approach and hope that EF Core implements Include filtering by the time my application actually needs it (I'll admit I might be optimizing slightly prematurely, but I'm genuinely curious if there is a better way to achieve this).

Did you tried with a left join linq query? – H. Herzl Jun 03 '19 at 04:24 — H. Herzl, Jun 03 '19 at 04:24

score 1 · Answer 1 · answered Jun 03 '19 at 19:50

1

You could use a Global Query Filter

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<RegionLanguage>(builder =>
    {
        builder.HasQueryFilter(rl => rl.CultureCode == "en-US");
    });
}

answered Jun 03 '19 at 19:50

Jan Paolo Go

5,842
4
22
50

I already looked into this, but it does not seem very elegant. Hardcoding "en-US" will not work, and using something like the _tenantId example on the linked page adds a ton of complexity because you then need to inject a service which determines the culture to use, which relies on HttpContext so now your data layer is reliant on HttpContext which is highly undesired. – Brad Jun 04 '19 at 16:42
not really sure about your requirement. will the `cultureCode` change at runtime? If so, how? If not, you could define `cultureCode` on your config file then apply config transform depending on region so you don't hardcode it. – Jan Paolo Go Jun 04 '19 at 16:46
1

Yes it will change depending on the request using the Accept-Language HTTP header. It's entirely dependant on which language the current user is using – Brad Jun 04 '19 at 17:36
You'd have to use a third party `Include` filter since EF Core doesn't support it yet. – Jan Paolo Go Jun 04 '19 at 17:46

score 0 · Answer 2 · answered Jun 03 '19 at 07:45

0

You can use projection.

var languageRegion = await _context.Regions
.Select(p => new Region
{
     Languages = p.Languages.FirstOrDefault(e => e.CultureCode == "en-US")
}.FirstOrDefaultAsync(e => e.Id == 34);

If regions and languages are not changing frequently you can use caching.

answered Jun 03 '19 at 07:45

Ahmet Arslan

5,380
2
33
35

This does seem more efficient, but I'm not to keen on the idea of having to write a projection on most of my queries. Is there anyway to project just the Languages property, and have all other properties still projected automatically? – Brad Jun 04 '19 at 16:45
Select reading is the most efficient solution if you only want to read values because select reading cause to not track entities. You can read other properties as well , just add related entities to in select command. – Ahmet Arslan Jun 05 '19 at 07:16

Entity Framework Core - storing/querying multilingual records in the database efficiently

2 Answers2