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

c# entity-framework-core

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).

1
1
6/3/2019 4:19:00 AM

Popular Answer

You could use a Global Query Filter

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<RegionLanguage>(builder =>
    {
        builder.HasQueryFilter(rl => rl.CultureCode == "en-US");
    });
}
0
6/3/2019 7:50:11 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow