[ignite-3] branch main updated: IGNITE-18793 Add public doc for LINQ (#1674)

[pt...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

ptupitsyn pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git


The following commit(s) were added to refs/heads/main by this push:
     new 9806c3ee00 IGNITE-18793 Add public doc for LINQ (#1674)
9806c3ee00 is described below

commit 9806c3ee000d6a97f039297c9b1ac8fbe5943750
Author: IgGusev <ig...@gridgain.com>
AuthorDate: Wed Feb 15 18:01:19 2023 +0200

    IGNITE-18793 Add public doc for LINQ (#1674)
---
 docs/_data/toc.yaml               |   8 +-
 docs/_docs/thin-clients/linq.adoc | 562 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 568 insertions(+), 2 deletions(-)

diff --git a/docs/_data/toc.yaml b/docs/_data/toc.yaml
index 317e31ddff..526946636a 100644
--- a/docs/_data/toc.yaml
+++ b/docs/_data/toc.yaml
@@ -45,8 +45,12 @@
       url: sql-reference/operators-and-functions
 - title: REST API
   url: rest/rest-api
-- title: Thin Clients
-  url: thin-clients/index
+- title: Clients
+  items:
+    - title: Clients Overview
+      url: thin-clients/index
+    - title: .NET LINQ Queries
+      url: thin-clients/linq
 - title: Distributed Computing
   url: compute/compute
 - title: Data Region Configuration
diff --git a/docs/_docs/thin-clients/linq.adoc b/docs/_docs/thin-clients/linq.adoc
new file mode 100644
index 0000000000..be47332ea0
--- /dev/null
+++ b/docs/_docs/thin-clients/linq.adoc
@@ -0,0 +1,562 @@
+= .NET LINQ Queries
+
+Apache 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 Apache 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 Apache 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 Apache 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();
+----