// 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.
= .NET LINQ Queries

Ignite .NET client provides LINQ support that is integrated with Ignite SQL APIs. You can avoid working with SQL syntax directly and write queries in C# with LINQ. C# LINQ expressions are then translated into Ignite-specific SQL. For example, the following two snippets achieve the same result:

[tabs]
--
tab:LINQ[]
[source, csharp]
----
var table = await Client.Tables.GetTableAsync("TBL1");
IQueryable<Poco> query = table!.GetRecordView<Poco>().AsQueryable()
    .Where(x => x.Key > 3)
    .OrderBy(x => x.Key);
List<Poco> queryResults = await query.ToListAsync();
----

tab:SQL[]
[source, csharp]
----
var query = "select KEY, VAL from PUBLIC.TBL1 where (KEY > ?) order by KEY asc";
await using IResultSet<IIgniteTuple> resultSet = await Client.Sql.
    ExecuteAsync(transaction: null, query, 3);
var queryResults = new List<Poco>();
await foreach (IIgniteTuple row in resultSet)
{
    queryResults.Add(new Poco { Key = (long)row[0]!, Val = (string?)row[1] });
}
----
--

LINQ has the following advantages over SQL:

* Queries are strongly typed and checked at compilation;
* It is easier to write and maintain with IDE support (auto-completion, navigation, find usages);
* LINQ is refactoring-friendly: rename a column and all queries are updated at once;
* Ignite-specific SQL knowledge is not required, and most C# developers are already familiar with LINQ;
* LINQ is safe against SQL injections;
* Results are mapped to types naturally.

In real-world scenarios the performance of Ignite LINQ queries is on par with equivalent SQL queries.
However, a small overhead still exists (due to query translation), and your mileage may vary depending on the query complexity, so it is recommended to measure the performance of your queries.

== Getting Started With LINQ

Here is how you can create a simple table in Ignite:

1. Create a table:
+
[source, csharp]
----
await Client.Sql.ExecuteAsync(
    null, @"CREATE TABLE PUBLIC.PERSON (NAME VARCHAR PRIMARY KEY, AGE INT)");
----
+
2. Define the classes (or records) that represent tables:
* Member names should match column names (case-insensitive).
* If a column name is not a valid C# identifier, use `[Column("name")]` attribute to specify the name.
+
[source, csharp]
----
public record Person(string Name, int Age, string Address, string Status);
----
+
3. Obtain a table reference:
+
[source, csharp]
----
ITable table = await Client.Tables.GetTableAsync("PERSON");
----
+
4. Use the `GetRecordView<T>()` method to get a typed view of the table:
+
[source, csharp]
----
IRecordView<Person> view = table.GetRecordView<Person>();
----
+
5. Use `AsQueryable()` to perform LINQ queries on `IRecordView<T>`.
+
[source, csharp]
----
List<string> names = await view.AsQueryable()
    .Where(x => x.Age > 30)
    .Select(x => x.Name)
    .ToListAsync();
----

== Using LINQ

=== Inspecting Generated SQL

Viewing generated SQL is useful for debugging and performance tuning. There are two ways to do it:

* `IgniteQueryableExtensions.ToQueryString()` extension method:
+
[source, csharp]
----
IQueryable<Person> query = table.GetRecordView<Person>()
    .AsQueryable()
    .Where(x => x.Age > 30);
string sql = query.ToQueryString();
----
+
* Debug logging:
+
[source, csharp]
----
var cfg = new IgniteClientConfiguration
{
    Logger = new ConsoleLogger { MinLevel = LogLevel.Debug },
    ...
};
using var client = IgniteClient.StartAsync(cfg);
...
----

All generated SQL will be logged with `Debug` level to the specified logger.

=== Transactions

Transaction can be passed to the LINQ provider by using the `AsQueryeable` parameter:

[source, csharp]
----
await using var tx = await client.Transactions.BeginAsync();
var view = (await client.Tables.GetTableAsync("person"))!.GetRecordView<Person>();
pocoView.AsQueryable(tx)...;
----

=== Custom Query Options

Custom query options (timeout, page size) can be specified by using the second `AsQueryable` parameter with `QueryableOptions`:

[source, csharp]
----
var options = new QueryableOptions
{
    PageSize = 512,
    Timeout = TimeSpan.FromSeconds(30)
};
table.GetRecordView<Person>().AsQueryable(options: options)...;
----

=== Result Materialization

Materialization is the process of converting query results (`IQueryable<T>`) into an object or a collection of objects.

LINQ is lazy. Nothing happens (no network calls, no SQL translation) until the query is materialized.
For example, the following code only constructs an expression, but does not execute anything:

[source, csharp]
----
IQueryable<Person> query = table!.GetRecordView<Person>().AsQueryable()
    .Where(x => x.Key > 3)
    .OrderBy(x => x.Key);
----

Query execution and materialization can be triggered in multiple ways:

==== Iteration

You can iterate through query results by using `foreach` statement, or asynchronously by using the  `AsAsyncEnumerable` method:

[source, csharp]
----
foreach (var person in query) { ... }
await foreach (var person in query.AsAsyncEnumerable()) { ... }
----

==== Converting to Collections

You can convert queries to collections by using the `ToList` and `ToDictionary` methods, or `ToListAsync` and `ToDictionaryAsync` methods to do it asynchronously:

[tabs]
--
tab:Synchronous[]
[source, csharp]
----
List<Person> list = query.ToList();
Dictionary<string, int> dict = query.ToDictionary(x => x.Name, x => x.Age);
----

tab:Asynchronous[]
[source, csharp]
----
List<Person> list = await query.ToListAsync();
Dictionary<string, int> dict = await query.
    ToDictionaryAsync(x => x.Name, x => x.Age);
----
--




==== Ignite-specific IResultSet

Underlying `IResultSet` can be obtained by using the `IgniteQueryableExtensions.ToResultSetAsync()` extension method:

[source, csharp]
----
await using IResultSet<Person> resultSet = await query.ToResultSetAsync();
Console.WriteLine(resultSet.Metadata);
var rows = resultSet.CollectAsync(...);
----

Obtaining `IResultSet` can be useful for access to metadata and `CollectAsync` method, which provides more control over result materialization.

== Supported LINQ Features

=== Projection

Projection is the process of converting query results into a different type.
Among other things, projections are used to select a subset of columns.

For example, `Person` table may have many columns, but we only need `Name` and `Age`.

* First, create a projection class:
+
[source, csharp]
----
public record PersonInfo(string Name, int Age);
----
+
* Then, use `Select` to project query results:
+
[source, csharp]
----
List<PersonInfo> result = query
    .Select(x => new PersonInfo(x.Name, x.Age))
    .ToList();
----

Resulting SQL will select only those two columns, avoiding overfetching
(a common issue that happens when ORM-generated query includes all table columns, but only a few of them are needed by the business logic).

Ignite also supports anonymous type projections:

[source, csharp]
----
var result = query.Select(x => new { x.Name, x.Age }).ToList();
----

=== Inner Joins

Use the standard `Join` method to perform joins on other tables:

[source, csharp]
----
var customerQuery = customerTable.GetRecordView<Customer>().AsQueryable();
var orderQuery = orderTable.GetRecordView<Order>().AsQueryable();
var ordersByCustomer = customerQuery
    .Join(orderQuery,
        cust => cust.Id,
        order => order.CustId,
        (cust, order) => new { cust.Name, order.Amount })
    .ToList();
----

=== Outer Joins

Outer joins are supported through the `DefaultIfEmpty` method.
For example, not every book in a library is borrowed by a student, so a left outer join is used to retrieve all books and their current borrowers (if any):

[source, csharp]
----
var bookQuery = bookTable.GetRecordView<Book>().AsQueryable();
var studentQuery = studentTable.GetRecordView<Student>().AsQueryable();
var booksWithStudents = bookQuery
    .Join(studentQuery.DefaultIfEmpty(),
        book => book.StudentId,
        student => student.Id,
        (book, student) => new { book.Title, student.Name })
    .ToList();
----

=== Grouping

Grouping is supported through `GroupBy` method. This is equivalent to SQL GROUP BY operator. You can get both single and multiple columns in your queries. When working with multiple columns, use anonymous type:


[tabs]
--
tab:Single Column[]
[source, csharp]
----
var bookCountByAuthor = bookTable.GetRecordView<Book>().AsQueryable()
    .GroupBy(book => book.Author)
    .Select(grp => new { Author = grp.Key, Count = x.Count() })
    .ToList();
----

tab:Multiple Columns[]
[source, csharp]
----
var bookCountByAuthorAndYear = bookTable.GetRecordView<Book>().AsQueryable()
    .GroupBy(book => new { book.Author, book.Year })
    .Select(grp => new { Author = grp.Key.Author,
                                  Year = grp.Key.Year,
                                  Count = x.Count() })
    .ToList();
----
--

Aggregate functions `Count`, `Sum`, `Min`, `Max`, `Average` can be used with groupings.

=== Ordering

`OrderBy`, `OrderByDescending`, `ThenBy`, `ThenByDescending` are supported. You can combine them to order by multiple columns:

[source, csharp]
----
var booksOrderedByAuthorAndYear = bookTable.GetRecordView<Book>().AsQueryable()
    .OrderBy(book => book.Author)
    .ThenByDescending(book => book.Year)
    .ToList();
----

=== Union, Intersect, Except

Multiple result sets can be combined by using the `Union`, `Intersect`, `Except` methods. For example:

[source, csharp]
----
IQueryable<string> employeeEmails = employeeTable
    .GetRecordView<Employee>().AsQueryable()
    .Select(x => x.Email);

IQueryable<string> customerEmails = customerTable
    .GetRecordView<Customer>().AsQueryable()
    .Select(x => x.Email);

List<string> allEmails = employeeEmails.Union(customerEmails)
    .OrderBy(x => x)
    .ToList();

List<string> employeesThatAreCustomers = employeeEmails
    .Intersect(customerEmails).ToList();
----

=== Aggregate Functions

Below is a list of .NET aggregate functions and their SQL equivalents that are supported in Ignite:

[cols="30%,30%,30%", width="70%"]
|===
|LINQ synchronous method |LINQ asynchronous method  | SQL Operator
|First|FirstAsync|FIRST
|FirstOrDefault|FirstOrDefaultAsync|FIRST ... LIMIT 1
|Single|SingleAsync|FIRST
|SingleOrDefault|SingleOrDefaultAsync|FIRST ... LIMIT 2
|Max|MaxAsync|MAX
|Min|MinAsync|MIN
|Average|AverageAsync|AVG
|Sum|SumAsync|SUM
|Count|CountAsync|COUNT
|LongCount|LongCountAsync|COUNT
|Any|AnyAsync|ANY
|All|AllAsync|ALL
|===

Here are examples of how you can use these methods:

[tabs]
--
tab:Synchronous[]
[source, csharp]
----
Person first = query.First();
Person? firstOrDefault = query.FirstOrDefault();
Person single = query.Single();
Person? singleOrDefault = query.SingleOrDefault();
int maxAge = query.Max(x => x.Age);
int minAge = query.Min(x => x.Age);
int avgAge = query.Average(x => x.Age);
int sumAge = query.Sum(x => x.Age);
int count = query.Count();
long longCount = query.LongCount();
bool any = query.Any(x => x.Age > 30);
bool all = query.All(x => x.Age > 30);
----

tab:Asynchronous[]
[source, csharp]
----
Person first = await query.FirstAsync();
Person? firstOrDefault = await query.FirstOrDefaultAsync();
Person single = await query.SingleAsync();
Person? singleOrDefault = await query.SingleOrDefaultAsync();
int maxAge = await query.MaxAsync(x => x.Age);
int minAge = await query.MinAsync(x => x.Age);
int avgAge = await query.AverageAsync(x => x.Age);
int sumAge = await query.SumAsync(x => x.Age);
int count = await query.CountAsync();
long longCount = await query.LongCountAsync();
bool any = await query.AnyAsync(x => x.Age > 30);
bool all = await query.AllAsync(x => x.Age > 30);
----
--

=== Math Functions

The following `Math` functions are supported (will be translated to SQL equivalents):
`Abs`, `Cos`, `Cosh`, `Acos`, `Sin`, `Sinh`, `Asin`, `Tan`, `Tanh`, `Atan`, `Ceiling`, `Floor`,
`Exp`, `Log`, `Log10`, `Pow`, `Round`, `Sign`, `Sqrt`, `Truncate`.

The following `Math` functions are NOT supported (no equivalent in Ignite SQL engine):
`Acosh`, `Asinh`, `Atanh`, `Atan2`, `Log2`, `Log(x, y)`.

Here is the example of how you can use math functions:

[source, csharp]
----
var triangles = table.GetRecordView<Triangle>().AsQueryable()
    .Select(t => new {
            Hypotenuse,
            Opposite = t.Hypotenuse * Math.Sin(t.Angle),
            Adjacent = t.Hypotenuse * Math.Cos(t.Angle)
        })
    .ToList();
----

=== String Functions

The following string functions are supported: `string.Compare(string)`, `string.Compare(string, bool ignoreCase)`, concatenation `s1 + s2 + s3`, `ToUpper`, `ToLower`,
`Substring(start)`, `Substring(start, len)`,
`Trim`, `Trim(char)`, `TrimStart`, `TrimStart(char)`, `TrimEnd`, `TrimEnd(char)`,
`Contains`, `StartsWith`, `EndsWith`, `IndexOf`, `Length`, `Replace`.

Here is the example of how you can use string functions:

[source, csharp]
----
List<string> fullNames = table.GetRecordView<Person>().AsQueryable()
    .Where(p => p.FirstName.StartsWith("Jo"))
    .Select(p => new {
        FullName = p.FirstName.ToUpper() +
        " " +
        p.LastName.ToLower() })
    .ToList();
----

=== Regular Expressions

`Regex.Replace` is translated to `regexp_replace` function. Here is how you can use regular expressions in your code:

[source, csharp]
----
List<string> addresses = table.GetRecordView<Person>().AsQueryable()
    .Select(p => new { Address = Regex.Replace(p.Address, @"(\d+)", "[$1]")
    .ToList();
----

NOTE: Regular expression engine within SQL may behave differently from .NET engine.

=== DML (Bulk Update and Delete)

Bulk update and delete with optional conditions are supported through `ExecuteUpdateAsync` and `ExecuteDeleteAsync` extensions methods on `IQueryable<T>`:

[source, csharp]
----
var orders = orderTable.GetRecordView<Order>().AsQueryable();
await orders.Where(x => x.Amount == 0).ExecuteDeleteAsync();
----

Update statement can set properties to constant values or to an expression based on other properties of the same row:

[source, csharp]
----
var orders = orderTable.GetRecordView<Order>().AsQueryable();
await orders
    .Where(x => x.CustomerId == customerId)
    .ExecuteUpdateAsync(
        order => order.SetProperty(x => x.Discount, 0.1m)
                      .SetProperty(x => x.Note, x => x.Note +
                            " Happy birthday, " +
                            x.CustomerName));
----

Resulting SQL:

[source, csharp]
----
update PUBLIC.tbl1 as _T0
set NOTE = concat(concat(_T0.NOTE, ?), _T0.CUSTOMERNAME), DISCOUNT = ?
where (_T0.CUSTOMERID IS NOT DISTINCT FROM ?)
----

=== Composing Queries

`IQueryable<T>` expressions can be composed dynamically. A common use case is to compose a query based on user input.
For example, optional filters on different columns can be applied to a query:

[source, csharp]
----
public List<Book> GetBooks(string? author, int? year)
{
    IQueryable<Book> query = bookTable.GetRecordView<Book>().AsQueryable();
    if (!string.IsNullOrEmpty(author))
        query = query.Where(x => x.Author == author);

    if (year != null)
        query = query.Where(x => x.Year == year);
    return query.ToList();
}
----

=== Column Name Mapping

Unless custom mapping is provided with `[Column]`, LINQ provider will use property or field names as column names,
using unquoted identifiers, which are case-insensitive.

[tabs]
--
tab:C#[]
[source, csharp]
----
bookTable.GetRecordView<Book>().AsQueryable().Select(x => x.Author).ToList();
----

tab:Resulting SQL[]
[source, csharp]
----
select _T0.AUTHOR from PUBLIC.books as _T0
----
--

To use quoted identifiers, or to map column names to different property names, use `[Column]` attribute:

[tabs]
--
tab:C#[]
[source, csharp]
----
public class Book
{
    [Column("book_author")]
    public string Author { get; set; }
}
// Or a record:
public record Book([property: Column("book_author")] string Author);
----

tab:Resulting SQL[]
[source, sql]
----
SELECT _T0."book_author" FROM PUBLIC.books AS _T0
----
--

=== KeyValueView

All examples above use `IRecordView<T>` to perform queries; LINQ provider supports `IKeyValueView<TK, TV>` equally well:

[source, csharp]
----
IQueryable<KeyValuePair<int, Book>> query =
    bookTable.GetKeyValueView<int, Book>().AsQueryable();
List<Book> books = query
    .Where(x => x.Key > 10)
    .Select(x => x.Value)
    .ToList();
----
