docs/_docs/net-specific/net-linq.adoc - ignite - Git at Google

 // Licensed to the Apache Software Foundation (ASF) under one or more
 // contributor license agreements.  See the NOTICE file distributed with
 // this work for additional information regarding copyright ownership.
 // The ASF licenses this file to You under the Apache License, Version 2.0
 // (the "License"); you may not use this file except in compliance with
 // the License.  You may obtain a copy of the License at
 //
 // http://www.apache.org/licenses/LICENSE-2.0
 //
 // Unless required by applicable law or agreed to in writing, software
 // distributed under the License is distributed on an "AS IS" BASIS,
 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 // See the License for the specific language governing permissions and
 // limitations under the License.
 = Apache Ignite.NET LINQ Provider

 == Overview

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

 == Installation

 * If you use the Ignite *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 link:SQL/indexes[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 Ignite cache by calling the `AsCacheQueryable` method, and use LINQ on it:

 [tabs]
 --
 tab:C#[]
 [source,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();
 ----
 --

 [CAUTION]
 ====
 [discrete]
 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

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

 [tabs]
 --
 tab:C#[]
 [source,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.

 [tabs]
 --
 tab:C#[]
 [source,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.

 [tabs]
 --
 tab:C#[]
 [source,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 https://ptupitsyn.github.io/LINQ-vs-SQL-in-Ignite/[LINQ vs SQL blog post, window=_blank] for more details
 on the LINQ provider performance.

 == Joins

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

 [tabs]
 --
 tab:C#[]
 [source,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:

 [tabs]
 --
 tab:C#[]
 [source,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:

 [tabs]
 --
 tab:C#[]
 [source,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 Ignite LINQ provider.

 [width="100%",cols="1,3",opts="header"]
 |===
 |`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`
 |===
	// Licensed to the Apache Software Foundation (ASF) under one or more
	// contributor license agreements. See the NOTICE file distributed with
	// this work for additional information regarding copyright ownership.
	// The ASF licenses this file to You under the Apache License, Version 2.0
	// (the "License"); you may not use this file except in compliance with
	// the License. You may obtain a copy of the License at
	//
	// http://www.apache.org/licenses/LICENSE-2.0
	//
	// Unless required by applicable law or agreed to in writing, software
	// distributed under the License is distributed on an "AS IS" BASIS,
	// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	// See the License for the specific language governing permissions and
	// limitations under the License.
	= Apache Ignite.NET LINQ Provider

	== Overview

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

	== Installation

	* If you use the Ignite 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 link:SQL/indexes[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 Ignite cache by calling the `AsCacheQueryable` method, and use LINQ on it:

	[tabs]
	--
	tab:C#[]
	[source,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();
	----
	--

	[CAUTION]
	====
	[discrete]
	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

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

	[tabs]
	--
	tab:C#[]
	[source,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.

	[tabs]
	--
	tab:C#[]
	[source,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.

	[tabs]
	--
	tab:C#[]
	[source,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 https://ptupitsyn.github.io/LINQ-vs-SQL-in-Ignite/[LINQ vs SQL blog post, window=_blank] for more details
	on the LINQ provider performance.

	== Joins

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

	[tabs]
	--
	tab:C#[]
	[source,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:

	[tabs]
	--
	tab:C#[]
	[source,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:

	[tabs]
	--
	tab:C#[]
	[source,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 Ignite LINQ provider.

	[width="100%",cols="1,3",opts="header"]
	\|===
	\|`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`
	\|===