entity framework core 5使用SQL查询并聚合转化为指定DTO返回

在Entity Framework Core 5中，可以使用LINQ查询语法或者原始SQL语句来查询数据。以下是使用原始SQL语句查询并聚合数据，并将其转换为指定DTO返回的示例：

1. 创建一个DTO类，用于存储查询结果：

public class OrderSummaryDTO
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalAmount { get; set; }
}

2. 编写原始SQL查询语句并执行：

var query = @"SELECT c.Id AS CustomerId, c.Name AS CustomerName, COUNT(o.Id) AS OrderCount, SUM(o.Amount) AS TotalAmount 
              FROM Customers c
              INNER JOIN Orders o ON c.Id = o.CustomerId
              GROUP BY c.Id, c.Name";

var result = dbContext.OrderSummaryDTOs.FromSqlRaw(query).ToList();

3. 将查询结果转换为DTO对象列表：

var orderSummaries = result.Select(r => new OrderSummaryDTO
{
    CustomerId = r.CustomerId,
    CustomerName = r.CustomerName,
    OrderCount = r.OrderCount,
    TotalAmount = r.TotalAmount
}).ToList();

4. 返回DTO对象列表：

return orderSummaries;
``