CODEWITHSUNDEEP
c#sql-server.net-coreentity-framework-coreasp.net-core-2.0
whiskytangofoxtrot
whiskytangofoxtrot

Reputation: 987

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

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?

Upvotes: 4

Views: 6292

Answers (2)

Grimm
Grimm

Reputation: 791

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("...").

Upvotes: 12

Will
Will

Reputation: 377

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");

Upvotes: 0

Related Questions