LINQ

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Apache Ignite.NET includes a LINQ provider for cache SQL queries. You can avoid dealing with SQL syntax and write queries directly in C#. Ignite LINQ provider supports all features of ANSI-99 SQL that is used for cache queries: distributed joins, groupings, aggregates, field queries, and more.

Installation

Binary distribution: add a reference to Apache.Ignite.Linq.dll
NuGet: Install-Package Apache.Ignite.Linq

Configuration

SQL indexing has to be configured the same way as for regular SQL queries, see DDL and Schema and Indexes sections.

Usage

Apache.Ignite.Linq.CacheLinqExtensions class is an entry point for LINQ provider.
Obtain a queryable instance over cache by calling AsCacheQueryable method, and use LINQ on it:

ICache<EmployeeKey, Employee> employeeCache = ignite.GetCache<EmployeeKey, Employee>(CacheName);

IQueryable<ICacheEntry<EmployeeKey, Employee>> queryable = cache.AsCacheQueryable();

Employee[] interns = queryable.Where(emp => emp.Value.IsIntern).ToArray();

🚧

You can use LINQ directly on the cache instance, without calling AsCacheQueryable(). However, this will result in LINQ to Objects query that fetches and processes entire cache data set locally, which is very inefficient.

Introspection

Ignite LINQ provider uses ICache.QueryFields underneath. You can examine produced SqlFieldsQuery by casting IQueryable to ICacheQueryable at any point before materializing statements (ToList, ToArray, etc):

// Create query
var query = ignite.GetCache<EmployeeKey, Employee>(CacheName).AsCacheQueryable().Where(emp => emp.Value.IsIntern);

// Cast to ICacheQueryable
var cacheQueryable = (ICacheQueryable) query;

// Get resulting fields query
SqlFieldsQuery fieldsQuery = cacheQueryable.GetFieldsQuery();

// Examine generated SQL
Console.WriteLine(fieldsQuery.Sql);

// Output: select _T0._key, _T0._val from "persons".Person as _T0 where _T0.IsIntern

Projections

Simple Where queries operate on ICacheEntry objects. You can select Key, Value, or any of the Key and Value fields separately. Multiple fields can be selected using anonymous types.

var query = ignite.GetCache<EmployeeKey, Employee>(CacheName).AsCacheQueryable().Where(emp => emp.Value.IsIntern);

IQueryable<EmployeeKey> keys = query.Select(emp => emp.Key);

IQueryable<Employee> values = query.Select(emp => emp.Value);

IQueryable<string> names = values.Select(emp => emp.Name);

var custom = query.Select(emp => new {Id = emp.Key, Name = emp.Value.Name, Age = emp.Value.Age});

Compiled Queries

LINQ provider causes certain overhead caused by expression parsing and SQL generation. You may want to eliminate this overhead for frequently used queries.

Apache.Ignite.Linq.CompiledQuery class provides compilation of queries for reuse. Call the Compile method to create a new delegate to represent the compiled query. All query parameters should be in the delegate parameters.

var queryable = ignite.GetCache<EmployeeKey, Employee>(CacheName).AsCacheQueryable();

// Regular query
var persons = queryable.Where(emp => emp.Value.Age > 21);
var result = persons.ToArray();

// Corresponding compiled query
var compiledQuery = CompiledQuery.Compile((int age) => queryable.Where(emp => emp.Value.Age > age));
IQueryCursor<ICacheEntry<EmployeeKey, Employee>> cursor = compiledQuery(21);
result = cursor.ToArray();

More details on LINQ performance: LINQ vs SQL blog post

Joins

Same-cache and cross-cache joins are possible. Same-cache joins are used when there are multiple types of data in the same cache. It is recommended to create a separate cache for each data type.

var persons = ignite.GetCache<int, Person>("personCache").AsCacheQueryable();
var orgs = ignite.GetCache<int, Organization>("orgCache").AsCacheQueryable();

// SQL join on Person and Organization to find persons working for Apache
var qry = from person in persons
  				from org in orgs
  				where person.Value.OrgId == org.Value.Id && org.Value.Name == "Apache"
  				select person

foreach (var cacheEntry in qry)
    Console.WriteLine(cacheEntry.Value);

// Same query with method syntax
qry = persons.Join(orgs, person => person.Value.OrgId, org => org.Value.Id, (person, org) => new {person, org}).Where(p => p.org.Name == "Apache").Select(p => p.person);

Contains

ICollection.Contains is supported, which is useful when we want to retrieve data by a set of ids, for example:

var persons = ignite.GetCache<int, Person>("personCache").AsCacheQueryable();
var ids = new int[] {1, 20, 56};

var personsByIds = persons.Where(p => ids.Contains(p.Value.Id));

This translates to ... where Id IN (?, ?, ?).
However, IN does not use indexes (see Performance and Debugging). Also this cannot be used in compiled queries because of variable argument number. Better alternative is to use Join on the ids collection:

var persons = ignite.GetCache<int, Person>("personCache").AsCacheQueryable();
var ids = new int[] {1, 20, 56};

var personsByIds = persons.Join(ids, 
                                person => person.Value.Id, 
                                id => id, 
                                (person, id) => person);

This translates to a temp table join: select _T0._KEY, _T0._VAL from "person".Person as _T0 inner join table (F0 int = ?) _T1 on (_T1.F0 = _T0.ID), and has a single array parameter, so the plan can be cached properly, and compiled queries are also allowed.

Supported SQL Functions

Below is a list of .NET functions and their SQL equivalents that are supported by Ignite LINQ provider.