> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/connectors/gridgain-8/net-specific/net-linq.md).

# LINQ Provider

Use the GridGain.NET LINQ provider to write distributed SQL queries in C# with LINQ, including projections, joins, and compiled queries.

## 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](https://www.gridgain.com/docs/gridgain8/latest/developers-guide/SQL/indexes) 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:

```csharp
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();
```

{% hint style="danger" %}
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.
{% endhint %}

`AsCacheQueryable` also accepts a `QueryOptions` object to configure query behavior. For example, use the `Label` property to identify the query in system views and long-running query warnings (see [SQL Query Labels](https://www.gridgain.com/docs/gridgain8/latest/perf-troubleshooting-guide/sql-tuning#sql-query-labels)):

```csharp
var queryable = cache.AsCacheQueryable(new QueryOptions { Label = "myQueryLabel" });
```

## 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):

```csharp
// 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.

```csharp
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.

```csharp
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](https://ptupitsyn.github.io/LINQ-vs-SQL-in-Ignite/) for more details on the LINQ provider performance.

## Joins

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

```csharp
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:

```csharp
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:

```csharp
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.

| .NET                            | SQL                            |
| ------------------------------- | ------------------------------ |
| `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`                       |

<sub>*This page is: Copyright © 2026 MariaDB. All rights reserved.*</sub>

{% @marketo/form formId="4316" %}