Skip to main content
All docs
V24.2

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.