// 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`
|===