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

上一页9.4、增删改操作下一页9.6、DataSet、DataTable 操作

最后更新于4年前

特别说明

查询所有数据

无查询条件

有条件查询

根据值是否有效构建查询

条件拼接高级用法

WhereIf 使用

WhereOr 使用

WhereIfOr 使用

一次性拼接好添加再查询

关于 LinqBuilder.Pool<T> 用法

支持以下拼接表达式

查询单条记录

根据主键Id查询

根据条件查询

分页查询

默认分页查询

配置更多分页条件

联表查询

有物理外键关系

根据条件成立再连表

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

Inner Join

Left Join

Lamda 联表

性能问题

原生SQL查询

返回仓储实体对象

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

查询记录是否存在

分组查询

常见分组

更多例子

多表联接分组

查询排序

正序

倒序

字符串排序

动态查询

字符串构建查询

支持Lambda空检查符号查询

高级用法，动态构建Lambda

动态Lambda 支持方法

复杂查询

其他查询

查看记录数

求和

求平均值

查时间范围

模糊查询

以某字符串开头

以某字符串结尾

包含字符串

数据库Case When实现

Inner Join

Left Join

以某字符串开头

以某字符串结尾

包含字符串

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