none
EF code first error “The specified index already exists. [ IX_Id ]” for object tree RRS feed

  • Question

  • Hello,

    Using EF code first 4.3 I'm trying to model an object tree with a required-required relationships and a required-optional relationships.

    Here is a simple representation of those classes

    public class Top
    {
        public int Id { get; set; }
        public virtual Middle Middle { get; set; }
    }

    public class Middle
    {
        public int Id { get; set; }
        public virtual Child Child { get; set; }
    }

    public class Child
    {
        public int Id { get; set; }
    }

    Here is the OnModelCreating code

    modelBuilder.Entity<Top>().HasRequired(t => t.Middle).WithRequiredPrincipal().WillCascadeOnDelete();
    modelBuilder.Entity<Middle>().HasRequired(t => t.Child).WithOptional().WillCascadeOnDelete();

    This produces the error "The specified index already exists. [ IX_Id ]" on SQLCE

    After checking the db schema, both model binder fluent API configuration lines create an index IX_Id on the table Middles.

    Does anyone know how to work around that problem?

    Is there a way to set the index name?

    Thank you!

    Pascal

    PS: Question has also been asked on SO here

    http://stackoverflow.com/questions/9951522/ef-code-first-error-the-specified-index-already-exists-ix-id-for-object-t

    Monday, April 2, 2012 2:10 PM

All replies

  • Does this help?

    http://stackoverflow.com/questions/9662969/the-operation-failed-because-an-index-or-statistics-with-name-ix-id-already-ex


    Fighting like Allen Iverson. Neve give up!

    Wednesday, April 4, 2012 2:46 AM
  • Hi PascalN1,

    Welcome to MSDN Forum.

    The exception is caused by the configuration for the relationship.

    In the first one-to-one relationship configuration, you have specified "Top" entity is the principle, so "Middle" entity's primary key will also be the foreign key which dependent on "Top" entity. In the second one-to-one relationship configuration, you have specified "Child" entity is the principle, then the "Middle" entity's primary key will also be the foreign which dependent on "Child" entity. It obviously can't work.

    You can modify the code as below.

    modelBuilder.Entity<Top>().HasRequired(t => t.Middle).WithRequiredPrincipal().WillCascadeOnDelete();
    modelBuilder.Entity<Middle>().HasOptional(m => m.Child).WithRequired().WillCascadeOnDelete();

    -> A top entity must have a middle entity and a middle entity also must have a top entity.
    -> A child entity must have a middle entity and a middle entity needn't have a child entity.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, April 4, 2012 8:33 AM
    Moderator
  • Hello,

    Thank you for your answer.  I think I understand your point.  The reason why the child class has an Optional on Middle is because in my real class hierarchy there are 2 Middle classes that require to have a Child member like below.  So the Child entity could have a relationship with Middle1 or Middle2but now require any of them.  Is there a better way to model that? or express that? i.e. Can I add a ChildId property that would act as FK or something like that?

    Thanks a lot!

    Pascal

    public class Top
    {
        public int Id { get; set; }
        public virtual Middle1 Middle { get; set; }
    }
    public class Middle1
    {
        public int Id { get; set; }
        public virtual Child Child { get; set; }
    }
    public class Middle2
    {
        public int Id { get; set; }
        public virtual Child Child { get; set; }
    }
    public class Child
    {
        public int Id { get; set; }
    }

    Tuesday, April 10, 2012 3:40 PM