# 9.5、查询操作

## 特别说明

**该章节中所有集合查询并不会真正的执行数据库查询，如若拷贝代码进去程序中，必须手动调用 `.ToList()` 方法才会做真正的数据库查询操作。🤣😂😎**

如果当前方法只做查询作用，建议所有的 `DbSet` 或 `IQueryable<TEntity>` 对象都带上 `.AsNoTracking()`，可以提高查询性能。[相关文档说明](https://docs.microsoft.com/zh-cn/ef/core/querying/tracking)

```csharp
_testRepository.Entity.AsNoTracking();
```

## 查询所有数据

### 无查询条件

```csharp
// 方式一，推荐
_testRepository.Entity;

// 方式二
_testRepository.GetAll();

// 方式三
from u in _testRepository.Entity
select u;
```

### 有条件查询

```csharp
// 方式一，推荐
_testRepository.Entity.Where(u => u.Id == 1 && u.Name.Contains("Hoa"));

// 方式二
_testRepository.GetAll(u => u.Id == 1 && u.Name.Contains("Hoa"));

// 方式三
from u in _testRepository.Entity
where u => u.Id == 1 && u.Name.Contains("Hoa")
select u;
```

### 根据值是否有效构建查询

```csharp
// 方式一，推荐
_testRepository.Entity
    .Where(u => u.Void == 0)
    .WhereIf(!string.IsNullOrEmpty(name),u => u.Name.Contains(name))
    .WhereIf(age > 18,u => u.Age == age)
    .Where(u => u.Field > 20);
    
// 方式二
_testRepository.GetAll()
    .Where(u => u.Void == 0)
    .WhereIf(!string.IsNullOrEmpty(name),u => u.Name.Contains(name))
    .WhereIf(age > 18,u => u.Age == age)
    .Where(u => u.Field > 20);
    
// 方式三
from u in _testRepository.Entity
where u.Void == 0 
      && (string.IsNullOrEmpty(name) || u.Name.Contains(name)) 
      && (age <= 18 || u.Age == age)
      && u.Field > 20;
select u;
```

`WhereIf` 相比 `Where` 多了一个参数，就是判断这个参数是否有效，如果有效才新增条件进行查询。

## 条件拼接高级用法

Hoa Framework 中提供了一些高级拼接条件的方法，有：

* `WhereIf`：如果第一个参数为 `true` 才构建查询表达式
* `WhereOr`：支持 `or` 连接表达式
* `WhereIfOr`：如果第一个参数为`true`，才构建 `or` 连接表达式

### WhereIf 使用

```csharp
_testRepository.Entity
    .WhereIf(!string.IsNullOrEmpty(name), u => u.Name.Contains(name))
    .WhereIf(age > 18, u => u.Age == age)
    .Where(u => u.Field > 20);
```

上述生成的`sql`条件全部采用  `and` 连接。

### WhereOr 使用

```csharp
_testRepository.Entity
    .WhereOr(u => u.Field > 20, 
             u => u.Name.Contains("monk"),
             u => u.CreatedDt > DateTime.Now.AddDays(-10))
    .Where(u => u.Void == 0);
```

在 `WhereOr` 表达式中的集合生成的 `sql`全部会采用 `or` 连接操作。

### WhereIfOr 使用

```csharp
_testRepository.Entity
    .WhereIfOr(
                  ( !string.IsNullOrEmpty(name), u => u.Field > 20 ), 
                  ( age > 18, u => u.Name.Contains("monk") ),
                  ( false, u => u.CreatedDt > DateTime.Now.AddDays(-10) )
               )
    .WhereIf(age > 18, u => u.Age == age)
    .Where(u => u.Void == 0);
```

`WhereIfOr` 就是 `WhereIf` 和 `WhereOr` 的结合版。

### 一次性拼接好添加再查询

框架还提供一种更灵活的方式拼接 `Where` 条件

```csharp
// 创建一个 初始化为 or 查询的表达式，也可以创建 And<Test>()
var predicate = LinqBuilder.Or<Test>();
// 随你喜欢拼接
predicate = predicate.Or(u => u.UserName.Contains("a"))
                     .Or(u => u.AccountId == 1)
                     .Or(u => u.Void == 0)
                     .And(u => u.CreatedDt > DateTime.Now)
                     .OrIf(age > 18, u => u.Name.Contains("monk"))
                     .AndIf(!string.IsNullOrEmpty(name), u => u.Field > 20);
// 最后查询                    
var result = _testRepository.Entity.Where(predicate);
```

还可以创建一个 `表达式池`，也就是给定初始化值再随意拼接

```csharp
// 创建一个初始化表达式池，也就是临时条件
var predicate = LinqBuilder.Pool<Test>(u => u.Id > 100);
// 随你喜欢拼接
predicate = predicate.Or(u => u.UserName.Contains("a"))
                     .Or(u => u.AccountId == 1)
                     .Or(u => u.Void == 0)
                     .And(u => u.CreatedDt > DateTime.Now)
                     .OrIf(age > 18, u => u.Name.Contains("monk"))
                     .AndIf(!string.IsNullOrEmpty(name), u => u.Field > 20);
// 最后查询                    
var result = _testRepository.Entity.Where(predicate);
```

### 关于 `LinqBuilder.Pool<T>` 用法

`LinqBuilder.Pool<T>` 是构建一个临时条件池，可重复构建。例如，我需要剩下如下 `sql` 条件

```sql
select * from test where name like 'Hoa' and ( age > 10 or gender = '男');
```

则可以这样写：

```csharp
var predicate =  LinqBuilder.Pool<Test>(u => u.Name.Contains("Hoa"))
    .And(
        LinqBuilder.Pool<Test>(u => u.Age > 10 || u.Gender == "男")
    );
    
var result = _testRepository.Entity.Where(predicate);
```

或用 `WhereOr` ，这种更简单

```
var result = _testRepository.Entity
                .Where(u => u.Name.Contains("Hoa"))
                .WhereOr(u => u.Age > 10,
                         u => u.Gender == "男");
```

### 支持以下拼接表达式

* `And`
* `AndIf`
* `Or`
* `OrIf`

## 查询单条记录

### 根据主键Id查询

```csharp
// 方式一，推荐
_testRepository.Find(1);

// 方式二
_testRepository.Entity.Find(1);

// 方式三
_testRepository.GetFirstOrDefault(predicate: u => u.Id == 1);

// 方式四，如果存在多条会报错
_testRepository.Entity.SingleOrDefault(u => u.Id == 1); 

// 方式五
_testRepository.Entity.FirstOrDefault(u => u.Id == 1); 

// 方式六
_testRepository.Entity.Where(u => u.Id == 1).FirstOrDefault();

// 方式七
_testRepository.GetAll(u => u.Id == 1).FirstOrDefault();
```

### 根据条件查询

```csharp
// 方式一，推荐
_testRepository.GetFirstOrDefault(predicate: u => u.Name.Equals("Hoa"));

// 方式二
_testRepository.Entity.SingleOrDefault(u => u.Name.Equals("Hoa")); 

// 方式三
_testRepository.Entity.FirstOrDefault(u => u.Name.Equals("Hoa")); 

// 方式四
_testRepository.Entity.Where(u => u.Name.Equals("Hoa")).FirstOrDefault();

// 方式无
_testRepository.GetAll(u => u.Name.Equals("Hoa")).FirstOrDefault();
```

## 分页查询

### 默认分页查询

```csharp
_testRepository.GetPagedList(u => u.Id > 1);
```

### 配置更多分页条件

```csharp
_testRepository.GetPagedList(
        predicate: u => u.Id > 1, 
        orderBy: u => u.OrderBy(o => o.Age), 
        pageIndex: 0, 
        pageSize: 20);
```

## 联表查询

### 有物理外键关系

有物理外键关系需要在主表和从表中配置相关的[导航属性](https://docs.microsoft.com/zh-cn/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key)

配置代码大致如下：

```csharp
// 一对多，多对多
[ForeignKey(testid)]
public virtual ICollection<TestEntity> Details { get; set; }

// 一对一
[ForeignKey(detailid)]
public virtual DetailEntity Detail { get; set; }
```

使用

```csharp
_testRepository.GetAll(p => p.PersonId == person.PersonId) 
               .Include(p => p.PersonAddresses)
                   .ThenInclude(p => p.Lookup)
               .Include(p => p.PersonEmails).ThenInclude(x => x.Lookup)
               .Include(p => p.PersonPhones).ThenInclude(x => x.Lookup)
               .Include(p => p.CoveragePersons)
                    .ThenInclude(p => p.Coverage)
                    .ThenInclude(p => p.Policy)
               .FirstOrDefault();
```

### 根据条件成立再连表

```csharp
_testRepository.GetAll(p => p.PersonId == person.PersonId) 
               .IncludeIf(!string.IsNullOrEmpty(name), p => p.PersonAddresses)
```

更多物理外键联表查询操作[可查看EF Core 官方文档](https://docs.microsoft.com/zh-cn/ef/core/querying/related-data)

### 无物理外键关系，存在逻辑外键

#### :flag\_black: Inner Join

```csharp
var leftJoinQuery = from u in _testRepository.Entity 
                    join a in _childRepository.Entity on u.Id equals a.ChildId
                    join b in _detailRepository.Entity on u.Id equals b.DetailId 
                    select new { a, u };
```

#### :flag\_black: Left Join

```csharp
var leftJoinQuery = from u in _testRepository.Entity 
                    join a in _childRepository.Entity on u.Id equals a.ChildId into results // left join关键
                    from a in results.DefaultIfEmpty()  // left join关键
                    join b in _detailRepository.Entity on u.Id equals b.DetailId   // 多个表
                    select new { a, u };
```

### Lamda 联表

```csharp
_testRepository.Entity
    .Join(_schoolRepository.Entity, 
          stu => stu.SchoolId, sch => sch.Id, 
          (stu, sch) => new { stu, sch.SchoolName });
```

更多关于 EF Core 复杂连表[可查看官方文档](https://docs.microsoft.com/zh-cn/ef/core/querying/complex-query-operators)。

## 性能问题

默认情况下，EF Core 不会加载关联数据，除非采用[预先加载](https://docs.microsoft.com/zh-cn/ef/core/querying/related-data)、[显式加载](https://docs.microsoft.com/zh-cn/ef/core/querying/related-data)或[懒加载模式](https://docs.microsoft.com/zh-cn/ef/core/querying/related-data)。但是如果调用 `ProjectToType<>()`方法之后，就变成了立即加载，这样会导致性能问题！

所以，如果没有关联实体的查询可以采用 `ProjectToType<>()`方式，否则采用 `ToList().Adapt<>()`方式。

## 原生SQL查询

**在Hoa Framework v1.4.0 版本新增了 切面上下文功能，也就是下面的代码不再推荐使用，见** [**9.12、切面上下文（TangentDbContext）**](https://monksoul.gitbook.io/hoa/shujukucaozuoshinan/qiemianshagnxiawen)**章节。**

### 返回仓储实体对象

```csharp
// 方式一
_testRepository.FromSql("select * from Test");

// 方式二
_testRepository.SqlQuery<TestEntity>("select * from Test");
```

### 返回任意对象（非常强大）

```csharp
// 方式一，返回DataTable
_testRepository.SqlQuery("select name,age from Test");
_testRepository.SqlQuery("select 'Hoa' as Name, 27 as Age");
_testRepository.SqlQuery("select 1");
_testRepository.SqlQuery("select * from Test where Id=@Id", 
                         new { Id = 1}.ToSqlParameters());

// 方式二，返回任意类型
_testRepository.SqlQuery<TestEntity>("select name,age from Test");
_testRepository.SqlQuery<TestEntity>("select 'Hoa' as Name, 27 as Age");
_testRepository.SqlQuery<decimal?>("select 1");
_testRepository.SqlQuery<TestEntity>("select * from Test where Id=@Id", 
                         new { Id = 1}.ToSqlParameters());
```

## 查询记录是否存在

```csharp
// 方式一，推荐
_testRepository.Exists(u => u.Void == 0);

// 方式二
_testRepository.Entity.Any(u => u.Void == 0);

// 方式三
_testRepository.Count(u => u.Void == 0) > 0;

// 方式四
_testRepository.Entity.Count(u => u.Void == 0) > 0;

// 方式五
_testRepository.Entity.Where(u => u.Void == 0).Count() > 0;

// 方式六
_testRepository.GetAll(u => u.Void == 0).Count() > 0;
```

## 分组查询

### 常见分组

```csharp
_testRepository.Entity.GroupBy(x => new { x.Column1, x.Column2 });
```

### 更多例子

```csharp
from student in _testRepository.Entity
group student by student.EnrollmentDate into dateGroup
select new EnrollmentDateGroup()
{
    EnrollmentDate = dateGroup.Key,
    StudentCount = dateGroup.Count()
};
```

### 多表联接分组

```csharp
from a in _testRepository.Entity
join b in _detailRepository.Entity
on a.PrefectureId equals b.PrefectureId
join c in table_c on c.id equals b.id
group emp by new { emp.Age, emp.Sex } into g
select new { Peo = g.Key, Count = g.Count() }
```

## 查询排序

### 正序

```csharp
// 单个排序
_testRepository.Entity.OrderBy(u => u.Age);

// 多个排序
_testRepository.Entity.OrderBy(u => u.Age).ThenBy(u => u.Name);
```

### 倒序

```csharp
// 单个排序
_testRepository.Entity.OrderByDescending(u => u.Age);

// 多个排序
_testRepository.Entity.OrderBy(u => u.Age).ThenByDescending(u => u.Name);
```

### 字符串排序

```csharp
_testRepository.OrderByOrdinal(u => "Age asc, Name desc");
```

## 动态查询

### 字符串构建查询

```csharp
// 动态Where查询，动态排序
var query = _testRepository.Entity
    .Where("City == @0 and Orders.Count >= @1", "London", 10)
    .OrderBy("CompanyName")
    .Select("new(CompanyName as Name, Phone)");

// 模糊查询
var list = _testRepository.Entity
      .Where("Name.Contains(@0)","ZZZ Projects")
      .ToList();
```

### 支持Lambda空检查符号查询

```csharp
var customers = _testRepository.Entity
        .Include(c => c.Location)
        .Where(c => c.Location?.Name == "test") // 注意 Location?.Name
        .ToList();
```

### 高级用法，动态构建Lambda

```csharp
// 示例一
var x = Expression.Parameter(typeof(int), "x");
var y = Expression.Parameter(typeof(int), "y");
var e = DynamicExpressionParser
            .ParseLambda(new ParameterExpression[] { x, y }, null, "(x + y) * 2");

// 示例二
var e = DynamicExpressionParser.ParseLambda(
        typeof(Customer), typeof(bool),
        "City = @0 and Orders.Count >= @1",
        "London", 10);
```

### 动态Lambda 支持方法

* `All`
* `Any`
* `Average`
* `Count`
* `First`
* `FirstOrDefault`
* `GroupBy`
* `GroupByMany`
* `GroupJoin`
* `Join`
* `Last`
* `LastOrDefault`
* `LongCount`
* `OfType`
* `OrderBy`
* `Select`
* `SelectMany`
* `Single`
* `SingleOrDefault`
* `Sum`
* `ThenBy`
* `Where`

## 复杂查询

Hoa Framework ORM框架支持复杂查询，包括联**表、分组、排序、聚合、自定义函数组合**等，如：

```csharp
var result = (
                 from claim in _claims.Entity
                 // Left Join 联表
                 join claimDetail in _claimDetails.Entity on claim.ClaimId equals claimDetail.ClaimId into claimDetailView
                 from claimDetail in claimDetailView.DefaultIfEmpty()
                 // Inner Join 联表，，支持自定义表函数：DbScalarFunctions.GetLookUpId
                 join personIdentifier in _personIdentifiers.Entity on new { claim.MemberNum, PersonIdentifierTypeId = DbScalarFunctions.GetLookUpId("PERSON IDENTIFIER TYPE", "Alliant Member Number") } equals new { MemberNum = personIdentifier.IdNum, personIdentifier.PersonIdentifierTypeId }
                 // Left Join 联表，支持多个字段筛选
                 join document in _documents.Entity on new { claim.ClaimId, Type = "EOB" } equals new { ClaimId = document.EntityId, document.Type } into documentView
                 from document in documentView.DefaultIfEmpty()
                 // 设置条件
                 where personIdentifier.PersonId == id
                 // 进行分组，支持多个字段
                 group claim by new
                 {
                     claim.ClaimId,
                     claim.ClaimNum,
                     claim.ExternalClaimNum,
                     claim.Status,
                     claim.ServiceFromDt,
                     claim.ReceivedDt,
                     claim.ProviderName,
                     document.DocumentNum
                 } into claimGroupView
                 // 筛选数据
                 select new GetMemberClaimsDto
                 {
                     MEDICAL_CLAIM_ID = claimGroupView.Key.ClaimId,
                     // 实现 Case When
                     CLAIM_NUM = claimGroupView.Key.ReceivedDt.Value < DateTime.Parse("1/1/2019") ? claimGroupView.Key.ExternalClaimNum : claimGroupView.Key.ClaimNum,
                     CLAIM_STATUS = claimGroupView.Key.Status,
                     PROVIDER_NAME = claimGroupView.Key.ProviderName,
                     DATE_OF_SERVICE = claimGroupView.Key.ServiceFromDt,
                     // 聚合查询 SUM
                     BILLED_AMOUNT = _claimDetails.Entity.Sum(u => u.BilledAmt),
                     DISCOUNT_AMOUNT = _claimDetails.Entity.Sum(u => u.BilledAmt - u.PaidAmt - u.OwedAmt),
                     PAID_AMOUNT = _claimDetails.Entity.Sum(u => u.PaidAmt),
                     OWED_AMOUNT = _claimDetails.Entity.Sum(u => u.OwedAmt),
                     EOB_LIST_ID = string.Empty,
                     EOB_NUMBER = claimGroupView.Key.DocumentNum,
                 })
                 // 排序
                 .OrderByDescending(u => u.DATE_OF_SERVICE).ToList();
```

最终打印的 `Sql` 如下：

```sql
SELECT [c3].[CLAIM_ID] AS [MEDICAL_CLAIM_ID],
       CASE
           WHEN [c3].[RECEIVED_DT] < '2019-01-01T00:00:00.000' THEN
               [c3].[EXTERNAL_CLAIM_NUM]
           ELSE
               [c3].[CLAIM_NUM]
       END AS [CLAIM_NUM],
       [c3].[STATUS] AS [CLAIM_STATUS],
       [c3].[PROVIDER_NAME],
       [c3].[SERVICE_FROM_DT] AS [DATE_OF_SERVICE],
       (
           SELECT SUM([c].[BILLED_AMT]) FROM [Claims].[CLAIM_DETAILS] AS [c]
       ) AS [BILLED_AMOUNT],
       (
           SELECT SUM(([c0].[BILLED_AMT] - [c0].[PAID_AMT]) - [c0].[OWED_AMT])
           FROM [Claims].[CLAIM_DETAILS] AS [c0]
       ) AS [DISCOUNT_AMOUNT],
       (
           SELECT SUM([c1].[PAID_AMT]) FROM [Claims].[CLAIM_DETAILS] AS [c1]
       ) AS [PAID_AMOUNT],
       (
           SELECT SUM([c2].[OWED_AMT]) FROM [Claims].[CLAIM_DETAILS] AS [c2]
       ) AS [OWED_AMOUNT],
       N'' AS [EOB_LIST_ID],
       [d].[DOCUMENT_NUM] AS [EOB_NUMBER]
FROM [Claims].[CLAIMS] AS [c3]
    LEFT JOIN [Claims].[CLAIM_DETAILS] AS [c4]
        ON [c3].[CLAIM_ID] = [c4].[CLAIM_ID]
    INNER JOIN [Persons].[PERSON_IDENTIFIERS] AS [p]
        ON ([c3].[MEMBER_NUM] = [p].[ID_NUM])
           AND ([dbo].[FN_GET_LOOKUP_ID](N'PERSON IDENTIFIER TYPE', N'Alliant Member Number') = [p].[PERSON_IDENTIFIER_TYPE_ID])
    LEFT JOIN [Reference].[DOCUMENTS] AS [d]
        ON ([c3].[CLAIM_ID] = [d].[ENTITY_ID])
           AND ('EOB' = [d].[TYPE])
WHERE [p].[PERSON_ID] = 1
GROUP BY [c3].[CLAIM_ID],
         [c3].[CLAIM_NUM],
         [c3].[EXTERNAL_CLAIM_NUM],
         [c3].[STATUS],
         [c3].[SERVICE_FROM_DT],
         [c3].[RECEIVED_DT],
         [c3].[PROVIDER_NAME],
         [d].[DOCUMENT_NUM]
ORDER BY [c3].[SERVICE_FROM_DT] DESC;
```

## 其他查询

### 查看记录数

```csharp
// 方式一，推荐
_testRepository.Count(u => u.Void == 0);

// 方式二
_testRepository.Entity.Count(u => u.Void == 0);
```

### 求和

```csharp
_testRepository.Entity.Sum(u => u.Count);
```

### 求平均值

```csharp
_testRepository.Entity.Average(u => u.Count);
```

### 查时间范围

```csharp
var starDate = DateTime.Parse("2019-05-10");
var endDate = DateTime.Parse("2020-05-10");
 _testRepository.Entity
     .Where(u => u.CreatedDt >= starDate && u.CreatedDt <= endDate);
```

### 模糊查询

#### :flag\_black: 以某字符串开头

```csharp
_testRepository.Entity.Where(u => u.Name.StartWith("Hoa"));
```

#### :flag\_black: 以某字符串结尾

```csharp
_testRepository.Entity.Where(u => u.Name.EndWith("Hoa"));
```

#### :flag\_black: 包含字符串

```csharp
_testRepository.Entity.Where(u => u.Name.Contains("Hoa"));
```

### 数据库Case When实现

数据库中的 `Case When` 实际上对应的是我们程序中的 `三元表达式` ，也就是使用 `三元表达式` 即可自动生成 `Case When` 语句。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://monksoul.gitbook.io/hoa/shujukucaozuoshinan/chaxuncaozuo.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.