Define SQL Index Programmatically

NCache allows querying the cache based on SQL queries like a database. However, unlike a database, any searchable object and its fields in NCache need to be indexed first. In this regard, NCache enables developers to define SQL indexes programmatically with ease. It lets you 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 to Define SQL Indexes Programmatically

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. It is defined at the primitive property or field level to indicate that the marked property or field can be indexed. The annotated properties or fields are automatically indexed.

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 approach can be used to create indexes when relatively few properties or fields of your class require indexing. It provides the flexibility to index selective fields, where you can handpick the desired properties or fields only.

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 whole 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 example above, the index name is the same as the field name since this is the default behavior while 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, while creating an index.

This can be useful when you are developing an application code across different languages, and the field names may vary. 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 } // will not be 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 the public properties and fields will be automatically indexed. If your class has a private field that requires indexing, then you need to explicitly mark it using the QueryIndexed attribute for creating its index.

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 will 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 indexing the fields of a class named Product using the QueryIndexable, attribute. This will index the ID, Name, and UnitPrice fields. The field Category 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, while NCache does not allow 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 files.

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.