C# SqlSugar中子查询的使用详解

SqlSugar是一个简单而强大的.NET ORM框架，它提供了丰富的功能来简化数据库操作。子查询是SQL中一个重要的概念，SqlSugar也提供了对子查询的良好支持。在SqlSugar中，子查询可以应用于多种场景，包括但不限于条件筛选、字段选择、连接查询等。本文将通过一系列例子详细介绍SqlSugar中子查询的使用方法。

实体类

Customer实体类

C#
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    // 可以根据实际需求添加更多属性
}

Order实体类

C#
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Amount { get; set; }
    // 可以根据实际需求添加更多属性
}

1. 子查询作为条件

在SqlSugar中，你可以使用子查询来构建复杂的查询条件。

例子1：使用子查询筛选数据

C#
// 创建一个针对Customer实体的SqlSugar仓库实例。
var customerRepository = new SqlSugarRepository<Customer>();

// 使用仓库的AsQueryable方法开始构造一个查询。
var result = customerRepository.AsQueryable()
    // 使用Where方法添加一个条件。这里的条件是使用SqlFunc.Subqueryable来构造一个子查询。
    // 子查询针对Orders表，查找CustomerId等于当前Customer实体Id的记录。
    // 使用NotAny方法来检查这样的记录是否不存在（即没有任何订单的客户）。
    .Where(c => SqlFunc.Subqueryable<Orders>()
    .Where(x => x.CustomerId == c.Id).NotAny())
    // 执行查询并将结果转换为列表。
    .ToList();

// 遍历查询结果，即没有任何订单的客户列表。
foreach (var order in result)
{
    // 打印每个客户的姓名和电子邮件地址。
    Console.WriteLine($"{order.Name} {order.Email}");
}

这个例子中，使用子查询来实现特定的业务逻辑需求，即找出那些没有下过任何订单的客户。通过使用SqlFunc.Subqueryable方法创建子查询，并利用NotAny方法检查符合条件的记录是否不存在，我们能够构造出符合需求的查询表达式。

2. 子查询作为字段

你还可以将子查询的结果作为查询结果的一部分，相当于SQL中的选择列表中的子查询。

例子2：子查询作为返回字段

C#
// 创建一个针对Customer实体的SqlSugar仓库实例。
var customerRepository = new SqlSugarRepository<Customer>();

// 使用仓库的AsQueryable方法开始构造一个查询。
var result = customerRepository.AsQueryable()
   // 使用Select方法来选择数据。这里我们创建了一个匿名类型，包含客户的姓名和订单数量。
   // 订单数量是通过一个子查询计算得到的，子查询针对Orders表，筛选出每个客户的订单并计数。
   .Select(c => new {
       c.Name,
       // 使用SqlFunc.Subqueryable来创建子查询，针对Orders表。
       // 使用Where方法来添加筛选条件，筛选出CustomerId等于当前客户Id的订单。
       // 使用Count方法来计算符合条件的订单数量。
       OrderCount = SqlFunc.Subqueryable<Orders>()
       .Where(o => o.CustomerId == c.Id).Count()
   })
   // 执行查询并将结果转换为列表。
   .ToList();

// 遍历查询结果，每个元素代表一个客户及其订单数量。
foreach (var order in result)
{ 
    // 打印每个客户的姓名和订单数量。
    Console.WriteLine($"{order.Name} {order.OrderCount}");
}

在这个例子中，通过Select方法结合子查询的方式，实现了对每个客户订单数量的统计。SqlFunc.Subqueryable<Orders>()用于创建针对Orders表的子查询，Where方法用于添加筛选条件（选择与当前遍历的客户Id相匹配的订单），Count方法用于计算满足条件的订单数量。

3. 子查询作为连接条件

SqlSugar允许你在连接操作中使用子查询，这在处理复杂的连接逻辑时非常有用。

例子3：使用子查询进行连接

C#
// 创建一个针对Customer实体的SqlSugar仓库实例。
var customerRepository = new SqlSugarRepository<Customer>();

// 使用仓库的AsQueryable方法开始构造一个查询。
var result = customerRepository.AsQueryable()
     // 使用LeftJoin方法执行左连接操作。左连接Orders表。
     // 连接条件使用SqlFunc.Subqueryable来构建一个子查询，选择Orders表中的CustomerId，
     // 并检查它是否等于当前遍历的Customer实体的Id。
     .LeftJoin<Orders>((c, o) => 
     c.Id == SqlFunc.Subqueryable<Orders>().Select(x => x.CustomerId))
     // 使用Where方法添加筛选条件，筛选出订单金额大于160的记录。
     .Where((c,o)=>o.Amount > 160)
     // 使用Select方法选择需要的数据字段，这里选择客户的姓名和订单的金额。
     .Select((c, o) => new { c.Name, o.Amount })
     // 执行查询并将结果转换为列表。
     .ToList();

// 遍历查询结果，每个元素代表一个客户及其订单金额。
foreach (var order in result)
{
    // 打印每个客户的姓名和订单金额。
    Console.WriteLine($"{order.Name} {order.Amount}");
}

这个例子展示了如何使用子查询作为连接条件。查询所有订单金额超过160的客户及其对应的订单金额。

4. 子查询作为表源

在SqlSugar中，子查询还可以作为一个表源进行查询。

例子4：子查询作为表源

C#
// 创建一个针对Customer实体的SqlSugar仓库实例。
var customerRepository = new SqlSugarRepository<Customer>();
// 创建一个针对Orders实体的SqlSugar仓库实例。
var orderRepository = new SqlSugarRepository<Orders>();

// 使用orderRepository的AsQueryable方法开始构造一个查询，这个查询用于筛选出在过去100天内的订单。
var subQuery = orderRepository.AsQueryable()
    // 使用Where方法添加筛选条件，筛选出订单日期在当前日期之前100天内的订单。
    .Where(o => o.OrderDate > DateTime.Now.AddDays(-100));

// 使用上面定义的子查询subQuery来进行内连接操作，连接Customer表。
var result =  subQuery.InnerJoin<Customer>((o,c)=>o.CustomerId == c.Id)
    // 使用Select方法选择需要的数据字段，这里选择了客户的姓名和订单的金额。
    .Select((o,c)=>new { c.Name, o.Amount })
    // 执行查询并将结果转换为列表。
    .ToList();

// 遍历查询结果，每个元素代表一个客户及其订单金额。
foreach (var order in result)
{
    // 打印每个客户的姓名和订单金额。
    Console.WriteLine($"{order.Name} {order.Amount}");
}

这个例子中，查询在过去100天内有订单的客户及其订单的金额。

4. 子查询SUM

查询数据库中的客户信息及其对应的订单总金额。

C#
// 创建一个SqlSugar仓库实例，专门用于操作Customer实体。
var customerRepository = new SqlSugarRepository<Customer>();

// 使用仓库的AsQueryable方法开始构建查询。
var result = customerRepository.AsQueryable().Select(x => new
{
    // 选择客户的姓名。
    Name = x.Name,
    // 使用子查询计算每个客户的订单总金额。
    // SqlFunc.Subqueryable<Orders>()开始定义一个针对Orders表的子查询。
    // Where子句用于过滤出当前迭代客户的订单。
    // Sum方法用于计算过滤出的订单的金额总和。
    Amount = SqlFunc.Subqueryable<Orders>().Where(o => o.CustomerId == x.Id)
    .Sum(o => o.Amount)
}).ToList(); // 执行查询并将结果转换为列表。

// 遍历查询结果列表。
foreach (var order in result)
{
    // 打印每个客户的姓名和他们的订单总金额。
    Console.WriteLine($"{order.Name} {order.Amount}");
}

在Select子句中，我们构造了一个匿名对象，其中包含客户的Name和他们的订单总金额Amount。为了计算Amount，我们使用了SqlFunc.Subqueryable<Orders>()来启动一个针对Orders表的子查询。这个子查询首先通过Where子句筛选出当前客户的所有订单（即CustomerId等于当前客户Id的订单），然后使用Sum函数来累加这些订单的金额字段(Amount)，得到该客户的订单总金额。

结论

SqlSugar通过提供对子查询的支持，使得构建复杂的数据库查询变得简单而高效。无论是作为条件、字段、连接条件还是表源，子查询都能在SqlSugar中得到灵活应用。掌握SqlSugar中子查询的使用，将有助于开发者编写出更加高效、优雅的数据访问代码。

目录

实体类