> For the complete documentation index, see [llms.txt](https://monksoul.gitbook.io/hoa/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://monksoul.gitbook.io/hoa/shujukucaozuoshinan/chaxuncaozuo.md).

# 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` 语句。