Unhandled Exception after Upgrading to Entity Framework 4.3.1

Question

Error:

Unhandled Exception: System.Data.SqlClient.SqlException: The operation failed because an index or statistics with name 'IX_ID' already exists on table 'PrivateMakeUpLessons'.

Model (Simplified, building in a separate test project for debugging):

public abstract class Lesson
{
    public Guid ID { get; set; }
    public string Room { get; set; }
    public TimeSpan Time { get; set; }
    public int Duration { get; set; }
}

public abstract class RecurringLesson : Lesson
{
    public int DayOfWeek { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public string Frequency { get; set; }
}

public class PrivateLesson : RecurringLesson
{
    public string Student { get; set; }
    public string Teacher { get; set; }
    public virtual ICollection<Cancellation> Cancellations { get; set; }
}

public class Cancellation
{
    public Guid ID { get; set; }
    public DateTime Date { get; set; }
    public virtual PrivateLesson Lesson { get; set; }
    public virtual MakeUpLesson MakeUpLesson { get; set; }
}

public class MakeUpLesson : Lesson
{
    public DateTime Date { get; set; }
    public string Teacher { get; set; }
    public virtual Cancellation Cancellation { get; set; }
}

Configuration:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Lesson>().ToTable("Lessons");
    modelBuilder.Entity<RecurringLesson>().ToTable("RecurringLessons");
    modelBuilder.Entity<PrivateLesson>().ToTable("PrivateLessons");
    modelBuilder.Entity<MakeUpLesson>().ToTable("PrivateMakeUpLessons");

    modelBuilder.Entity<Cancellation>()
        .HasOptional(x => x.MakeUpLesson)
        .WithRequired(x => x.Cancellation);

    base.OnModelCreating(modelBuilder);
}

Notes:

This worked fine in EF 4.2. Is there something wrong with my model? The actual model is much more complicated which is why I have all the classes abstracted out. Also, I am working against an existing database so I need to use Table-Per-Type inheritance.

If I change the relationship of Cancellation to PrivateMakeUpLesson from 1 to 0..1 to 0..1 to 0..1 it works. This is undesirable because you can't have a PrivateMakeUpLesson without a Cancellation.

Also, if I make PrivateMakeUpLesson NOT inherit from Lesson then it also works, but it IS a lesson and needs to remain so for existing business logic.

I'd appreciate any guidance. Thank you!

Edit:

Starting a bounty. I can't find any documentation on what changed between EF 4.2 and EF 4.3 with regard to the index generation for code first. It's clear that EF 4.3 is creating more indexes and that the naming scheme has changed but I want to know if there's a bug in EF or if there is something fundamentally wrong with my model or fluent API configuration.

Answer 1

As of EF 4.3, indexes are added for freign key columns during database creation. There is a bug that can cause an index to be created more than once. This will be fixed in a future EF release.

Until then, you can work around the issue by creating your database using Migrations instead of database initializers (or the Database.Create() method).

After generating the initial migration, you will need to delete the redundant call to Index().

CreateTable(
    "dbo.PrivateMakeUpLessons",
    c => new
        {
            ID = c.Guid(nullable: false),
            ...
        })
    .PrimaryKey(t => t.ID)
    .ForeignKey("dbo.Lessons", t => t.ID)
    .ForeignKey("dbo.Cancellations", t => t.ID)
    .Index(t => t.ID)
    .Index(t => t.ID); // <-- Remove this

To continue creating your database at run-time, you can use the MigrateDatabaseToLatestVersion initializer.

Answer 2

In my opinion this is clearly a bug.

The problem starts with the observation that EF creates an index IX_ID at all. If you strip down the model to the following...

public abstract class Lesson
{
    public Guid ID { get; set; }
}

public class RecurringLesson : Lesson
{
}

public class MyContext : DbContext
{
    public DbSet<Lesson> Lessons { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<RecurringLesson>().ToTable("RecurringLessons");
    }
}

... and let EF create the database schema you get two tables Lessons and RecurringLessons as expected for a TPT inheritance mapping. But I am wondering why it creates two indices for the table RecurringLessons:

• Index PK_RecurringLessons (clustered, unique) with Index column ID
• Index IX_ID (not clustered, not unique) with Index column ID again

I don't know if there is any benefit for the database to have a second index on the same column. But for my understanding it doesn't make sense 1) to create an index on the same column that is already covered in the PK clustered index, and 2) to create a not unique index on a column which is the primary key and therefore necessarily unique.

Moreover due to the one-to-one relationship EF tries to create an index on the table of the dependent of this association which is PrivateMakeUpLessons. (It's the dependent (and not the principal) because Cancellation is required in entity MakeUpLesson.)

ID is the foreign key in this association (and primary key at the same time because one-to-one relationships are always shared primary key associations in Entity Framework). EF apparently always creates a index on the foreign key of a relationship. But for one-to-many relationships this is not a problem because the FK column is different from the PK column. Not so for one-to-one relatonships: The FK and PK are the same (that is ID), hence EF tries to create an index IX_ID for this one-to-one relationship which already exists due to the TPT inheritance mapping (which leads to a one-to-one relationship as well from database perspective).

The same consideration as above applies here: The table PrivateMakeUpLessons has a clustered PK index on column ID. Why is a second index IX_ID on the same column required at all?

In addition EF doesn't seem to check that it already wants to create an Index with name IX_ID for the TPT inheritance, leading finally to the exception in the database when the DDL is sent to create the database schema.

EF 4.2 (and before) didn't create any indices (except PK indices) at all, this was introduced in EF 4.3, especially indices for FK columns.

I didn't find a workaround. In the worst case you have to create the database schema manually and avoid that EF tries to create it (= disable database initialization). In the best case there is a way to disable automatic FK index creation, but I don't know if it's possible.

You can submit a bug report here: http://connect.microsoft.com/VisualStudio

Or maybe someone from EF development team will see your question here and provide a solution.

Answer 3

I got a very similar error to this one in my code a while back. Try putting the cancellation list inside the Lesson class. That's what solved my problem.