SQL Reference for NCache

Note

This feature is only available in NCache Enterprise.

NCache offers an SQL-like query syntax with extended searching options. The new query syntax allows you to retrieve specific projections from the cache-store. Also, you can now specify * in a projection, to retrieve all of the indexed attributes from the cache, which further depicts its SQL-like behavior.

Note

NCache also supports backward compatibility for the older query syntax.

Mentioned below is the standard NCache-supported query syntax, which uses the 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. Mentioned below is a list of NCache-supported projections:

  • *: Returns all of the indexed attributes and NamedTags in the form of separate columns.
  • ColumnName: Returns the column/columns specified with the objects satisfying the query criteria.
  • $VALUE$: Returns the actual object that has been stored inside the cache.
  • $GROUP$: Returns the Groups specified with the objects satisfying the criteria in the WHERE clause.
  • $TAG$: Returns the Tags specified with the objects satisfying the criteria in the WHERE clause.
  • Projection functions: Returns the output of Aggregate Functions including:
    • SUM(Identifier)
    • COUNT(*)
    • MIN(Identifier)
    • MAX(Identifier)
    • AVG(Identifier)
Note

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

Type Name

Extending our previous SQL-like query syntax, the Type attribute 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.

Syntax

The following examples further explain the NCache-supported query syntax with the assumption that users are dealing with a Product class.

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 Price < 87
> Greater than. SELECT ProductName From Product WHERE Price > 77
<= Less than equal to. SELECT UnitsInStock From Product WHERE Price <= 68
>= Greater than equal to. SELECT UnitsInStock From Product WHERE Price >= 98
IS NULL Check if a value is NULL. SELECT ProductName From Product WHERE Description IS NULL
IN In a range. SELECT * From Product WHERE Category IN ('Fruit','Vegetable')
LIKE Mostly used for wild card base searching. SELECT ProductID From Product WHERE ProductName LIKE ?

Logical Query Operators

You can combine two different expressions with the OR and AND expressions to further narrow down your query. You can also combine operators such as NULL, LIKE, IN, etc., with the NOT operator to broaden your search scope. All of these operators are explained below with their respective examples.

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

You can calculate the SUM, COUNT, AVG, MIN, or MAX for a specified query result set using the following functions.

Operator Description Use (Example)
SUM Calculates sum of the result set for a specified criteria. SELECT SUM(Product.UnitsAvailable) From Product WHERE Category = ?
COUNT Calculates count of the result set for a specified criteria. SELECT COUNT(*) From Product WHERE ShippingDate IS NULL
AVG Calculates average of the result set for specified criteria. SELECT AVG(Product.Price) From Product WHERE Category = ?
MIN Returns minimum of the result set for specified criteria. SELECT MIN(Product.Price) From Product WHERE Category = ?
MAX Returns maximum of the result set for specified criteria. SELECT MAX(Product.Price) From Product WHERE Category = ?

Miscellaneous

Below are examples of some other SQL operators supported by NCache demonstrated within the context of the Product class.

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 Price
DateTime.Now Retrieve result set with respect to current date time. SELECT * FROM Order WHERE OrderDate < DateTime.Now
DateTime Refers to any specific date time compatible string and lets you retrieve result set with respect to specified date time. SELECT OrderID FROM Order WHERE OrderDate = DateTime('2000-1-1')

Escape Sequences

While writing the inline queries, you can now use ', \n, \b, and \t escape sequences as characters. The following examples explain this further.

Escape Sequence Use (Example)
' SELECT * from FQN.Class where IndexedAttribute LIKE 'D\\'Angelo'
\n SELECT * from FQN.Class where IndexedAttribute LIKE 'D\\n Angelo'
\b SELECT * from FQN.Class where IndexedAttribute LIKE 'D\\b Angelo'
\t SELECT * from FQN.Class where IndexedAttribute LIKE 'D\\t Angelo'

With LIKE operators in inline queries, you can now use the wildcards * and ? to search as characters with the syntax \\* and \\? respectively. The following examples explain this in detail:

Characters Use (Example)
* SELECT * from FQN.Class where IndexedAttribute LIKE 'Hello\\*World'
? SELECT * from FQN.Class where IndexedAttribute LIKE 'What is your name\\?'

See Also

Locking Data For Concurrency Control
Query Cache with LINQ