Defining Indexes

This topic provides examples of how to configure indexes on artificial types. 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 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
• Setting Multiple Column Index
• Specifying Sort Order

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().
   IsUnique().
   IsClustered().
   WithName("IX_Products_Price").
   WithColumn("Price");

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

The image below shows the created index on the server.

Setting Multiple Column Index

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

productConfiguration.
   HasIndex().
   IsUnique().
   IsClustered().
   WithName("IX_Products_Price_ProductName").
   WithColumn("Price").
   Ascending().
   WithColumn("Name").
   Descending();

productConfiguration.
    HasIndex().
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price_ProductName").
    WithColumn("Price").
    Ascending().
    WithColumn("Name").
    Descending()

Specifying Sort Order

You could specify the sort order by using the Ascending and Descending methods.

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

productConfiguration.
    HasIndex().
    IsUnique().
    IsClustered().
    WithName("IX_Products_Price").
    WithColumn("Price").
    Ascending()