why code first want to create index column?

Question

I have two table like below:

[Table("MyFlashCard")]
public partial class MyFlashCard
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public MyFlashCard()
    {
        MyFlashCardPics = new HashSet<MyFlashCardPic>();
    }
    public int Id { get; set; }

    public int? FaslID { get; set; }

    public virtual FasleManJdl FasleManJdl { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<MyFlashCardPic> MyFlashCardPics { get; set; }
}

[Table("MyFlashCardPic")]
public partial class MyFlashCardPic
{
    public int Id { get; set; }

    [ForeignKey("MyFlashCard")]
    public int MyFlashCardId { get; set; }

    public virtual MyFlashCard MyFlashCard { get; set; }
}

and a ModelBuilder:

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
        modelBuilder.Entity<MyFlashCard>()
            .HasMany(e => e.MyFlashCardPics)
            .WithRequired(e => e.MyFlashCard)
            .HasForeignKey(e => e.MyFlashCardId)
            .WillCascadeOnDelete();
  }

and when I add migration it will create the below code:

CreateTable(
            "dbo.MyFlashCardPic",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    MyFlashCardId = c.Int(nullable: false),
                    MyFlashCard_Id = c.Int(),
                    MyFlashCard_Id1 = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCard_Id)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCard_Id1)
            .ForeignKey("dbo.MyFlashCard", t => t.MyFlashCardId, cascadeDelete: true)
            .Index(t => t.MyFlashCardId)
            .Index(t => t.MyFlashCard_Id)
            .Index(t => t.MyFlashCard_Id1);

I only have MyFlashCardId column in MyFlashCardPic table but it want to create another column like: MyFlashCard_Id, MyFlashCard_Id1

I want to know why this happens and how prevent it?

If I delete these columns from above migration,after creating database(with update-database command) it will throws below error when I want to use MyFlashCardPic entity

Invalid column name 'MyFlashCard_Id1' , Invalid column name 'MyFlashCard_Id'

and if I don't delete these columns from migration I have problem in editing flashcard that have pics like another question I have recently

How to find out context objects that one entity is attached to?

another point is that without

[ForeignKey("MyFlashCard")]

attribute it will create 3 index column and without

modelBuilder.Entity<MyFlashCard>()
            .HasMany(e => e.MyFlashCardPics)
            .WithRequired(e => e.MyFlashCard)
            .HasForeignKey(e => e.MyFlashCardId)
            .WillCascadeOnDelete();

in OnModeling it will create 4 index column

Apparently the issue is caused by something not shown here. Create a clean new project with single db context containing only what you have included in the post (and new database), and you'll see that the migration will not contain these additional columns. Hence you need to create minimal reproducible example. Once you do that, you'd probably find the cause. — Ivan Stoev
– Ivan Stoev, Commented Jul 27, 2017 at 6:57
There is a relationship between MyFlashCard & MyFlashCardPic which EF generates default FK naming instead of user-defined one (related_entity + _ + PK name). If there is already exist another relationship with same name, by default EF will add suffix numbers to avoid duplicate FK naming. You can try on another dummy project to find out this behavior. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Jul 31, 2017 at 6:59
Maybe "Add-Migration [SOMENAME] -Force" console command would solve it? — Alex
– Alex, Commented Aug 1, 2017 at 14:18

Jonathan Willcock · Accepted Answer · 2017-07-31 10:40:01Z

I think your problem is that you have both:

[ForeignKey("MyFlashCard")]
public int MyFlashCardId { get; set; }

and

public virtual MyFlashCard MyFlashCard { get; set; }

When adding the public virtual property you are prompting EF to set a foreign key relationship, which by default it does so with ClassNameId syntax. Because you have already created the FK yourself with the same name, it nevertheless still thinks it has to do something, so it creates another with 1 as a suffix. To get around the problem, remove your own ForeignKey entry and let EF do its stuff!

EDIT

I created a new MVC solution. Did the initial database create, then added a new class containing:

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations.Schema;

namespace MVCef.Models
{
    [Table("MyFlashCard")]
    public class MyFlashCard
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public MyFlashCard()
        {
            MyFlashCardPics = new HashSet<MyFlashCardPic>();
        }
        public int Id { get; set; }

        public int? FaslID { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<MyFlashCardPic> MyFlashCardPics { get; set; }
    }

    [Table("MyFlashCardPic")]
    public class MyFlashCardPic
    {
        public int Id { get; set; }

        public virtual MyFlashCard MyFlashCard { get; set; }
    }
}

Made appropriate changes in IdentityModel, created new migration and everything worked, as advertised - including foreign key creation. I suggest you try the same with a new solution. I think somewhere along the line, you have confused the hell out of EF!

no without [ForeignKey("MyFlashCard")]' attribute it will create 3 index column and with out modelBuilder...` in OnModeling it will create 4 index column

Tanveer Badar · Accepted Answer · 2017-08-01 12:57:29Z

0

You should either remove the [ForeignKey("MyFlashCard")] attribute or stop configuring it through model builder. Doing both together is the cause of your troubles.

How does index creation affect you in all this?

answered Aug 1, 2017 at 12:57

Tanveer Badar

5,5622 gold badges29 silver badges34 bronze badges

Comments

bubi · Accepted Answer · 2017-08-03 14:35:37Z

I inserted exactly your model (I only added another missing class with Id and Description properties). I inserted also the "double configuration" of the same foreign key.
These are the statements that EF 6.1.3 runs

ExecuteNonQuery==========
CREATE TABLE [FasleManJdls] (
 [Id] int not null identity(1,1)
, [Description] varchar(50) null
);
ALTER TABLE [FasleManJdls] ADD CONSTRAINT [PK_FasleManJdls_873b808d] PRIMARY KEY ([Id])
ExecuteNonQuery==========
CREATE TABLE [MyFlashCardPic] (
 [Id] int not null identity(1,1)
, [MyFlashCardId] int not null
);
ALTER TABLE [MyFlashCardPic] ADD CONSTRAINT [PK_MyFlashCardPic_873b808d] PRIMARY KEY ([Id])
ExecuteNonQuery==========
CREATE TABLE [MyFlashCard] (
 [Id] int not null identity(1,1)
, [FaslID] int null
, [FasleManJdl_Id] int null
);
ALTER TABLE [MyFlashCard] ADD CONSTRAINT [PK_MyFlashCard_873b808d] PRIMARY KEY ([Id])
ExecuteNonQuery==========
CREATE INDEX [IX_MyFlashCardId] ON [MyFlashCardPic] ([MyFlashCardId])
ExecuteNonQuery==========
CREATE INDEX [IX_FasleManJdl_Id] ON [MyFlashCard] ([FasleManJdl_Id])
ExecuteNonQuery==========
ALTER TABLE [MyFlashCardPic] ADD CONSTRAINT [FK_MyFlashCardPic_MyFlashCard_MyFlashCardId] FOREIGN KEY ([MyFlashCardId]) REFERENCES [MyFlashCard] ([Id])
ExecuteNonQuery==========
ALTER TABLE [MyFlashCard] ADD CONSTRAINT [FK_MyFlashCard_FasleManJdls_FasleManJdl_Id] FOREIGN KEY ([FasleManJdl_Id]) REFERENCES [FasleManJdls] ([Id])

No strange columns, exactly as I'm expecting, one foreign key ([MyFlashCardPic].[MyFlashCardId] REFERENCES [MyFlashCard].[Id]).
I'm quite sure that the problem is somewhere else.

Wich EF are you using?

EDIT
Why your classes are marked as partial? Are you sure that you don't have other code around?

Collectives™ on Stack Overflow

why code first want to create index column?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related