EntityFramework Core relation to composite key

Question

Consider the following database tables. Unfortunately the tables cannot be altered in any way.

Houses has an auto-increment ID field named Id, a string field named Name and an integer field named AreaId. The latter is not a foreign key to the Areas table.

Areas has a composite key consisting of AreaId, CountryId and LangId. An Area with the same AreaId can exist but with different CountryId and LangId. E.g.: There can be two rows with the same AreaId but different LangId.

NOTE: Why does a House have multiple Areas? A House doesn't have multiple Area's, it only has oneArea. TheArea`s table has a composite key, meaning that a specific row will have multiple translations. E.g.: Area ID 5 might have LangId 5 for English and LangId 3 for Spanish.

The two tables are described by the following two C# classes.

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

    [MaxLength(80)]
    public string Name { get; set; }

    public int? AreaId { get; set; }

    [ForeignKey("AreaId")]
    public List<Area> Areas { get; set; }
}

public class Area
{
    public int AreaId { get; set; }

    public int CountryId { get; set; }

    public string LangId { get; set; }

    public string Name { get; set; }
}

The composite key is defined in the context, exactly as stated in the docs.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Area>()
        .HasKey(a => new { a.AreaId, a.CountryId, a.LangId });
}

For example let's get a list of all the Houses in the database, including their respective areas.

_context.Houses.Include(h => h.Areas).ToList();

The following SQL is generated in the output window and the resulting List contains Houses incorrectly matched with the Areas.

SELECT [a].[AreaId], [a].[CountryId], [a].[LangId], [a].[Name]
FROM [Areas] AS [a]
WHERE EXISTS (
    SELECT 1
    FROM [Houses] AS [h]
    WHERE [a].[AreaId] = [h].[Id])
ORDER BY [a].[Id]

As you can see, EntityFramework relates [a].[AreaId] with [h].[Id] and not [h].[AreaId]. How can I express this relationship in EF?

Answer 1

You won't be able to map this correctly in EF. If you want House to refer to Area, the foreign key should consist of the same fields as Area's composite key, otherwise EF won't accept the mapping. A work-around could be to skip the mapping and to join the entities manually when necessary, but that conceals the real issue: poor design.

The major design flaw is that you have to duplicate an Area when translations are added. Now the question is -- and always will be -- Which record represents my physical Area entity? The basic premise of a relational database is that entities are represented by unique records. Your design violates that core principle.

Unfortunately the tables cannot be altered in any way.

Well, they should be! Leaving it this way shouldn't even be considered. You shouldn't work with a warped relational model, it's too pivotal for smooth application development.

The model, as I can piece it together from your description, should probably be something like this:

public class House
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? AreaId { get; set; }
    public Area Area { get; set; }
}

public class Area
{
    public int Id { get; set; }
    public int CountryId { get; set; }
    public Country Country { get; set; }
    public string Name { get; set; } // E.g. the name in a default language
    public ICollection<AreaTranslation> AreaTranslations { get; set; }
}

public class AreaTranslation
{
    public int AreaId { get; set; }
    public int LanguageId { get; set; }
    public string LocalizedName { get; set; }
}

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Language
{
    public int Id { get; set; }
    public string Name { get; set; }
}

For this model you need one explicit mapping instruction (EF will infer the rest):

modelBuilder.Entity<AreaTranslation>()
            .HasKey(a => new { a.AreaId, a.LanguageId });

You see that Area now genuinely represents a physical area out there. A House now naturally has one Area, not this weird collection of Areas that must be considered as one area somehow. The various languages come into play by the AreaTranslation junction class. I assume that an Area belongs to one Country.