.NET LINQ查询
Ignite .NET 客户端也支持 Ignite SQL API 与 LINQ 的集成,因此就不需要直接使用 SQL 语法。可以使用 LINQ 在 C# 中编写查询,然后再将 C# LINQ 表达式转换为 Ignite 自己的 SQL,例如以下两个代码段可实现相同的结果:
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();
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] });
}
与 SQL 相比,LINQ 具有以下优点:
- 查询在编译时进行强类型化和检查;
- 更容易通过 IDE 编写和维护(自动完成、导航、查找用法)更容易编写和维护;
- LINQ 方便重构:重命名列,所有查询都会立即更新;
- 不需要了解 Ignite 特有的 SQL 知识,并且大多数 C# 开发者熟悉 LINQ;
- LINQ 可以防止 SQL 注入;
- 结果自然地映射到类型。
在实际场景中,Ignite LINQ 查询的性能与等效的 SQL 查询相当,但是还是存在少量开销(由于查询转换)。实际情况可能会因查询复杂性而异,因此建议评估查询的性能。
1.LINQ入门
下面的代码在 Ignite 中创建了一个简单的表:
创建表:
csharpawait Client.Sql.ExecuteAsync( null, @"CREATE TABLE PUBLIC.PERSON (NAME VARCHAR PRIMARY KEY, AGE INT)");
定义表示表的类(或记录):
- 成员名称应与列名称匹配(不区分大小写);
- 如果列名不是有效的 C# 标识符,需要使用
[Column("name")]
属性指定名称。
csharppublic record Person(string Name, int Age, string Address, string Status);
获取表引用:
csharpITable table = await Client.Tables.GetTableAsync("PERSON");
使用
GetRecordView<T>()
方法获取表的类型化视图:csharpIRecordView<Person> view = table.GetRecordView<Person>();
在
IRecordView<T>
上用AsQueryable()
执行 LINQ 查询:csharpList<string> names = await view.AsQueryable() .Where(x => x.Age > 30) .Select(x => x.Name) .ToListAsync();
2.使用LINQ
2.1.检查生成的SQL
查看生成的 SQL 对于调试和性能优化非常有用,有两种方法可以获得生成的SQL:
IgniteQueryableExtensions.ToQueryString()
扩展方法:csharpIQueryable<Person> query = table.GetRecordView<Person>() .AsQueryable() .Where(x => x.Age > 30); string sql = query.ToQueryString();
- 调试日志记录:csharp所有生成的 SQL 都将以
var cfg = new IgniteClientConfiguration { Logger = new ConsoleLogger { MinLevel = LogLevel.Debug }, ... }; using var client = IgniteClient.StartAsync(cfg); ...
Debug
级别记录到指定的 logger 中。
2.2.事务
可以使用AsQueryeable
参数将事务传递给 LINQ 程序:
await using var tx = await client.Transactions.BeginAsync();
var view = (await client.Tables.GetTableAsync("person"))!.GetRecordView<Person>();
pocoView.AsQueryable(tx)...;
2.3.自定义查询选项
自定义查询选项(超时、页面大小)可以通过使用第二个AsQueryable
的QueryableOptions
参数来指定:
var options = new QueryableOptions
{
PageSize = 512,
Timeout = TimeSpan.FromSeconds(30)
};
table.GetRecordView<Person>().AsQueryable(options: options)...;
2.4.结果具体化
具体化是将查询结果IQueryable<T>
转换为对象或对象集合的过程。
LINQ 是延迟执行的,在查询具体化之前,什么都不会执行(没有网络访问,没有 SQL 转换)。 例如以下代码仅构造一个表达式,但不执行任何逻辑:
IQueryable<Person> query = table!.GetRecordView<Person>().AsQueryable()
.Where(x => x.Key > 3)
.OrderBy(x => x.Key);
可以通过多种方式触发查询执行和具体化:
2.4.1.迭代
可以使用foreach
语句循环访问查询结果,也可以使用AsAsyncEnumerable
方法异步循环访问查询结果:
foreach (var person in query) { ... }
await foreach (var person in query.AsAsyncEnumerable()) { ... }
2.4.2.转换为Collections
可以使用ToList
和ToDictionary
方法将查询转换为集合,或者使用ToListAsync
和ToDictionaryAsync
方法异步执行此操作:
List<Person> list = query.ToList();
Dictionary<string, int> dict = query.ToDictionary(x => x.Name, x => x.Age);
List<Person> list = await query.ToListAsync();
Dictionary<string, int> dict = await query.
ToDictionaryAsync(x => x.Name, x => x.Age);
2.4.3.Ignite特有的IResultSet
底层的IResultSet
可以通过IgniteQueryableExtensions.ToResultSetAsync()
扩展方法获取:
await using IResultSet<Person> resultSet = await query.ToResultSetAsync();
Console.WriteLine(resultSet.Metadata);
var rows = resultSet.CollectAsync(...);
获取的IResultSet
可用于访问元数据和CollectAsync
方法,从而对结果具体化提供更多控制。
3.支持的LINQ特性
3.1.投影
投影是将查询结果转换为不同类型的过程,此外投影还可用于选择列的子集。
例如,Person
表可能有很多列,但实际只需要Name
和Age
:
- 首先创建一个投影类:csharp
public record PersonInfo(string Name, int Age);
- 然后
Select
用于投影查询结果:csharpList<PersonInfo> result = query .Select(x => new PersonInfo(x.Name, x.Age)) .ToList();
生成的 SQL 将仅选择这两列,以避免过度获取(一个常见问题是,ORM 生成的查询包含所有表列,但业务逻辑只需要其中的几个列)。
Ignite 还支持匿名类型投影:
var result = query.Select(x => new { x.Name, x.Age }).ToList();
3.2.内联接
内联接是通过Join
方法实现的:
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();
3.3.外联接
外联接是通过DefaultIfEmpty
方法实现的。例如并非图书馆中的每本书都由学生借阅,因此使用左外联接来检索所有书籍及其当前借阅者(如果有):
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();
3.4.分组
分组是通过GroupBy
实现的,这相当于 SQL 的 GROUP BY 子句。可以在查询中同时获取单列和多列。使用多个列时,需要使用匿名类型:
var bookCountByAuthor = bookTable.GetRecordView<Book>().AsQueryable()
.GroupBy(book => book.Author)
.Select(grp => new { Author = grp.Key, Count = x.Count() })
.ToList();
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();
聚合函数Count
、Sum
、Min
、Max
、Average
可以与分组操作一起使用。
3.5.排序
支持OrderBy
、OrderByDescending
、ThenBy
、ThenByDescending
,可以将它们组合起来,按多列排序:
var booksOrderedByAuthorAndYear = bookTable.GetRecordView<Book>().AsQueryable()
.OrderBy(book => book.Author)
.ThenByDescending(book => book.Year)
.ToList();
3.6.并集、交集和差集
可以使用Union
、Intersect
、Except
方法对多个结果集进行组合运算,例如:
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();
3.7.聚合函数
以下是 .NET 的聚合函数及其 Ignite 支持的等效 SQL 函数列表:
LINQ 同步方法 | LINQ 异步方法 | SQL 运算符 |
---|---|---|
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 |
以下是如何使用这些方法的示例:
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);
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);
3.8.数学函数
支持Abs
、Cos
、Cosh
、Acos
、Sin
、Sinh
、Asin
、Tan
、Tanh
、Atan
、Ceiling
、Floor
、Exp
、Log
、Log10
、Pow
、Round
、Sign
、Sqrt
、Truncate
数学函数(将转换为等价 SQL 函数)。
不支持以下数学函数(在 Ignite SQL 引擎中没有等价函数):Acosh
、Asinh
、Atanh
、Atan2
、Log2
、Log(x, y)
。
以下是如何使用数学函数的示例:
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();
3.9.字符串函数
支持以下字符串函数:string.Compare(string)
、string.Compare(string, bool ignoreCase)
、连接: 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
。
以下是如何使用字符串函数的示例:
List<string> fullNames = table.GetRecordView<Person>().AsQueryable()
.Where(p => p.FirstName.StartsWith("Jo"))
.Select(p => new {
FullName = p.FirstName.ToUpper() +
" " +
p.LastName.ToLower() })
.ToList();
3.10.正则表达式
Regex.Replace
将被转换为regexp_replace
函数,以下是使用正则表达式的方法:
List<string> addresses = table.GetRecordView<Person>().AsQueryable()
.Select(p => new { Address = Regex.Replace(p.Address, @"(\d+)", "[$1]")
.ToList();
提示
SQL 中的正则表达式引擎的行为可能因 .NET 引擎的不同而不同。
3.11.DML(批量更新和删除)
通过IQueryable<T>
的ExecuteUpdateAsync
和ExecuteDeleteAsync
扩展方法支持具有可选条件的批量更新和删除:
var orders = orderTable.GetRecordView<Order>().AsQueryable();
await orders.Where(x => x.Amount == 0).ExecuteDeleteAsync();
Update 语句可以将属性设置为常量值或基于同一行其他属性的表达式:
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));
生成的 SQL:
update PUBLIC.tbl1 as _T0
set NOTE = concat(concat(_T0.NOTE, ?), _T0.CUSTOMERNAME), DISCOUNT = ?
where (_T0.CUSTOMERID IS NOT DISTINCT FROM ?)
3.12.组合查询
IQueryable<T>
表达式可以动态组合。一个常见的场景是根据用户输入组合查询。 例如,可以将不同列上的可选筛选条件应用于查询:
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();
}
3.13.列名映射
除非通过[Column]
提供了自定义映射,否则 LINQ 程序将使用属性或字段名作为列名,如果标识符不带引号,将不区分大小写。
bookTable.GetRecordView<Book>().AsQueryable().Select(x => x.Author).ToList();
select _T0.AUTHOR from PUBLIC.books as _T0
要使用带引号的标识符,或将列名映射到不同的属性名,需要使用[Column]属性:
public class Book
{
[Column("book_author")]
public string Author { get; set; }
}
// Or a record:
public record Book([property: Column("book_author")] string Author);
SELECT _T0."book_author" FROM PUBLIC.books AS _T0
3.14.KeyValueView
前述所有示例都使用IRecordView<T>
执行查询,LINQ 程序也支持IKeyValueView<TK, TV>
:
IQueryable<KeyValuePair<int, Book>> query =
bookTable.GetKeyValueView<int, Book>().AsQueryable();
List<Book> books = query
.Where(x => x.Key > 10)
.Select(x => x.Value)
.ToList();
18624049226