EntityFramework Primary Composite Key Relationship

Question

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

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

Areas has a composite key consisting of AreaId , CountryId and LangId . An area with the same AreaId may exist, but with different CountryId and LangId . For example: there may be two lines with the same AreaId , but different LangId .

NOTE. Why does House have several Area s? A House does not have several Area's, it only has one Area . The . The area table has a composite key, which means that a particular row will have multiple translations. For example: Area 5 identifier may 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 aggregate key is defined in context, as indicated in the documents.

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

For example, you can get a list of all the Houses in the database, including their respective fields.

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

The following SQL is created in the output window, and the resulting list contains houses that incorrectly match 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 associates [a].[AreaId] with [h].[Id] , not [h].[AreaId] . How can I express this attitude in EF?

Answer 1

You cannot correctly display this in EF. If you want House refer to Area , the foreign key must consist of the same fields as the Area composite, otherwise EF will not accept the mapping. A workaround may be to skip the display and merge the objects manually when necessary, but this hides the real problem: poor design .

The main design flaw is that when adding translations you have to duplicate Area . Now the question is - and always will be ... What record represents a physical Area object? The basic premise of a relational database is that entities are represented by unique records. Your design violates this basic principle.

Unfortunately, tables cannot be modified in any way.

Well, they should be! Leaving it this way cannot even be considered. You do not have to work with a perverse relational model; this is too important for smooth application development.

The model, since I can put it together with 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; } // Eg 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 display instruction (EF will select the rest):

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

You see that Area now truly represents a physical area. And House now, of course, has one Area , and not this strange set of Area , which must somehow be considered as one area. Different languages ​​come in the AreaTranslation class. I believe that a Area belongs to one Country .