none
Add-Migration generates duplicate index - Update-Database not executable RRS feed

  • Question

  • For each One-to-ZeroOrOne association with a navigation property on only one side (non-bidirectional) an index is created on the "required" end entity. The problem is that all indices have the same name. The result is an SQL exception "index already exists" when executing Update-Database.

    Two examples:

    Test1: Entity Test1A is referred by entities Test1B and Test1C.

    Test2: Entity Test2A referres entities Test2B and Test2C.

    Class MyContext
      Inherits DbContext
      Public Property Test1ASet As DbSet(Of Test1A)
      Public Property Test1BSet As DbSet(Of Test1B)
      Public Property Test1CSet As DbSet(Of Test1C)
      Public Property Test2ASet As DbSet(Of Test2A)
      Public Property Test2BSet As DbSet(Of Test2B)
      Public Property Test2CSet As DbSet(Of Test2C)
      Protected Overrides Sub OnModelCreating(mb As System.Data.Entity.DbModelBuilder)
        mb.Entity(Of Test1B).HasOptional(Function(x) x.NavPropBA).WithRequired()
        mb.Entity(Of Test1C).HasOptional(Function(x) x.NavPropCA).WithRequired()
        mb.Entity(Of Test2A).HasRequired(Function(x) x.NavPropAB).WithOptional()
        mb.Entity(Of Test2A).HasRequired(Function(x) x.NavPropAC).WithOptional()
      End Sub
    End Class
    Class Test1A
      <Key()> Public Property T1aId As Integer
    End Class
    Class Test1B
      <Key()> Public Property T1bId As Integer
      Property NavPropBA As Test1A
    End Class
    Class Test1C
      <Key()> Public Property T1cId As Integer
      Property NavPropCA As Test1A
    End Class
    Class Test2A
      <Key()> Public Property T2aId As Integer
      Property NavPropAB As Test2B
      Property NavPropAC As Test2C
    End Class
    Class Test2B
      <Key()> Public Property T2bId As Integer
    End Class
    Class Test2C
      <Key()> Public Property T2cId As Integer
    End Class

    Add-Migration generates two indices for Test1A.T1aId and two indices for Test2A.T2aId.

    Imports System.Data.Entity.Migrations Namespace Migrations Public Partial Class M0 Inherits DbMigration Public Overrides Sub Up() CreateTable( "Test1A", Function(c) New With { .T1aId = c.Int(nullable:=False) }) _ .PrimaryKey(Function(t) t.T1aId) _ .ForeignKey("Test1B", Function(t) t.T1aId) _ .ForeignKey("Test1C", Function(t) t.T1aId) _ .Index(Function(t) t.T1aId) _ .Index(Function(t) t.T1aId) CreateTable( "Test1B", Function(c) New With { .T1bId = c.Int(nullable:=False, identity:=True) }) _ .PrimaryKey(Function(t) t.T1bId) CreateTable( "Test1C", Function(c) New With { .T1cId = c.Int(nullable:=False, identity:=True) }) _ .PrimaryKey(Function(t) t.T1cId) CreateTable( "Test2A", Function(c) New With { .T2aId = c.Int(nullable:=False) }) _ .PrimaryKey(Function(t) t.T2aId) _ .ForeignKey("Test2B", Function(t) t.T2aId) _ .ForeignKey("Test2C", Function(t) t.T2aId) _ .Index(Function(t) t.T2aId) _ .Index(Function(t) t.T2aId) CreateTable( "Test2B", Function(c) New With { .T2bId = c.Int(nullable:=False, identity:=True) }) _ .PrimaryKey(Function(t) t.T2bId) CreateTable( "Test2C", Function(c) New With { .T2cId = c.Int(nullable:=False, identity:=True) }) _ .PrimaryKey(Function(t) t.T2cId) End Sub Public Overrides Sub Down() DropIndex("Test2A", New String() {"T2aId"}) DropIndex("Test2A", New String() {"T2aId"}) DropIndex("Test1A", New String() {"T1aId"}) DropIndex("Test1A", New String() {"T1aId"}) DropForeignKey("Test2A", "T2aId", "Test2C") DropForeignKey("Test2A", "T2aId", "Test2B") DropForeignKey("Test1A", "T1aId", "Test1C") DropForeignKey("Test1A", "T1aId", "Test1B") DropTable("Test2C") DropTable("Test2B") DropTable("Test2A") DropTable("Test1C") DropTable("Test1B") DropTable("Test1A") End Sub End Class End Namespace

    There are also multiple identical Drop-Statements generated, which would lead to errors when down-grading.

    DB Migrations should either create only a single index for similar associations or should give them unique names (supposed that the SQL server would not maintain the same thing multiple times).

    I didn't find a circumvention except to correct the generated code manually.

    Kind regards

    Peter

    Tuesday, February 28, 2012 11:03 AM

All replies

  • I updated to EntityFramework 4.3.1, but the problem still exists.
    Thursday, March 1, 2012 9:00 AM
  • How did you add the add the index with EF Migration? Could you post the steps?

    Fighting like Allen Iverson. Neve give up!


    Friday, March 2, 2012 8:26 AM
  • I didn't add the indices myself. I've created a model using CodeFirst with some associations between entities (see sample above). The I ran Enable-Migrations and Add-Migration commands. The result is the posted code. EF DBMigrations creates the indices on its own.
    Friday, March 2, 2012 10:21 AM
  • Hi Ralf Peter Lucke,

    Welcome to MSDN Forum.

    I can repro the issue, I will do more research on it and come back as soon as possible.

    Best Regards


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

    Monday, March 5, 2012 8:25 AM
    Moderator
  • Hi Ralf Peter Lucke,

    The error caused by the override method, you need modify the 'OnModelCreating' method as below

      Protected Overrides Sub OnModelCreating(mb As System.Data.Entity.DbModelBuilder)
        mb.Entity(Of Test1B).HasOptional(Function(x) x.NavPropBA).WithOptionalPrincipal()
        mb.Entity(Of Test1C).HasOptional(Function(x) x.NavPropCA).WithOptionalPrincipal()
        mb.Entity(Of Test2A).HasRequired(Function(x) x.NavPropAB).WithRequiredPrincipal()
        mb.Entity(Of Test2A).HasRequired(Function(x) x.NavPropAC).WithRequiredPrincipal()
      End Sub
    

    Best Regards

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

    Tuesday, March 6, 2012 2:10 AM
    Moderator
  • Hello Allen

    Thank you for your response, but it's not applicable. In my sample you changed the relationships from "optional" to "required" resp. from "required" to "optional". The first relationship, Test1A - Test1B, has a cardinality of ZeroOrOne-to-One. You changed it to ZeroOrOne-to-ZeroOrOne. The third relationship  has a cardinality of One-to-ZeroOrOne. You changed it to One-to-One. These changes would have an impact on the application.

    It should be changed (for the first and second relationship) to ...HasOptional(...).WithRequiredPrinciapl(), but when using HasOptional the method  WithRequiredPrincipal doesn't exist. Also, for the third and fourth relationship, when using HasRequired the method WithOptionalPrincipal doesn't exist.

    Kind regards

    Peter

    Tuesday, March 6, 2012 12:52 PM
  • Hi Ralf Peter Lucke,

    I will do more research on this issue and come back as soon as possible. Thanks for your understanding.

    Best Regards


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

    Wednesday, March 7, 2012 8:07 AM
    Moderator