giannoug giannoug - 1 month ago 11
ASP.NET (C#) Question

EntityFramework Core relation to composite key

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

Database schema

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
Area
s? A
House
doesn't have multiple
Area's, it only has one
Area
. The
Area`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

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.