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.
- A non-unique index over the Name column.
- A non-unique multi-column index over the Name and Age columns.
- A non-unique multi-column index over the Age and ChildCount columns.
- 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:
- 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). - 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 IndexedObject
and GCRecord
columns only in the base persistent class, because XPO creates these columns only in the base table.
Steps to implement:
- 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. - 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.