sql-serverentity-frameworkindexingef-code-first

How to create a non clustered index specified by tuning Advisor, EF6.1 Code First


I've run a couple of examples through SQL Server Tuning Advisor and it has recommended an index, however I'm not quite sure how I would implement them with code first.

public class Exchange
{
    public int Id { get; set; }
    public int ExchangeSetId { get; set; }
    [Required]
    [MaxLength(5)]
    public string AreaCode { get; set; }
    [Required]
    [MaxLength(10)]
    public string BasePrefix { get; set; }
    [MaxLength(100)]
    public string BaseLabel { get; set; }
    [MaxLength(100)]
    public string BaseLocation { get; set; }
    [Required]
    [MaxLength(10)]
    public string DestPrefix { get; set; }
    [MaxLength(100)]
    public string DestLabel { get; set; }
    [MaxLength(100)]
    public string DestLocation { get; set; }
    [MaxLength(100)]
    public string Rule { get; set; }
    public int ClassId { get; set; }
}

The Index Tuning Advisor suggests is as follows

SET ANSI_PADDING ON

CREATE NONCLUSTERED INDEX [_dta_index_Exchanges_5_661577395__K3_K4_K7] ON [dbo].[Exchanges]
(
    [AreaCode] ASC,
    [BasePrefix] ASC,
    [DestPrefix] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

How would I go about adding this to my DB Context / Model?


Solution

  • Since you are using EF 6.1 you can use IndexAttribute. You add it to properties:

    [Index("IndexName", 1)]
    [Required]
    [MaxLength(5)]
    public string AreaCode { get; set; }    
    
    [Index("IndexName", 2)]
    [Required]
    [MaxLength(10)]
    public string BasePrefix { get; set; }
    
    [Index("IndexName", 3)]
    [Required]
    [MaxLength(10)]
    public string DestPrefix { get; set; }
    

    If you are using migrations you will need to add it using CreateIndex() option.