GridGain Developers Hub

GridGain.NET LINQ Provider

Overview

GridGain.NET includes a LINQ provider that is integrated with GridGain SQL APIs. You can avoid dealing with SQL syntax directly and write queries in C# with LINQ. The GridGain LINQ provider supports all features of ANSI-99 SQL including distributed joins, groupings, aggregates, sorting, and more.

Installation

  • If you use the GridGain binary distribution: add a reference to Apache.Ignite.Linq.dll

  • If you use NuGet: Install-Package Apache.Ignite.Linq

Configuration

SQL indexes need to be configured the same way as for regular SQL queries, see Defining Indexes section for details.

Usage

Apache.Ignite.Linq.CacheLinqExtensions class is an entry point for the LINQ provider. Obtain a queryable instance over an GridGain cache by calling the 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();

Introspection

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

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

The Apache.Ignite.Linq.CompiledQuery class supports queries compilation. 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();

Refer to the LINQ vs SQL blog post for more details on the LINQ provider performance.

Joins

The LINQ provider support JOINs that span several caches/tables and nodes.

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 query translates into the …​ where Id IN (?, ?, ?) command. However, keep in mind, that this form 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 LINQ query 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 the GridGain LINQ provider.

String.Length LENGTH

String.ToLower

LOWER

String.ToUpper

UPPER

String.StartsWith("foo")

LIKE 'foo%'

String.EndsWith("foo")

LIKE '%foo'

String.Contains("foo")

LIKE '%foo%'

String.IndexOf("abc")

INSTR(MyField, 'abc') - 1

String.IndexOf("abc", 3)

INSTR(MyField, 'abc', 3) - 1

String.Substring("abc", 4)

SUBSTRING(MyField, 4 + 1)

String.Substring("abc", 4, 7)

SUBSTRING(MyField, 4 + 1, 7)

String.Trim()

TRIM

String.TrimStart()

LTRIM

String.TrimEnd()

RTRIM

String.Trim('x')

TRIM(MyField, 'x')

String.TrimStart('x')

LTRIM(MyField, 'x')

String.TrimEnd('x')

RTRIM(MyField, 'x')

String.Replace

REPLACE

String.PadLeft

LPAD

String.PadRight

RPAD

Regex.Replace

REGEXP_REPLACE

Regex.IsMatch

REGEXP_LIKE

Math.Abs

ABS

Math.Acos

ACOS

Math.Asin

ASIN

Math.Atan

ATAN

Math.Atan2

ATAN2

Math.Ceiling

CEILING

Math.Cos

COS

Math.Cosh

COSH

Math.Exp

EXP

Math.Floor

FLOOR

Math.Log

LOG

Math.Log10

LOG10

Math.Pow

POWER

Math.Round

ROUND

Math.Sign

SIGN

Math.Sin

SIN

Math.Sinh

SINH

Math.Sqrt

SQRT

Math.Tan

TAN

Math.Tanh

TANH

Math.Truncate

TRUNCATE

DateTime.Year

YEAR

DateTime.Month

MONTH

DateTime.Day

DAY_OF_MONTH

DateTime.DayOfYear

DAY_OF_YEAR

DateTime.DayOfWeek

DAY_OF_WEEK - 1

DateTime.Hour

HOUR

DateTime.Minute

MINUTE

DateTime.Second

SECOND

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 20, 2024