SQL and LINQ Support in NCache

In real life complex scenarios, your application often needs to fetch data based on certain attributes rather than searching through the primary key. Querying your data using SQL or LINQ makes it easier for you to retrieve data from your database depending upon your custom logic at runtime.

NCache supports SQL and LINQ querying capabilities that let you query your distributed cache the exact way you query your database. For this reason, NCache provides various projections, operations and aggregate functions in SQL and LINQ support to reduce network traffic and enhance performance.

SQL Support in NCache

NCache supports SQL querying mechanism to let you search your cache according to a given criterion and returns the required result set. NCache uses a query language that is very close to the native SQL structured language, which makes it easy for you to query your indexed cached data.

Query methods

NCache uses query interface similar to the ADO.NET interface to support various query methods to search for or delete data from the cache. Here are some of the most important query methods supported in NCache.

ExecuteReader: used to search data and return key-value pairs. While using the ExecuteReader method, you can specify whether you just want the keys to be returned, or keys and their data (either as a whole or in chunks).

string query = "SELECT * FROM FQN.Product WHERE UnitsInStock > ?";
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitsInStock",0);

ICacheReader reader = cache.ExecuteReader(queryCommand);
    
while (reader.Read())
{
    string ID = reader.GetValue<string>("ProductID");
    string Name = reader.GetValue<string>("ProductName");    
}

ExecuteNonQuery: is used to execute delete SQL statements. It returns the number of deleted rows as a result.
ExecuteScalar: is used when a single value needs to be returned. Normally used for count and aggregate.

SQL Projections

NCache allows you to specify columns of your choice to be projected against your SQL query. Mentioned below are the basic, most commonly used projections supported in NCache,

* : when used in a query, it returns the values of all indexed attributes of the specified object defined at the time of configuration.
$VALUE$ : when used in a query, it returns you the key with the entire object that contains indexed as well as the non-indexed attributes.
Aggregate functions: these functions include functions like SUM(), COUNT(), MIN/MAX() etc. You can use aggregate functions with any combination of other provided SQL projections.

string query = "SELECT COUNT(*) FROM FQN.Product WHERE UnitsInStock > ?";

Refer to SQL Search for Object for more information.

SQL Miscellaneous Operators

These operators are used and re-used throughout SQL database for processing and manipulating data. NCache supports various SQL operators that allow you to get the desired result efficiently. These functions are:

GROUPBY: returns the value by grouping all keys that fall under the given criterion.
ORDERBY: returns the value in a sorted form.
DateTime: searches the cache in with respect to particular date or time.

string query = @"SELECT * FROM FQN.Product WHERE UnitsInStock < ? 
                     Group By Category 
                     ORDER BY UnitsInStock DESC";
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitsInStock",50);

SQL Operators in Query

SQL operators are reserved keywords in the WHERE clause to perform logical and comparison operations. NCache provides you support of the following SQL operators to query your data according to custom conditions.

IN: used in searching the cache for all provided attributes. Similarly, you have the NOT IN operator that searches for attributes other than the ones provided in the clause.

string query = "SELECT ProductName, UnitsAvailable From FQN.Product WHERE Category IN (‘Beverages’, ‘Confections’)";

LIKE: used to search for specific pattern in a column. LIKE operator supports two wildcards:
- * : a substitute for zero or more characters in the string.
- ? : a substitute for single character in a string.

Similarly, you have the NOT LIKE operator.

string query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?";

  var queryCommand = new QueryCommand(query);

  queryCommand.Parameters.Add("ProductName", "Cha*");
  queryCommand.Parameters.Add("Category", "?dib*");
  ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);

To get acquainted with how to query cache data, refer to our documentation on SQL Search in Cache.

LINQ Support in NCache

LINQ is a generic .NET query language that allows you to search and filter out data from your data source. LINQ syntax is quite similar to SQL but in functionalities, it provides you with better and more optimized querying methods when querying your NCache servers. It provides the ability to allow more efficient query expressions while catering to syntax checking during code compilation.

NCache seamlessly integrates LINQ to query information within the cache via a LINQ provider. The link provider facilitates execution of LINQ queries over the distributed cache while improving the application’s performance without changing the LINQ object model.

Supported Querying Expressions

NCache supports LINQ language’s basic querying expressions to search cache data. They are:

Lambda expressions: Lambda expressions in a LINQ equation translate logic at runtime so it can pass on the data source conveniently.

IQueryable<Product> products = new NCacheQuery<Product>(cache);
  IQueryable<Product> product = products.Select(p => p).Where(p => p. UnitsInStock > 10);

Query expressions: Query expressions are used to query and transform data from your LINQ-enabled distributed cache servers.

IQueryable<Product> products = new NCacheQuery<Product>(cache);
  IQueryable<Product> product = from prod in products
                 where prod.UnitsInStock > 10
                 select prod;

LINQ Operators

LINQ operators are extension methods that offer query capabilities like projection, aggregation, filtering, and sorting. Among these operators, NCache supports the ones mentioned below:

Projection operator: projects values on the basis of a transform function. The following code snippet shows how to use the select projection operator to search cache data.

IQueryable<Product> product = products.Select(prod => prod);

Restriction operator: restricts the result set to selected data that satisfies a particular condition. This code snippet searches the cache for all products whose units on order are greater than 10.

IQueryable<Product> product = products.Select(prod => prod).Where(prod => prod.UnitsInStock > 10);

Query Operators

NCache supports basic query operators with LINQ querying like:

Equals (==)
Not Equals (!=)
Less Than (<)
Greater Than (>)
Less Than Equal To (<=)
Greater Than Equal To (>=)

Refer to LINQ Query Operators for the usage of these operators.

Logical Operators

NCache supports two logical operators with LINQ expressions; AND (&&) operator and OR (||) operator. These operators are used to combine two or more restriction criteria for the cache search. Follow our guide on LINQ Logical Operators to understand their usage in detail.

Aggregation Operators

NCache supports aggregation operators to allow any type of desired aggregation in LINQ. The operators NCache supports are:

Count
Max
Min
Average
Sum

An example of how to use aggregate functions to search for cache data and count the results is shown below:

int count = products.Select(p => p).Where(prod => prod.UnitsInStock <= 50).Count();

Understand the usage of aggregate operators in NCache from our guide on LINQ Aggregate Operators.

Wildcard Operators

In conjunction with the restriction and aggregation operators, NCache supports multiple wildcards in LINQ expressions. These wildcard operators are:

StartsWith
EndsWith
Contains
Equals

An example of using wildcards with a restriction where operator to search for a specific data set in the cache is shown below:

IQueryable<Product> product = products.Select(p => p).Where(p => p.ProductName.StartsWith("Be"));

Refer to our documentation on LINQ Wildcard Operators to know more.

LINQPad Support

LINQPad is a third party tool that is used to interactively query SQL database using query and lambda expressions as well as write C# codes without any IDE. NCache supports full seamless integration with the LINQPad utility to improve data analysis and application performance.

To process queries in LINQPad, NCache LINQ Provider uses the NCacheContext class that is an implementation of .NET framework’s IQueryable interface.

IQueryable<Product> product = new NCacheContext<Product>("myPartitionedCache");
IQueryable<Product> products = from prod in product
             where prod.UnitsInStock > 50
             select prod;
products.Dump();

Running this script in the LINQPad will create a LINQ object product which is used to query the products with UnitsInStock greater than 50.

To know more on how to use LINQPad to query data in cache, follow our documentation on Query Data in Cache with LINQPad and to configure LINQPad in NCache for yourself, follow the steps provided in Configure LINQPad for NCache.

What to Do Next?

Review NCache Features
Download NCache
Request a Personalized LIVE Demo