title: Entity Framework 5.0 composite foreign key to non primary key - is it possible?
body: <p>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:</p>
  
  <p><img src="https://i.stack.imgur.com/jlF9s.png" alt="overview"></p>
  
  <p>The odd thing about it is that the foreign key is between <code>B(Almost1, Almost2)</code> and <code>A(Almost1, Almost2)</code> not from <code>B(AId)</code> to <code>A(AId)</code>.  This is allowed by SQL server as <code>Almost1</code> and <code>Almost2</code> combined are unique and neither are nullable (on table <code>A</code> at least - on <code>B</code> they are as it is an optional relationship but that is by the by).  </p>
  
  <p>Here's some SQL for creating this situation:</p>
  
  <pre><code>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])
  </code></pre>
  
  <p>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?</p>
  
  <p>Here is my c#:</p>
  
  <pre><code>public class MyContext : DbContext
  {
      public MyContext(string connectionString) : base(connectionString)
      {
          MyAs = Set&lt;A&gt;();
          MyBs = Set&lt;B&gt;();
      }
  
      public DbSet&lt;A&gt; MyAs { get; private set; }
      public DbSet&lt;B&gt; MyBs { get; private set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          var aEntity = modelBuilder.Entity&lt;A&gt;();
          aEntity.ToTable("A");
          aEntity.HasKey(a =&gt; a.AId);
  
          var bEntity = modelBuilder.Entity&lt;B&gt;();
          bEntity.ToTable("B");
          bEntity.HasKey(a =&gt; a.BId);
          bEntity
              .HasOptional(b =&gt; b.A)
              .WithMany(a =&gt; a.Bs)
              .Map(m =&gt; 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&lt;B&gt; Bs { get; private set; }
      public void AddB(B b)
      {
          if (b == null) throw new ArgumentNullException("b");
          if (Bs == null) Bs = new List&lt;B&gt;();
          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();
          }
      }
  }
  </code></pre>
  
  <p>It throws an <code>InvalidOperationException</code> saying:</p>
  
  <blockquote>
    <p>The specified association foreign key columns 'Almost1, Almost2' are invalid. The number of columns specified must match the number of primary key columns.</p>
  </blockquote>
  
  <p>If I ignore the <code>AId</code> column and instead make <code>Almost1</code> and <code>Almost2</code> a composite primary key, so my <code>OnModelCreating</code> method now looks like this:</p>
  
  <pre><code>protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
      var aEntity = modelBuilder.Entity&lt;A&gt;();
      aEntity.ToTable("A");
      aEntity.HasKey(a =&gt; new { a.Almost1, a.Almost2 });
      aEntity.Ignore(a =&gt; a.AId);
  
      var bEntity = modelBuilder.Entity&lt;B&gt;();
      bEntity.ToTable("B");
      bEntity.HasKey(a =&gt; a.BId);
      bEntity
          .HasOptional(b =&gt; b.A)
          .WithMany(a =&gt; a.Bs)
          .Map(m =&gt; m.MapKey("Almost1", "Almost2"));
  }
  </code></pre>
  
  <p>It works, but I don't really want to do this as there is also a table (let's call it <code>C</code>) that relates to <code>A</code> in the traditional way by having an <code>AId</code> column and the foreign key is going from <code>C.AId</code> to <code>A.AId</code>.</p>
  
  <p>Yeah, it's a bit weird I know - but is it possible to deal with this in Entity Framework?</p>