实体框架5.0外键非主键 - 这可能吗？ [英] Entity Framework 5.0 composite foreign key to non primary key - is it possible?

问题描述

我在.NET 4.5的控制台应用程序中使用实体框架5.0.0.0和我有它两个表来访问数据库，象这样它们之间的外键关系：

I am using Entity Framework 5.0.0.0 in a .net 4.5 console application and I have to access a database with two tables in it with a foreign key relationship between them like so:

奇怪的它是一个外键都是B之间（Almost1，差不多有）和 A（Almost1，差不多有）不是来自 B（AID）到 A（AID）。这是由SQL服务器允许作为 Almost1 和差不多有组合是独一无二的，也不是可空（上表 A 至少 - 对 B 他们是因为它是一个可选的关系，但那是靠了靠）。

The odd thing about it is that the foreign key is between B(Almost1, Almost2) and A(Almost1, Almost2) not from B(AId) to A(AId). This is allowed by SQL server as Almost1 and Almost2 combined are unique and neither are nullable (on table A at least - on B they are as it is an optional relationship but that is by the by).

下面是一些SQL创建这样的情况：

Here's some SQL for creating this situation:

CREATE TABLE [dbo].[A](
    [AId] [int] IDENTITY(1,1) NOT NULL,
    [Almost1] [int] NOT NULL,
    [Almost2] [int] NOT NULL,
 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 
(
    [AId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [A_Constraint] UNIQUE NONCLUSTERED 
(
    [Almost1] ASC,
    [Almost2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[B](
    [BId] [int] IDENTITY(1,1) NOT NULL,
    [Almost1] [int] NULL,
    [Almost2] [int] NULL,
 CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED 
(
    [BId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[B] ADD  CONSTRAINT [FK_A_B] FOREIGN KEY([Almost1], [Almost2])
REFERENCES [dbo].[A] ([Almost1], [Almost2])

问题是，它似乎没有被允许？实体框架 - 是这种情况还是我只是没有正确定义我的模型

The thing is, it seems that it is not allowed by Entity Framework - is this the case or am I just not defining my model correctly?

下面是我的C＃：

public class MyContext : DbContext
{
    public MyContext(string connectionString) : base(connectionString)
    {
        MyAs = Set<A>();
        MyBs = Set<B>();
    }

    public DbSet<A> MyAs { get; private set; }
    public DbSet<B> MyBs { get; private set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var aEntity = modelBuilder.Entity<A>();
        aEntity.ToTable("A");
        aEntity.HasKey(a => a.AId);

        var bEntity = modelBuilder.Entity<B>();
        bEntity.ToTable("B");
        bEntity.HasKey(a => a.BId);
        bEntity
            .HasOptional(b => b.A)
            .WithMany(a => a.Bs)
            .Map(m => m.MapKey("Almost1", "Almost2"));
    }
}

public class A
{
    public int AId { get; set; }
    public int Almost1 { get; set; }
    public int Almost2 { get; set; }
    public virtual ICollection<B> Bs { get; private set; }
    public void AddB(B b)
    {
        if (b == null) throw new ArgumentNullException("b");
        if (Bs == null) Bs = new List<B>();
        if (!Bs.Contains(b)) Bs.Add(b);
        b.A = this;
    }
}

public class B
{
    public int BId { get; set; }
    public virtual A A { get; set; }
}

class Program
{
    static void Main()
    {
        using (var ctx = new MyContext(@"connection string"))
        {
            ctx.MyAs.Add(new A { Almost1 = 1, Almost2 = 1 });
            ctx.SaveChanges();
        }
    }
}

它抛出一个出现InvalidOperationException 说：

指定的关联外键列'Almost1，差不多有'无效。指定要主键列的数目相匹配的列数。

The specified association foreign key columns 'Almost1, Almost2' are invalid. The number of columns specified must match the number of primary key columns.

如果我忽略 AID 列，而是让 Almost1 和差不多有复合主键，所以我的 OnModelCreating 方法现在看起来是这样的：

If I ignore the AId column and instead make Almost1 and Almost2 a composite primary key, so my OnModelCreating method now looks like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    var aEntity = modelBuilder.Entity<A>();
    aEntity.ToTable("A");
    aEntity.HasKey(a => new { a.Almost1, a.Almost2 });
    aEntity.Ignore(a => a.AId);

    var bEntity = modelBuilder.Entity<B>();
    bEntity.ToTable("B");
    bEntity.HasKey(a => a.BId);
    bEntity
        .HasOptional(b => b.A)
        .WithMany(a => a.Bs)
        .Map(m => m.MapKey("Almost1", "Almost2"));
}

它的工作原理，但我真的不希望这样做，因为有也是一个表（姑且称之为 C ），它由具有<$ C $涉及到 A 的传统方式C> AID 列和外键是从 C.AId 将 A.AId

It works, but I don't really want to do this as there is also a table (let's call it C) that relates to A in the traditional way by having an AId column and the foreign key is going from C.AId to A.AId.

是的，这有点不可思议，我知道 - 但有可能处理这个实体框架

Yeah, it's a bit weird I know - but is it possible to deal with this in Entity Framework?

推荐答案

正如前面提到的，因为EF不支持唯一的密钥和其他SQL对象。我结束了创建一些脚本嵌入的资源，让他们执行的降种子过程的一部分/创建初始化。

As mentioned, since EF doesn't support unique keys and other SQL objects. I ended up creating some scripts as embedded resources and have them execute as part of the seed process of the drop/create initializer.

不知道这是否会允许你浏览在代码中的对象之间，但它工作得很好让数据库获得一个进程更新。

Not sure if this will allow you to navigate between the objects in code, but it works pretty nicely to have the database get updated in one process.

这篇关于实体框架5.0外键非主键 - 这可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！