8

I have simple table definition in EF 6 code-first with simple foreign key.

public class Address
{
        /// <summary>
        /// Gets or sets the id.
        /// </summary>
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Column(Order = 1)]
        public int Id { get; set; }

         /// <summary>
        /// Gets or sets the town.
        /// </summary>
        public virtual Town Town { get; set; }

        /// <summary>
        /// Gets or sets the paf address town id.
        /// </summary>
        [Column(Order = 2)]
        public int TownId { get; set; }
}

When the table is created it is creating a foreign key as well as an index. I wonder why, because such index is usually very inefficient, and for big databases it causing a lot of issues. So why it created that index instead of foreign key only. And how to disable by default such index creating.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marcin
  • 3,232
  • 4
  • 31
  • 48
  • 6
    I beg to disagree: for **SQL Server**, an index on a foreign key column is a **very efficient** means to speed up JOINs and lookups! It's something I usually recommend on **any** foreign key column! – marc_s Mar 06 '14 at 13:24
  • OK, maybe I'm wrong about efficiency, but I still don't understand why it is added, we can add indexes in EF CF explicit. So why it is adding it by default and how to prevent such behaviour? I know how to cut this by using migrations, but I'm just curious. – Marcin Mar 06 '14 at 13:40
  • @Marcin Yes you can control the indexes yourself by using database migrations. There might also be a convention that you can remove that does this behavior, but I'm not positive on that part. – Dismissile Mar 06 '14 at 13:41
  • [Similar question here](http://stackoverflow.com/q/29707363/314291) – StuartLC Apr 17 '15 at 20:27

1 Answers1

3

This is just a convention of Entity Framework. If you don't like it, then you can enable migrations on your project and change the migration to not include the foreign key. I disagree with your assertion that it is inefficient, though.

To enable database migrations do the following:

  1. In the Package Manager console, type Enable-Migrations
  2. In the Package Manager console, type Add-Migration InitialMigration
  3. A new migration will be added to the Migrations folder, in it you will see an Up method with a few statements. Find the line that adds the foreign key and remove it.
  4. In the Package Manager console, type Update-Database to apply migrations.
  5. Repeat steps 2-4 for any new changes that come in.

This is assuming you do not have a database yet and are starting from scratch.

Dismissile
  • 32,564
  • 38
  • 174
  • 263
  • Thanks, this is what I did, to prevent that. Just was curious why it is happening. – Marcin Mar 06 '14 at 13:41
  • I assume they do it so that any joins across these relationships will be speedy as the database grows. In a small database it might be overkill, but when you add a lot of data, the index will be useful. It might be wasteful for a small database, but performance isn't really a concern for any tables when you have so little of it. – Dismissile Mar 06 '14 at 13:44
  • I will accept this answer, maybe will help for somebody – Marcin Mar 06 '14 at 13:48
  • Is there any other option because i have already created database and also have alote of table . Doing this manually will take much much time. Is there any other way to do the same thing ? – Usman lqbal Sep 14 '17 at 06:13