Skip to main content
All docs
V24.1

Define Database Indexes

  • 8 minutes to read

Indexes allow a database to find data in a table without scanning the entire table. You can create indexes on a single property (column) or a combination of properties (columns).

Note

Attributes described in this topic do not affect existing database schemas, they work only if you create a new database table.

If you have an existing database table, use your database server tools to create indexes.

Index for a Single Column (Property)

The Indexed attribute indicates that a table (or another form of data store) has an index for a decorated property (column).

public class User : XPObject {
    [Indexed]
    public int UserId {
        get { return fUserId; }
        set { SetPropertyValue(nameof(UserId), ref fUserId, value); }
    }
    int fUserId;

    public string UserName {
        get { return fUserName; }
        set { SetPropertyValue(nameof(UserName), ref fUserName, value); }
    }
    string fUserName;
}

Index Uniqueness

You can apply the IndexedAttribute attribute with Unique = true to a persistent property to guarantee the values in the corresponding column are unique:

public class User : XPObject {
    [Indexed(Unique = true)]
    public int UserId {
        get { return fUserId; }
        set { SetPropertyValue(nameof(UserId), ref fUserId, value); }
    }
    int fUserId;

    public string UserName {
        get { return fUserName; }
        set { SetPropertyValue(nameof(UserName), ref fUserName, value); }
    }
    string fUserName;
}

Composite Index

XPO allows you to define a multi-column index in a persistent object.

using DevExpress.Xpo;

public class Person : XPObject {
    // Make a composite index for the LastName, FirstName, and BirthDate columns.
    // The combination of these properties is unique.
    [Indexed("FirstName;BirthDate", Unique=true)]
    public string LastName {
        get { return fLastName; }
        set { SetPropertyValue(nameof(LastName), ref fLastName, value); }
    }
    string fLastName = string.Empty;

    public string FirstName {
        get { return fFirstName; }
        set { SetPropertyValue(nameof(FirstName), ref fFirstName, value); }
    }
    string fFirstName = string.Empty;

    public DateTime BirthDate {
        get { return fBirthDate; }
        set { SetPropertyValue(nameof(BirthDate), ref fBirthDate, value); }
    }
    DateTime fBirthDate;

}

Note

XPO does not support tables with multi-column (compound) keys or indexes in ASE databases. To avoid exceptions when connecting to ASE databases containing these tables, use one-column keys or indexes.

Apply an Attribute to an Object (Create One-Column or Composite Indexes)

To define multiple non-unique database indexes, use IndicesAttribute.

Apply this attribute to a persistent class to specify database indexes to be created in the database table associated with the class. The Indices attribute allows you to use a single attribute declaration to specify multiple non-unique indexes for the current table:

[Indices("Name", "Name;Age", "Age;ChildCount")]
public class Person : XPObject {
    [Size(32)]
    public String Name {
        get { return fName; }
        set { SetPropertyValue(nameof(Name), ref fName, value); }
    }
    String fName;

    [Indexed(Unique = true), Size(64)]
    public String FullName {
        get { return fFullName; }
        set { SetPropertyValue(nameof(FullName), ref fFullName, value); }
    }
    String fFullName;

    public int Age {
        get { return fAge; }
        set { SetPropertyValue(nameof(Age), ref fAge, value); }
    }
    int fAge;

    public int ChildCount {
        get { return fChildCount; }
        set { SetPropertyValue(nameof(ChildCount), ref fChildCount, value); }
    }
    int fChildCount;

}

With this code in place, the database table corresponding to Person has the following indexes.

  1. A non-unique index over the Name column.
  2. A non-unique multi-column index over the Name and Age columns.
  3. A non-unique multi-column index over the Age and ChildCount columns.
  4. A unique index over the FullName column.

Include XPO’s Service Columns into Indexes to Enable Unique Value Constraints

The Indexed attribute can include XPO’s service columns (ObjectType and GCRecord) for the following purposes:

  1. Allow persistent objects of different types have data fields with the same values. The data records remain unique against the class type (stored in the ObjectType column).
  2. Allow a record to have the same field values as the deleted records have. This is helpful when the deferred deletion is enabled: the “deleted” records are not physically deleted and have non empty values in the GCRecord column.

Note

When a single table is created for each class in an inheritance hierarchy, you can add the IndexedAttribute/IndicesAttribute involving the service ObjectType and GCRecord columns only in the base persistent class, because XPO creates these columns only in the base table.

Steps to implement:

  1. If a data property must be unique against the object type, decorate it with the IndexedAttribute attribute and specify “ObjectType” and Unique = true as parameters.
  2. If a data property must be unique without taking into account deleted records, decorate it with the IndexedAttribute attribute taking “GCRecord” and Unique = true as parameters.
using System;
using DevExpress.Xpo;
using System.ComponentModel;

namespace ConsoleApplication1 {
    public class BasePersistentClass : XPObject {
        public BasePersistentClass(Session session) : base(session) { }

        [Indexed("ObjectType", Unique = true)]
        public string UniqueAgainstObjectTypeInBaseClass { get; set; }

        [Indexed("GCRecord", Unique = true)]
        public string UniqueAgainstGCRecordInBaseClass { get; set; }
    }
    public class DerivedPersistentClass : BasePersistentClass {
        public DerivedPersistentClass(Session session) : base(session) { }

        [Indexed("ObjectTypeCopy", Unique = true)]
        public string UniqueAgainstObjectTypeInDerivedClass { get; set; }

        [Indexed("GCRecordCopy", Unique = true)]
        public string UniqueAgainstGCRecordInDerivedClass { get; set; }

        // To add uniqueness on the service columns in the derived class, 
        // you should declare additional *persistent* clone-properties 
        // that will return the value of corresponding source property.
        [Persistent, Browsable(false)]
        protected XPObjectType ObjectTypeCopy {
            get { return Session.GetObjectType(this); }
        }
        [Persistent, Browsable(false)]
        protected int? GCRecordCopy {
            get { return GetPropertyValue<int?>("GCRecord"); }
        }
    }
}

Note

Microsoft Access skips NULL values when checking value uniqueness.