Define SQL Index Programmatically

NCache allows you to query the cache using SQL-like queries similar to those used in a database. However, unlike a database, any searchable object and its fields must be indexed before they can be queried. To support this, NCache enables developers to define SQL indexes programmatically with ease. You can index all public, private, and protected primitive fields and properties. See NCache supported data types for further details.

Tip

Alternatively, you can configure SQL indexes through the NCache Management Center or PowerShell.

Note

A reference type field cannot be indexed.

Prerequisites

NCache provides the flexibility to create indexes through different approaches. You can create indexes for selective fields or the whole class based on your needs. Meanwhile, certain fields can be excluded from indexing when the class is indexed. In addition, indexing support is provided for multilingual clients as well. Specifically, NCache has the following custom fields for defining indexes programmatically:

  1. QueryIndexed: Used for indexing selective fields.

  2. QueryIndexed["indexName"]: Used for indexing a field against a user-provided index name.

  3. QueryIndexable: Used for indexing the whole class.

  4. NonQueryIndexed: Used for excluding fields from indexing when a class is indexed.

Important

It is recommended to avoid indexing unnecessary fields since indexing has memory and performance overhead.

Here we discuss different approaches to create indexes in NCache using these fields.

Selective Indexing

You can create an index for a particular property or field of a class using the QueryIndexed attribute. This attribute is applied at the primitive property or field level to indicate that the marked member should be indexed. All annotated properties or fields are automatically indexed by NCache.

Note

Always use QueryIndexed when you need to index only a few properties or fields of a class since it saves performance and memory costs.

This method is ideal when only a small subset of a class's properties or fields requires indexing. It provides the flexibility to selectively index only those members you choose.

Warning

If you explicitly mark a reference field as QueryIndexed, it throws an exception "Index is not supported for <Ref Type Name>". Indexes are only supported on value types.

The following example shows how to index selective fields using QueryIndexed without indexing the entire class. NCache will automatically create indexes for the fields ID and Name only.

public class Product
{
    [QueryIndexed]
    public int ID { get; set} // explicitly indexed

    [QueryIndexed]
    public string Name { get; set} // explicitly indexed

    public Decimal UnitPrice { get; set } // will not be indexed

    public Customer Customer { get; set; } // will not be indexed
}
Note

To ensure the operation is fail-safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

In the above example, the index name is same as the field name since this is the default behavior when using the QueryIndexed attribute for selective indexing. However, NCache allows you to define custom names for your fields or properties to be indexed through the QueryIndexed ("indexName") annotation. In that case, the user-specified name in the constructor will be used when creating an index.

This feature is particularly useful when developing applications across multiple languages where field names may differ. For instance, an e-commerce application has .NET and Java clients, where the .NET client names a field of the Product class as UnitPrice, while a Java client names the same field as PricePerUnit. For indexing this field, a custom name can be specified in both .NET and Java using the QueryIndexed("price") attribute. The marked field will be indexed with the name price for all clients. Now, different clients can perform the query using price in the WHERE clause or as a projection.

Warning

If a field is indexed using QueryIndexed["indexName"], querying data based on field name will throw an exception "Index not defined for field name".

The following example demonstrates how to index selective fields of a class using the index name of your choice.

public class Product
{
    [QueryIndexed]
    public int ID { get; set} // explicitly indexed with index name ID

    [QueryIndexed]
    public string Name { get; set} // explicitly indexed with index name

    [QueryIndexed("price")]
    public Decimal UnitPrice { get; set } // explicitly indexed 

    public Customer Customer { get; set; } // will not be indexed
}

Class Indexing

You can index your class using the attribute QueryIndexable. It is defined at the class level to indicate that the whole class can be indexed. When you mark a class as QueryIndexable, all public properties and fields will be automatically indexed. If your class has a private field that requires indexing, you must explicitly mark those members with the QueryIndexed attribute.

This approach should only be used when you want to query index either all or most of the properties or fields of a class. Otherwise, it is not encouraged, since there is a chance that it may index unnecessary properties. Eventually, this will degrade the performance of your application due to the underlying memory and performance overhead of indexing.

Warning
  • It is not recommended to use QueryIndexable, unless all or most of the properties or fields require indexing, since indexing has memory and performance costs.
  • Marking a class as QueryIndexable doesn't index private fields. In this case, you'll need to individually mark each private field as QueryIndexable to index it.

The following example demonstrates how to index the fields of a class named Product using the QueryIndexable attribute. This approach will automatically index the ID, Name, and UnitPrice fields. The Category field is explicitly marked as QueryIndexed since it is private. Indexing Customer will be ignored since it is a reference field pointing to the object of the Customer class, and NCache does not support indexing reference fields.

[QueryIndexable]
public class Product
{
    public int ID { get; set} // auto index

    public string Name { get; set} // auto index

    public Decimal UnitPrice { get; set } // auto index

    [QueryIndexed]
    private string Category { get; set } // explicitly marked for indexing

    public Customer Customer { get; set; } // will not be indexed
}

Excluding Fields from Indexing

You can use the NonQueryIndexed attribute to prevent the indexing of unrequired properties or fields when your class is indexed. The attribute NonQueryIndexed is defined at the primitive property or field level to indicate that the property or field should be excluded from indexing when the class itself is marked as QueryIndexable. All the public properties and fields will be automatically indexed. The fields marked with the NonQueryIndexed attribute will be excluded from indexing. You can use a combination of QueryIndexable and NonQueryIndexed when most of the properties require indexing except a few.

The following example shows how to exclude the field UnitPrice from indexing by explicitly marking it as NonQueryIndexed when the class itself is indexed. This will ignore the indexing of the annotated field.

[QueryIndexable]
public class Product
{
    public int ID { get; set} // auto index

    public string Name { get; set} // auto index

    [NonQueryIndexed]
    public Decimal UnitPrice { get; set } // will not be indexed

    public Customer Customer { get; set; } // will not be indexed
}
Note

NCache allows you to suppress any errors associated with creating indexes via the NCacheServer.SuppressIndexNotDefinedException tag in the NCache Service Config file.

Additional Resources

NCache provides a sample application for SQL Queries on GitHub.

See Also

.NET: Alachisoft.NCache.Client namespace.
Java: com.alachisoft.ncache.client namespace.