How to set up selection of language for a user with Entity Framework code first

Question

I'm adding the ability to select a language for a user and a company in an existing Entity Framework code first application, and I'm not quite sure how to best go about it.

This is an example of the kind of scenario I want:

All available languages in the system: English, German, French, Spanish, Portuguese, Italian, Dutch, Danish, Swedish and Norwegian

Company A: English, German and French are available, English is default.

Company B: Spanish, Portuguese, Italian and English are available, Italian is default.

User X: Belongs to Company A, so English, German and French are available. When the user was created English was set to their selected language (as it's the default language for the company), but the user has selected French as their selected language instead.

User Y: Does not belong to any company. English and French are available, English is selected.

I've created the entities like this (additional properties removed for brevity):

public class Language
{
    [Key]
    public Guid Id { get; set; }
    public string LanguageCode { get; set; }
    public string Name { get; set; }
}

public class Company
{
    public ICollection<Language> AvailableLanguages { get; set; }
    public Language DefaultLanguage { get; set; }
}

public class User
{
    public ICollection<Language> AvailableLanguages { get; set; }
    public Language SelectedLanguage { get; set; }
}

This gives me a Languages table containing Id, LanguageCode, Name, CompanyId and UserId, which is fine for mapping companies and users to languages, but it doesn't give me anywhere to store the master list of languages. Do I need to create a separate entity for this to work, or what would be the best way to achieve this?

Does the list of available languages change depending on what user or what company it is? Or is the available languages simply a lookup table? — Drauka
I need both - there is a master list of all languages, and then when you create a company/user you select which languages are available for them. — Ingen Speciell
Is 'DefaultLanguage' and 'SelectedLanguage' part of 'AvailableLanguages' or are they separate. — Drauka

Slauma Slauma · Accepted Answer · 2014-03-14T17:37:26

Your description indicates that the relationships between Language and Company and between Language and User (with respect to the AvailableLanguages) are actually many-to-many relationships: A language can be available in many companies and a company can have many available languages - and the same for users.

In order to achieve many-to-many relationships you can leave the model as is but add a mapping with Fluent API:

modelBuilder.Entity<Company>()
    .HasMany(c => c.AvailableLanguages)
    .WithMany()
    .Map(m =>
    {
        m.ToTable("CompanyLanguages");
        m.MapLeftKey("CompanyId");
        m.MapRightKey("LanguageId");
    });

modelBuilder.Entity<User>()
    .HasMany(u => u.AvailableLanguages)
    .WithMany()
    .Map(m =>
    {
        m.ToTable("UserLanguages");
        m.MapLeftKey("UserId");
        m.MapRightKey("LanguageId");
    });

That will give you five tables now, the three entity tables and two additional link tables for the two many-to-many relationships. Language doesn't contain any foreign keys anymore and becomes the master language table you have in mind.

I don't see in your model how you would handle the situation that an additional language gets added to the list of available languages of a company. Do you want to update then the available languages for every user that belongs to the company? A better solution might be to give the user just a reference to a company (instead of his own list of available languages) and select the user's available languages from his company when you need it. The case that users do not belong to any company at all could possibly be solved by defining a special company "Anonymous company" or so that those users belong to.

How to set up selection of language for a user with Entity Framework code first

1 Answers