What is the need to inform the "Order" parameter in the object of type IndexAnnotation?

5

I am creating indexes for some columns that will be used in a query with Where() . The intention is to make the search faster. Until then I was creating like this:

Property(x => x.Documento)
            .HasColumnAnnotation(IndexAnnotation.AnnotationName,
                                 new IndexAnnotation(new IndexAttribute("ix_documento", 1) { IsUnique = false }));

Property(x => x.Vencimento)
            .HasColumnAnnotation(IndexAnnotation.AnnotationName,
                                 new IndexAnnotation(new IndexAttribute("ix_vencimento", 2) { IsUnique = false }));

But I noticed that the class IndexAttribute has a constructor that only accepts the index name.

The description of the order parameter is:

  

order: A number which will be used to determine column ordering for multi-column indexes.

It would look something like this:

  

A number that will be used to determine column ordering for   multi-column indices

I do not understand this, and if in my case it makes any difference or not.

    
asked by anonymous 03.01.2017 / 14:15

1 answer

7

First of all, let's take the exact description of the IndexAttribute.Order .

  

Gets or sets a number that determines column ordering for multi-column indexes. This will be -1 if no column order has been specified.

     

Notes
  Multi-column indexes are created using the same index name across multiple attributes. The information in these attributes is then merged together to specify the actual database index.

In short, you will use the Order property when you have an Index for multiple columns of the same name.

See this example (with DataAnnotations to be simpler to understand) of Microsoft > to better understand how to use:

public class Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        [Index("IX_BlogIdAndRating", 2)]
        public int Rating { get; set; }
        [Index("IX_BlogIdAndRating", 1)]
        public int BlogId { get; set; }
    }

Note that the properties Rating and BlogId have the same index, where the first column is BlogId , because of the order 1.

For your specific example, there is no need to add the order because the indexes are different. And as stated in the question, you just want to make the query "faster."

If you'd like to learn more about Indexes, see this question.

    
03.01.2017 / 14:31