SQL Reference

You have to make API calls and specify a search based on SQL in order to fetch a collection of objects from the cache. For the above mentioned scenario, you just have to select all products related to required category and it will return all related key or items.

NCache requires all searchable attributes to be indexed before using those indexes.

You can use different operators according to your required query criteria to fetch the desired result set from your cache.

Syntax of the language is explained with the help of examples below:

Basic Query Operators

Operator	Description	Use (Example)
= OR ==	Equals to	SELECT Product WHERE this.ProductID == ?
!= OR <>	Not equals to	SELECT Product WHERE this.ProductID <> ?
<	Less than	SELECT Product WHERE this.ProductID < ?
>	Greater than	SELECT Product WHERE this.ProductID > ?
<=	Less than equal to	SELECT Product WHERE this.ProductID <= ?
>=	Greater than equal to	SELECT Product WHERE this.ProductID >= ?
IN	In a range	SELECT Product this.UnitsAvailable IN (?,?,?,?))
LIKE	Pattern like. Mostly used for wild card base searching. Use * for one or many occurrence of character. Use . for single occurrence of character.	SELECT Product WHERE this.ProductName LIKE ? AND this.Category LIKE ?
NOT LIKE	Not like pattern	SELECT Product WHERE this.ProductID NOT LIKE ?

Logical Query Operators

Operator	Description	Use (Example)
AND	Retrieve merged result set for two or more conditions.	SELECT Product WHERE this.ProductName LIKE ? AND this.Category LIKE ?
OR	Retrieve result set which fulfills any one condition.	SELECT Product WHERE this.ProductName LIKE ? OR this.Category LIKE ?
NOT	Retrieve result set which doesn't match the given criteria.	SELECT Product WHERE this.ProductID NOT LIKE ?

Aggregate Functions

Operator	Description	Use (Example)
SUM	Calculate sum of result set of specified criteria.	SELECT SUM(Product.UnitsAvailable) WHERE this.ProductID <= 10
COUNT	Calculates count of the result set for a specified criteria.	SELECT count(Product) WHERE this.ProductID < ?
AVG	Calculates average of the result set for specified criteria.	SELECT AVG(Product.UnitsAvailable) WHERE this.ProductID < ?
MIN	Returns minimum of the result set for specified criteria.	SELECT MIN(Product.UnitsAvailable) WHERE this.Category = ?
MAX	Returns maximum of the result set for specified criteria.	SELECT MAX(Product.UnitsAvailable) WHERE this.Category = ?

Miscellaneous

Operator	Description	Use (Example)
GROUP BY	Group data based on an aggregate function.	SELECT this.Category, COUNT(Product) WHERE this.ProductID > ? GROUP BY this.Category
ORDER BY	Sort the result set based on criteria in descending or ascending order.	SELECT Product WHERE this.Category = ? ORDER BY this.Category
DateTime.Now	Retrieve result set with respect to current date time.	SELECT Order WHERE this.OrderDate < DateTime.Now
DateTime (any date time compatible string)	Retrieve result set with respect to specified date time.	SELECT Order WHERE this.OrderDate = DateTime(2017, 6, 20)

See Also

Using Locks for Concurrent Updates
SQL Search for Objects
SQL Search for Keys
Using IN Operator
Using Like Operator
Using Group By
Search Cache with LINQ