Indexes

Thong Nguyen edited this page Aug 3, 2018 · 12 revisions

Introduction

Indexes are defined using the IndexAttribute. The attribute can be put on either a property or a class. Indexes placed on classes must be provided with a single property name (for a single property index) or list of property names (for composite indexes). Indexes placed on properties implicitly are indexes for that property. Indexes placed on multiple properties with the same (IndexAttribute.IndexName) form part of a composite index.

Naming

By default index names are Property_idx or Property1_Property2_idx for composite indexes. You can override the naming convention by using NamingTransformsConfiguration.. Alternatively, you can explicitly define the name of an index for each index by using the IndexAttribute.IndexName property.

The defaults for NamingTransformsConfiiguration are. Note that the sed transforms in the configuration only apply if the user defined index name is an empty string (unconfigured). If the IndexAttribute.IndexName is explicitly set then that value is passed into the sed expression and the defaults will not override that value.

DefaultForeignKeyConstraintName = "s/^\\s*$/fk_$(PERSISTED_TYPENAME:L)_$(PERSISTED_PROPERTYSUFFIXNAMES:L)/";
DefaultIndexConstraintName = "s/^\\s*$/idx_$(PERSISTED_TYPENAME:L)_$(PERSISTED_PROPERTYSUFFIXNAMES:L)/";
DefaultPrimaryKeyConstraintName = "s/^\\s*$/pk_$(PERSISTED_TYPENAME:L)_$(PERSISTED_PROPERTYSUFFIXNAMES:L)/";
DefaultDefaultValueConstraintName = "s/^\\s*$/def_$(PERSISTED_TYPENAME:L)_$(PERSISTED_PROPERTYSUFFIXNAMES:L)/";

Conditions

Conditions for indexes can be defined by using the IndexAttribute.Condition property. The string should be a C# style expression. If the attribute is applied to a property then you can use the value keyword to reference the value of the current property. If the attribute is applied to a class then you must reference the property value with the property name.

[Index(Condition = "Name != null && Age > 10")]
public class [DataAccessObject] Cat { ... }

// or

[Index(Condition = "value != null && Age > 10")]
public virtual string Name { get; set; }

Lowercase

Indexes can be lowercase by setting the IndexAttribute.Lowercase property. If you are declaring an index at the class level then the property name should be suffixed with :Lowercase.

[Index(Lowercase = true)]
public class [DataAccessObject] Cat { ... }

// or

[Index("Name:Lowercase", "Age")]

Include Only

Indexes can have columns that are stored with the index but don't form part of the index. You can do this with the IndexAttribute.IncludeOnly property. If you are declaring an index at the class level then the property name should be suffixed with :IncludeOnly.

[Index(IncludeOnly = true)]
public class [DataAccessObject] Cat { ... }

// or

[Index("Name", "Age", "Nickname:IncludeOnly")]
public virtual string Name { get; set; }

Sort Order

Indexes can have columns that are sorted ascending or descending. You can do this with the IndexAttribute.SortOrder property. If you are declaring an index at the class level then the property name should be suffixed with :Ascending or :Descending.

[Index("Age", SortOrder = SortOrder.Descending)]
public class [DataAccessObject] Cat { ... }

// or

[Index("Name", "Age:Descending", "Nickname")]
public virtual string Name { get; set; }

Code Examples

Each IndexAttribute defines a new index for the property.

public class Cat : DataAccessObject<Guid>
{
    [Index]
    [PersistedMember]
    public virtual string Name { get; set; }

    [Index]
    [PersistedMember]
    public virtual int Age { get; set; }

    [Index]
    [PersistedMember]
    public virtual string Nickname { get; set; }
}

Composite indexes can be created by using multiple Index attributes with the same IndexName. The ordering of columns in the index can be defined by using the CompositeIndex property. The CompositeIndex property is any integer and defines ordering relative to other composite indexes for the same index.

public class Cat : DataAccessObject<Guid>
{
    [PersistedMember]
    [Index(IndexName = "Name_Age_Nickname_idx", Lowercase = true, CompositeIndex = 1)]
    public virtual string Name { get; set; }

    [PersistedMember]
    [Index(IndexName = "Name_Age_Nickname_idx", CompositeIndex = 2)]
    public virtual int Age { get; set; }

    [PersistedMember]
    [Index(IndexName = "Name_Age_Nickname_idx", IncludeOnly = true, CompositeIndex = 3)]
    public virtual string Nickname { get; set; }
}

It is easier to define composite indexes by declaring a single IndexAttribute on the class. The ordering of the columns in the index is implied by the ordering of the property names provided to the attribute constructor.

[Index("Name:Lowercase", "Age", "Nickname:IncludeOnly")]
public class Cat : DataAccessObject<Guid>
{
    [PersistedMember]
    public virtual string Name { get; set; }

    [PersistedMember]
    public virtual int Age { get; set; }

    [PersistedMember]
    public virtual string Nickname { get; set; }
}
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.