9.5、查询操作

数据查询是应用系统中最常用的操作，几乎覆盖了80%的代码。

特别说明

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

如果当前方法只做查询作用，建议所有的 DbSet 或 IQueryable<TEntity> 对象都带上 .AsNoTracking()，可以提高查询性能。相关文档说明

_testRepository.Entity.AsNoTracking();

查询所有数据

无查询条件

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

// 方式二
_testRepository.GetAll();

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

有条件查询

// 方式一，推荐
_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;

根据值是否有效构建查询

// 方式一，推荐
_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 使用

_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 使用

_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 使用

_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 条件

// 创建一个 初始化为 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);

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

// 创建一个初始化表达式池，也就是临时条件
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 条件

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

则可以这样写：

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查询

// 方式一，推荐
_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();

根据条件查询

// 方式一，推荐
_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();

分页查询

默认分页查询

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

配置更多分页条件

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

联表查询

有物理外键关系

有物理外键关系需要在主表和从表中配置相关的导航属性

配置代码大致如下：

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

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

使用

_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();

根据条件成立再连表

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

更多物理外键联表查询操作可查看EF Core 官方文档

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

🏴 Inner Join

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 };

🏴 Left Join

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 联表

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

更多关于 EF Core 复杂连表可查看官方文档。

性能问题

默认情况下，EF Core 不会加载关联数据，除非采用预先加载、显式加载或懒加载模式。但是如果调用 ProjectToType<>()方法之后，就变成了立即加载，这样会导致性能问题！

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

原生SQL查询

在Hoa Framework v1.4.0 版本新增了 切面上下文功能，也就是下面的代码不再推荐使用，见 9.12、切面上下文（TangentDbContext）章节。

返回仓储实体对象

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

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

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

// 方式一，返回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());

查询记录是否存在

// 方式一，推荐
_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;

分组查询

常见分组

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

更多例子

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

多表联接分组

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() }

查询排序

正序

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

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

倒序

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

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

字符串排序

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

动态查询

字符串构建查询

// 动态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空检查符号查询

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

高级用法，动态构建Lambda

// 示例一
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框架支持复杂查询，包括联表、分组、排序、聚合、自定义函数组合等，如：

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 如下：

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;

其他查询

查看记录数

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

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

求和

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

求平均值

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

查时间范围

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

模糊查询

🏴 以某字符串开头

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

🏴 以某字符串结尾

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

🏴 包含字符串

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

数据库Case When实现

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