I am using Entity Framework 5.0.0.0 in a .net 4.5 console application, and I need to access a database with two tables in it with a foreign key relationship between them as follows:
The odd thing 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 the SQL server, since Almost1 and Almost2 combined unique, and none of them can be zero (in table A at least - they appear on B as an optional connection, but this is by by by).
Here are some SQL to create 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 fact is that this is not allowed by the Entity Framework - is that so, or am I just not correctly defining my model?
Here is my 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 => bA) .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); bA = this; } } public class B { public int BId { get; set; } public virtual AA { 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(); } } }
It throws an InvalidOperationException message:
The specified foreign key columns of the association "Almost1, Almost2" are invalid. The number of columns specified must match the number of columns of the primary key.
If I ignore the AId column and instead create Almost1 and Almost2 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 => bA) .WithMany(a => a.Bs) .Map(m => m.MapKey("Almost1", "Almost2")); }
This works, but I really do not want to do this, because there is a table (let it be called C ) that refers to A traditional way, having the AId column and the foreign key goes from C.AId to A.AId .
Yes, this is a little strange, I know - but is it possible to deal with the Entity Framework?