| // 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(); |
| ---- |
