The operation failed because an index or statistic

Question

I'm using EF4.3.1 code first, I have the code as below,

 namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new myContext())
            {
                TestA ta = new TestA();
                ta.Name = "Hero";
                TestB tb = new TestB();
                tb.Name = "Allen";
                TestC tc = new TestC();
                tc.Name = "Iverson";
                ta.tb = tb;
                ta.tc = tc;
                context.testASet.Add(ta);
            }
        }
    }

    class TestA
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public TestB tb { get; set; }

        public TestC tc { get; set; }
    }

    class TestB
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public TestA ta { get; set; }
    }

    class TestC
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public TestA ta { get; set; }
    }

    class myContext : DbContext
    {
        public DbSet<TestA> testASet { get; set; }
        public DbSet<TestB> testBSet { get; set; }
        public DbSet<TestC> testCSet { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TestB>().HasOptional(x => x.ta).WithRequired();
            modelBuilder.Entity<TestC>().HasOptional(x => x.ta).WithRequired();

            modelBuilder.Entity<TestA>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<TestB>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<TestC>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);


        }
    }
}

When I debug the project, the exception is thrown, "The operation failed because an index or statistics with name 'IX_ID' already exists on table 'TestAs'." But, if I remove 'TestC' from the code, only 'TestA' and 'TestB', modify the code as below,

namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new myContext())
            {
                TestA ta = new TestA();
                ta.Name = "Hero";
                TestB tb = new TestB();
                tb.Name = "Allen";               
                ta.tb = tb;
                context.testASet.Add(ta);
            }
        }
    }

    class TestA
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public TestB tb { get; set; }

    }

    class TestB
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public TestA ta { get; set; }
    }


    class myContext : DbContext
    {
        public DbSet<TestA> testASet { get; set; }
        public DbSet<TestB> testBSet { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TestB>().HasOptional(x => x.ta).WithRequired();

            modelBuilder.Entity<TestA>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<TestB>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        }
    }
} 

everything works well. Anybody know why the exception was thrown?

Answer 1

You are using the shared primary key mapping with one dependent entity and 2 principle entities. According to your maaping primary key of TestA is a FK to TestB as well as TestC. That is why EF complains when it tries to create 2 FKs with same name.

IMO this is not a practical situation. Explain what relationships are among the entities so that situation can be modeled more realistically.