EntityFramework与组合键的核心关系 [英] EntityFramework Core relation to composite key

问题描述

请考虑以下数据库表.不幸的是，这些表不能以任何方式更改.

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

Houses具有一个名为Id的自动递增ID字段，一个名为Name的字符串字段和一个名为AreaId的整数字段.后者不是Areas表的外键.

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具有由AreaId，CountryId和LangId组成的复合键.可以存在具有相同AreaId但具有不同CountryId和LangId的区域.例如:可以有两行具有相同的AreaId但不同的LangId.

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.

注意:为什么House有多个Area? House没有多个Area's, it only has one Area . The Area的表具有复合键，这意味着特定行将具有多个转换.例如:ID为5的区域可能具有英语的LangId 5和西班牙语的LangId 3.

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.

下面的两个C#类描述了这两个表.

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();

在输出窗口中生成以下SQL，并且结果列表包含与区域不正确匹配的房屋.

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]

如您所见，EntityFramework将[a].[AreaId]与[h].[Id]而不是[h].[AreaId]关联.如何在EF中表达这种关系?

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

推荐答案

您将无法在EF中正确映射它.如果要House引用Area，则外键应包含与Area的复合键相同的字段，否则EF将不接受映射.一种变通方法是跳过映射，并在必要时手动加入实体，但这掩盖了真正的问题:不良的设计.

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.

主要的设计缺陷是添加翻译时必须复制Area.现在的问题是-并将永远是-哪个记录代表我的物理Area实体?关系数据库的基本前提是实体由唯一记录表示.您的设计违反了这一核心原则.

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; }
}

对于此模型，您需要一条明确的映射指令(EF会推断其余的指令):

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

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

您会看到Area现在真正代表那里的物理区域.一个House现在自然具有一个Area，而不是必须以某种方式将其视为一个区域的Area怪异集合. AreaTranslation交接类会发挥各种语言的作用.我假设Area属于一个Country.

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.

这篇关于EntityFramework与组合键的核心关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！