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)
    • Lucene specific: 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)

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 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