Defining Indexes

This topic provides examples of how to configure indexes by using the Fluent Mapping API. The Fluent Mapping API exposes the HasIndex extension method from the Telerik.OpenAccess.Metadata.Fluent.Advanced namespace for index configuration.

In order to use the HasIndex extension method, you have to use/import the Telerik.OpenAccess.Metadata.Fluent.Advanced namespace.

The HasIndex extension method can be used only with explicitly mapped properties.

The HasIndex extension method returns a MetaIndexConfiguration object, which exposes the following methods:

IsUnique - specifies that the defined index is unique.
IsNotUnique - specifies that the defined index is not unique.
IsClustered - specifies that the defined index is clustered.
IsNotClustered - specifies that the defined index is not clustered.
WithName - specifies the name of the index. You have to set a name for the index. Otherwise, when you create/update your database the creation of the index will fail.
WithMember - adds a member to the index definition. The corresponding column is then used in the index definition. Usually, you will use this method to specify additional index options (e.g. sort order).
WithColumn - adds a column to the index definition. Usually, you will use this method to specify additional index options (e.g. sort order).

Indexes are usually created based on their configuration. However, there are two backends that are treated different - Firebird and MySql. Firebird does not allow the creation of mixed asc/desc column setups. An index must be created with either asc columns or desc columns, since the whole index is created as asc or desc. Telerik Data Access will throw an exception when such a setup is detected. MySql does accept but not use asc/desc configurations up to version 5.6 (which is the latest during the authoring of this topic). Therefore because there is no possibility to detect the asc/desc setup, Telerik Data Access will ignore the asc/desc configuration for schema diff calculations. An exception is thrown if a mixed asc/desc column setup is detected.

The next several sections illustrate how to configure indexes:

Setting Single Column Index

The following example demonstrates how to create a new index on the Price column. You have to set a name for the index. Otherwise, when you create/update your database the creation of the index will fail. In this example, the name of the index is IX_Products_Price.

productConfiguration.HasIndex(x => x.Price)
   .IsUnique()
   .IsClustered()
   .WithName("IX_Products_Price");

productConfiguration.HasIndex(Function(x) x.Price).
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price")

The image below shows the created index on the server.

Setting Multiple Column Index

The second example in this topic shows you how to create a new index on multiple columns (Price and ProductName).

productConfiguration.HasIndex(x => new {
   x.Price,
   x.ProductName
}).IsUnique()
.IsClustered()
.WithName("IX_Products_Price_ProductName");

productConfiguration.
    HasIndex(Function(x) New With {Key x.Price, Key x.ProductName}).
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price_ProductName")

Specifying Sort Order

You could set the sort order by using either the WithMember or the WithColumn method in conjunction with the Ascending/Descending method.

productConfiguration
   .HasIndex()
   .WithMember(x => x.Price)
   .Ascending()
   .IsUnique()
   .IsClustered()
   .WithName("IX_Products_Price");

productConfiguration.
    HasIndex().
    WithMember(Function(x) x.Price).
    Ascending().
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price")

The HasIndex Method Can Be Used Only With Explicitly Mapped Properties

The HasIndex extension method can be used only with explicitly mapped properties. There is known limitation with the HasIndex method. If you use the HasIndex method, the properties used in the lambda expression have to be explicitly mapped. So, in case your class is mapped by using the default mapping, you need to use the ToColumn method with a not null and not empty string.

The following code snippet demonstrates a default mapped class.

public class DataMetadataSource : FluentMetadataSource
{
   protected override IList<MappingConfiguration> PrepareMapping()
   {
       List<MappingConfiguration> configurations = new List<MappingConfiguration>();

       MappingConfiguration<Product> productConfiguration = new MappingConfiguration<Product>();
       productConfiguration.MapType().ToTable("Products");
       productConfiguration.HasProperty(x => x.ID).IsIdentity(KeyGenerator.Autoinc);

       configurations.Add(productConfiguration);
       return configurations;
   }
}

Public Class DataMetadataSource
 Inherits FluentMetadataSource
 Protected Overrides Function PrepareMapping() As IList(Of MappingConfiguration)
  Dim configurations As New List(Of MappingConfiguration)()

  Dim productConfiguration As New MappingConfiguration(Of Product)()
  productConfiguration.MapType().ToTable("Products")
  productConfiguration.HasProperty(Function(x) x.ID).IsIdentity(KeyGenerator.Autoinc)

  configurations.Add(productConfiguration)
  Return configurations
 End Function
End Class

If you want to keep the default mapping and you want to define indexes, you could use the following approach:

productConfiguration.HasProperty(x => x.Price).ToColumn("Price");
productConfiguration.HasIndex(x => x.Price).IsUnique().IsClustered().WithName("IX_Products_Price");

productConfiguration.HasProperty(Function(x) x.Price).ToColumn("Price")
productConfiguration.HasIndex(Function(x) x.Price).IsUnique().IsClustered().WithName("IX_Products_Price")

Creating Index by Using ColumnName

You can create a new index by passing the column name as a string.

In order to use the WithMember method that accepts a string parameter you need use/import the Telerik.OpenAccess.Metadata.Fluent.Artificial namespace.

productConfiguration.HasIndex()
   .IsUnique()
   .IsClustered()
   .WithName( "IX_Products_Price" )
   .WithMember( "Price" );

productConfiguration.
    HasIndex().
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price").
    WithMember("Price")