SQL Reference for NCache

Note

This feature is only available in NCache Enterprise Edition.

NCache offers an SQL Like query syntax with extended searching options. NCache has further refined its query syntax from NCache 5.0 onward with some major improvements. The new query syntax allows you to retrieve specific projections from the cache store. Also, you can now specify * to retrieve the whole data of a type from the cache which further depicts its SQL-Like behavior.

Note

NCache also supports backward compatibility for the older query syntax.

Below mentioned is the standard NCache-supported query syntax which uses GROUP BY and ORDER BY clauses:

SELECT <projections> FROM <type> [WHERE <expression>] [GROUP BY <objectAttributes>] [ORDER BY <orderArguments>]

Projections

You can specify the columns of your choice to be projected against your query. Below-mentioned is a list of NCache supported projections:

• * returns all the indexed attributes, named tags in the form of separate columns
• $VALUE$ returns the object satisfying the criteria in the where clause
• $GROUP$ returns the groups specified with the objects satisfying the criteria in the where clause
• Projection functions are the Aggregate Functions including SUM(Identifier), COUNT(*), MIN(Identifier), MAX(Identifier), AVG(Identifier), SCORE (textsearchexpression) & TERMFREQ (Identifier, stringliteral))
• Identifiers include all the indexed attributes names or namedtags key associated with the objects)

Note

You can use any of the above-mentioned projections in combination except the * one as it can only be specified alone.

Type

Extending our previous SQL-Like query syntax, the Type portion can specify one of the following:

• Fully Qualified Name(FQN) of the indexed type
• $DataType$ (for data structures)
• $Text$ (for System.String type)

Important

NCache requires all searchable attributes to be indexed before using them.

Modern API

Assuming the example of a Product class, the following examples further explain the NCache supported query syntax:

Basic Query Operators

Operator	Description	Use (Example)
= OR ==	Equals to	SELECT * From Product WHERE ProductID == 10
!= OR <>	Not equals to	SELECT * From Product WHERE ProductID <> 8
<	Less than	SELECT ProductName From Product WHERE ProductID < 87
>	Greater than	SELECT ProductName From Product WHERE ProductID > 77
<=	Less than equal to	SELECT UnitsInStock From Product WHERE ProductID <= 68
>=	Greater than equal to	SELECT UnitsInStock From Product WHERE ProductID >= 98
IN	In a range	SELECT * From Product WHERE UnitsInStock IN (10,15,20,25)
NOT IN	Not in a range	SELECT * From Product WHERE UnitsInStock NOT IN (10,15,20,25)
LIKE	Pattern like. Mostly used for wild card base searching. Use * for one or many occurrence of character.	SELECT ProductID From Product WHERE ProductName LIKE ? AND Category LIKE ?
NOT LIKE	Not like pattern	SELECT ProductID From Product WHERE ProductName NOT LIKE ? AND Category NOT LIKE ?

Logical Query Operators

You can combine two different expressions with the OR and AND expressions to further narrow down your search.

Operator	Description	Use (Example)
AND	Retrieve merged result set for two or more conditions.	SELECT ProductID From Product WHERE ProductName = 'Chai' AND Category='Edibles'
OR	Retrieve result set which fulfills any one condition.	SELECT ProductID From Product WHERE ProductName = 'Chai' OR Category = 'Edibles'
NOT	Retrieve result set which doesn't match the given criteria.	SELECT ProductName From Product WHERE ProductID NOT IN (10,15,20)

Miscellaneous

Operator	Description	Use (Example)
GROUP BY	Group data based on an aggregate function.	SELECT Category, Count(*) FROM Product WHERE UnitsInStock < 15 GROUP BY Category
ORDER BY	Sort the result set based on criteria in descending or ascending order.	SELECT * FROM Product WHERE Category = 'Edibles' ORDER BY Category
DateTime.Now	Retrieve result set with respect to current date time.	SELECT * FROM Order WHERE OrderDate < DateTime.Now
DateTime (any date time compatible string)	Retrieve result set with respect to specified date time.	SELECT OrderID FROM Order WHERE OrderDate = DateTime('2000-1-1')"
COUNT	Calculates count of the result set for a specified criteria.	SELECT COUNT(*) FROM Product WHERE UnitsInStock < 20

Note

For backward compatibility reference, the old querying functions along with executable examples are given below.

Legacy API

Note

Legacy API is only available in NCache Enterprise Edition.

Assuming the example of a Product class, the following examples explain the older query syntax:

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

Locking Data For Concurrency Control
SQL Search for Objects
SQL Search for Keys Syntax and Usage
SQL IN Operator Syntax and Usage
SQL Like Operator Syntax and Usage
SQL GROUP BY Syntax and Usage
Search Cache with LINQ