SQL Reference for NCache
Your application typically uses cache as a
Hashtable where everything is
stored based on a key and you must have this key to fetch an item. This is like
having a relational database where you can use only the primary key to find
data. This works fine in many situations but a real life complex application
often needs to find data based on attributes other than just the primary key.
And since you are keeping a lot of your data in the cache, it would be very
useful if you could search the cache in this manner as well. A basic example of
this would be an e-commerce website that needs to fetch all products based on
criteria such as category, price, and weight etc. You cannot specify category
based keys for all products and hence cannot store the list of those keys for
For searching purposes, NCache provides you with its own powerful cache searching mechanism where you can fetch the cache keys/data using SQL like query syntax.
NCache allows you to search and delete result sets according to your query criteria. However, it does not support insertion/updating on the basis of any query criteria.
To retrieve the data according to your specified non-primary-key criteria, NCache provides you with an extension of SQL. SQL lets you search the cache against a criteria on which the data is required.
For the scenario highlighted above, you just have to select all the products related to the required category and it will return all related keys or items.
"SELECT Product WHERE this.Category = ?"
You can use a rich set of operators provided by NCache according to your required query criteria to fetch desired result sets from your cache.
Object Query Language is not case sensitive.
The following operator categories are supported in NCache queries:
|Basic Query Operators||= , == , != , <> , < , > , <=, >=, IN, LIKE , NOT LIKE|
|Logical Operators||AND , OR , NOT|
|Aggregate Functions||SUM, COUNT, AVG, MIN, MAX|
|Miscellaneous||DateTime.Now , DateTime ("any date time compatible string")|