.net c#每层级表和组合主键

Table-per-hierarchy and composite primary key
2020-11-21
  •  译文(汉语)
  •  原文(英语)

我在旧数据库(无法修改)中有两个表,其数据如下:

每层级表和组合主键

表1具有复合主键(代码,Abbrev),但是Abbrev也用作鉴别符(请参见下文).Table2具有两个外键列(CodeA,CodeB),它们均引用Table1中的相同字段Code.Table1.Code字段中有重复项.

我想在实体框架6中使用按表分层的方法.因此,我创建了以下模型类:

[Table("Table1")]
public class MyBaseClass
{
    [Key]
    public string Code { get; set; }
}

public class MyBaseClassA : MyBaseClass
{
}

public class MyBaseClassB: MyBaseClass
{
}

[Table("Table2")]
public class SubClass
{
    [Key]
    public int Id { get; set; }

    [Required]
    [ForeignKey("MyBaseClassA")]
    public string CodeA { get; set; }

    public virtual MyBaseClassA ClassA { get; set; }

    [Required]
    [ForeignKey("MyBaseClassB")]
    public string CodeA { get; set; }

    public virtual MyBaseClassB ClassB { get; set; }

}

我在DataContext:DbContext类中定义了按层次结构的表,如下所示:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyBaseClass>().Map<MyBaseClassA>(m => m.Requires("Abbrev").HasValue("A"))
            .Map<MyBaseClassB>(m => m.Requires("Abbrev").HasValue("B"));
    }

问题是当我想使用这样的映射时-我无法将鉴别符字段(Table1.Abbrev)用作MyBaseClass中复合键的一部分-我收到以下错误:

EntitySet'DataContext.MyBaseClass'中的所有对象都必须具有唯一的主键.但是,类型为"MyBaseClassA"的实例和类型为"MyBaseClassB"的实例都具有相同的主键值"EntitySet = MyBaseClass; Code = 1".

是否可以使用Entity Framework 6(或更高版本)映射以上模型?

速聊1:
我认为您的表1模型是错误的,您说您有一个代码和缩写的复合键,但是在您的实体中,您只有一个关键部分(代码).
速聊2:
从某种意义上说是正确的,但是我不能同时在DataContext类中使用鉴别符映射和在模型中使用相同属性(作为复合键的一部分),EF拒绝了它.
速聊3:
您需要修改数据吗?如果您只是阅读这些内容,则可以定义视图,以使其更容易使用EF和更好地进行编码.
速聊4:
是的,我也必须修改数据.
速聊5:
您是从旧数据库开始的,但是如果您首先尝试使用代码功能使其创建自己的数据库,您会发现loiti是正确的,表1的第一个模型是错误的.表1的键是no因为它只有一个密钥而不是一个复合密钥,所以不再正确.您必须将复合字段保留在该表中.EF怎么还不知道它是一个复合键,现在它认为表中只有一个键,因此将采取相应的行动.
解决过程1

恐怕使用实体框架是不可能的.

首先,您必须映射的完整键Table1,因为EF无法仅Table1通过识别对象Code.只是不支持作为复合主键一部分的鉴别符.

所以你不能亚型Table1.现在,仅此而已,您可以选择不使用继承.但是Table2才是真正的阻尼器.EF要求外键引用完整的主键.因此,由于Table1应该具有复合键,所以Table2两个外键也应该像{ Code, Abbrev }.好吧,中甚至没有一个Abbrev字段Table2.

您唯一可以做的就是Table1按原样映射(没有继承),并且Table2它们之间也没有任何关联.您必须手动编写(各种)联接才能在一个查询中从数据库中获取相关记录.

例如,Table2使用Table1as获得a A:

from t1 in context.Table1s
join t2 in context.Table2s on t1.Code equals t2.CodeA
where t1.Abbrev == "A"
select new { A = t1, t2 }

Table2同时带有Table1asATable1as的a B:

from t2 in context.Table2s
select new 
{
    t2,
    A = (from t1 in context.Table1s 
         where t1.Code == t2.CodeA && t1.Abbrev == "A")
        .FirstOrDefault(),
    B = (from t1 in context.Table1s 
         where t1.Code == t2.CodeB && t1.Abbrev == "B")
        .FirstOrDefault(),
}
解决过程2

创建一个新表,该表具有"Abbrev"列作为其主键.(在您的示例中,该表仅具有两行,并且"Abbrev"列的值分别为"A"和"B".)然后在此新表和现有Table1之间定义外键关系.在代码中,通过将"Abbrev"列附加为现有主键定义的一部分来更新Table1的MyBaseClass.

这应该解决MyBaseClass生成的"必须具有唯一的主键"错误.

I have two tables in a legacy database (which I cannot modify) with data as follows:

Table-per-hierarchy and composite primary key

Table1 has a composite primary key (Code, Abbrev), but Abbrev is also used as a discriminator (see below). Table2 has two foreign key columns (CodeA, CodeB), both referencing the same field Code in Table1. There are duplicates in the Table1.Code field.

I would like to use table-per-hierarchy approach with Entity framework 6. So, I created the following model classes:

[Table("Table1")]
public class MyBaseClass
{
    [Key]
    public string Code { get; set; }
}

public class MyBaseClassA : MyBaseClass
{
}

public class MyBaseClassB: MyBaseClass
{
}

[Table("Table2")]
public class SubClass
{
    [Key]
    public int Id { get; set; }

    [Required]
    [ForeignKey("MyBaseClassA")]
    public string CodeA { get; set; }

    public virtual MyBaseClassA ClassA { get; set; }

    [Required]
    [ForeignKey("MyBaseClassB")]
    public string CodeA { get; set; }

    public virtual MyBaseClassB ClassB { get; set; }

}

I defined table-per-hierarchy in my DataContext : DbContext class as follows:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyBaseClass>().Map<MyBaseClassA>(m => m.Requires("Abbrev").HasValue("A"))
            .Map<MyBaseClassB>(m => m.Requires("Abbrev").HasValue("B"));
    }

The problem is when I want to use such mapping - I can't use the discriminator field (Table1.Abbrev) as a part of a composite key in the MyBaseClass - I get the following error:

All objects in the EntitySet 'DataContext.MyBaseClass' must have unique primary keys. However, an instance of type 'MyBaseClassA' and an instance of type 'MyBaseClassB' both have the same primary key value, 'EntitySet=MyBaseClass;Code=1'.

Is it possible to map the model above with Entity framework 6 (or newer)?

Talk1:
i think your model of table 1 is wrong, you said you have a composite key of code and abbrev but in your entity you have just one key part (code).
Talk2:
In a way, that's right but I can't have both discriminator mapping in the DataContext class and the same property in the model (as a part of a composite key), EF refuses it.
Talk3:
Do you need to modify the data? If you're just reading from these you could define views that make it more straightforward to use EF against and nicer to code against.
Talk4:
Yes, I have to modify data as well.
Talk5:
You are starting from a legacy database, but if you were to try the feature of code first to make it create its own database, you will find that loiti is right, your first model of table 1 is wrong.Your key of table1 is no longer correct because it has a single key, not a composite. You must keep the composite field in that table. How else is EF going to know that it is a composite key, now it thinks there is only a single key to table one and is going to act accordingly.
Solutions1

I'm afraid this isn't possible with Entity Framework.

To begin with, you have to map the full key of Table1, because EF can't possibly identify Table1 objects by Code only. And a discriminator that's part of a compound primary key is just not supported.

So you can't subtype Table1. Now if that was all, you could choose not to use inheritance. But Table2 is the real damper. EF requires foreign keys to reference a full primary key. So, since Table1 should have a compound key, Table2's two foreigns key should also look like { Code, Abbrev }. Well, there isn't even one Abbrev field in Table2.

The only thing you can do is map Table1 as it is (without inheritance) and also Table2 without any association between them. You'll have to manually write joins (of sorts) to get related records from the database in one query.

For instance, to get a Table2 with a Table1 as A:

from t1 in context.Table1s
join t2 in context.Table2s on t1.Code equals t2.CodeA
where t1.Abbrev == "A"
select new { A = t1, t2 }

Or a Table2 with both a Table1 as A and a Table1 as B:

from t2 in context.Table2s
select new 
{
    t2,
    A = (from t1 in context.Table1s 
         where t1.Code == t2.CodeA && t1.Abbrev == "A")
        .FirstOrDefault(),
    B = (from t1 in context.Table1s 
         where t1.Code == t2.CodeB && t1.Abbrev == "B")
        .FirstOrDefault(),
}
Solutions2

Create a new table that has the "Abbrev" column as its primary key. (This table would only have two rows per your example with "Abbrev" column values of "A" and "B".) Then define a foreign key relationship between this new table and the existing Table1. In the code, update the MyBaseClass for Table1 by appending the "Abbrev" column as part of the existing primary key definition.

This should resolve the "must have unique primary keys" error generated by MyBaseClass.

转载于:https://stackoverflow.com/questions/28309701/table-per-hierarchy-and-composite-primary-key

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入