How to define a multi-field index with Entity Framework Core 2.1

Question

I am working on an ASP.Net Core 2.1 API and I use Entity Framework Core 2.1. I am using migrations to manage changes to the database. My backing data store is an instance of Azure SQL Server.

I need to add a multi-field non-clustered index to one of my tables but I am having difficulty finding a concise reference on how to do this in my google searches.

I tried using the [Index()] data annotation in the POCO class but it was not recognized. So, I assume I have to do this in the OnModelCreating method of my DbContext class, but I have not found an example of how to do that for a multi-field non-clustered index.

Here is an example entity class

public class H1Record : EntityBase
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [ForeignKey("ShippingServicesFileId")]
    public ShippingServicesFile ShippingServicesFile { get; set; }

    [Required]
    public int ShippingServicesFileId { get; set; }

    [Column(TypeName = "varchar(20)")]
    public string BatchId { get; set; }

    [Column(TypeName = "varchar(34)")]
    [MaxLength(34)]
    public string ElectronicFileNumber { get; set; }

    [Column(TypeName = "varchar(1)")]
    [MaxLength(1)]
    public string ElectronicFileType { get; set; }

    [Column(TypeName = "varchar(8)")]
    [MaxLength(8)]
    public string DateOfMailing { get; set; }

    [Column(TypeName = "varchar(6)")]
    [MaxLength(6)]
    public string TimeOfMailing { get; set; }

    public DateTime MailingDateTime { get; set; }

    [Column(TypeName = "varchar(1)")]
    [MaxLength(1)]
    public string EntryFacilityType { get; set; }

    [Column(TypeName = "varchar(5)")]
    [MaxLength(5)]
    public string EntryFacilityZipCode { get; set; }

    [Column(TypeName = "varchar(4)")]
    [MaxLength(4)]
    public string EntryFacilityZipPlus4 { get; set; }

    [Column(TypeName = "varchar(2)")]
    [MaxLength(2)]
    public string DirectEntryOriginCountryCode { get; set; }

    [Column(TypeName = "varchar(3)")]
    [MaxLength(3)]
    public string ShipmentFeeCode { get; set; }

    [Column(TypeName = "varchar(6)")]
    [MaxLength(6)]
    public string ExtraFeeForShipment { get; set; }

    [Column(TypeName = "varchar(2)")]
    [MaxLength(2)]
    public string ContainerizationIndicator { get; set; }

    [Column(TypeName = "varchar(3)")]
    [MaxLength(3)]
    public string UspsElectronicFileVersionNumber { get; set; }

    [Column(TypeName = "varchar(12)")]
    [MaxLength(12)]
    public string TransactionId { get; set; }

    [Column(TypeName = "varchar(8)")]
    [MaxLength(8)]
    public string SoftwareVendorProductVersionNumber { get; set; }

    [Column(TypeName = "varchar(9)")]
    [MaxLength(9)]
    public string FileRecordCount { get; set; }

    [Column(TypeName = "varchar(9)")]
    [MaxLength(9)]
    public string MailerId { get; set; }

    public ICollection<D1Record> D1Records { get; set; } = new List<D1Record>();

    public ICollection<C1Record> C1Records { get; set; } = new List<C1Record>();

}

And here is the entitybase class

public class EntityBase
{
    public DateTime CreatedDate { get; set; }
    public DateTime LastModifiedDate { get; set; }
    public int CreatedByUserId { get; set; }
    public int LastModifiedByUserId { get; set; }
    public bool DeleteFlag { get; set; }

}

I want to create a non-clustered index for ShippingServicesFileId and DeleteFlag in the OnModelCreating method of my DbContext do that it gets picked up when I run add-migration in my Package Manager Console.

Any ideas?

Grimm · Accepted Answer · 2022-01-26 10:12:25Z

14

UPDATE (applies to EF Core >= 5):

Since EF Core 5.0 it is also possible to define a composite index over multiple columns with data annotations:

[Index(nameof([Column1]), nameof([Column2], ...))]
public class ...
{
    ...
}

Use

[Index(nameof([Column1]), nameof([Column2], ..., IsUnique = true))]

to create a unique one.

Note: .ForSqlServerIsClustered() cannot be written as a data annotation (clustered index on a SQL Server). Here you should continue to use the Fluent API (see below).

see https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

UPDATE (applies to EF Core < 5):

You cannot create an index on more than one column with data annotations so you have to use Fluent API (in OnModelCreating):

modeBuilder.Entity<ENTITYCLASS>().HasIndex(x => new {x.PROPERTY1, x.PROPERTY2, ...})

to create a non-clustered index. Use .IsUnique() to create a unique one. Use .ForSqlServerIsClustered() if you want a clustered index on a SQL Server. Optionally you can give it another name with .HasName("...").

edited Jan 26, 2022 at 10:12

answered Jul 23, 2018 at 16:54

Grimm

81111 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gwruck Over a year ago

This is no longer true. Per Microsoft documentation: learn.microsoft.com/en-us/ef/core/modeling/…, it is possible to create a Composite Index across multiple fields using annotations.

Grimm Over a year ago

@gwruck My answer is split for EF Core < 5 and EF Core >= 5. Did you miss that?

gwruck Over a year ago

...sorry, my mistake - must be dislexic. I had in my head that the second point related to the current situation, but the first point you have made is correct for >=5.

Grimm Over a year ago

No problem, I'm glad I didn't miss anything. Thanks for the clarification!

adiga · Accepted Answer · 2019-07-08 07:36:03Z

0

You can pass in the columns as a string array during a migration if that's your preferred method. E.g.

migrationBuilder.CreateIndex("IX_H1Record", "H1Record",new string[] { "ShippingServicesFileId", "DeleteFlag"}, "dbo");

edited Jul 8, 2019 at 7:36

adiga

35.4k9 gold badges65 silver badges88 bronze badges

answered Jul 8, 2019 at 7:32

Will

3773 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to define a multi-field index with Entity Framework Core 2.1

2 Answers 2

UPDATE (applies to EF Core >= 5):

UPDATE (applies to EF Core < 5):

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE (applies to EF Core >= 5):

UPDATE (applies to EF Core < 5):

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related