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?
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.