Abp vNext : Appliction 层 ApplicationService 中聚合根之间级联查询

背景
有问题查询实现
改进查询实现

背景

领域驱动设计（DDD）最佳实践，聚合根是其内部实体数据访问的总入口，故聚合根内最好不要有其它聚合根实例对象（属性导航、集合导航）的引用，仅保留其它聚合根的Id。
这样就需要考聚合根之间级联查询。

下面的查询示例，涉及 4 个聚合根（数据库表)的级联查询，包括左外部联接。

Linq 左外部联接：
https://learn.microsoft.com/zh-cn/dotnet/csharp/linq/perform-left-outer-joins

聚合根关系：

聚合根 RawMaterialOutwarehouseRecord 的定义：


public class RawMaterialOutwarehouseRecord : AuditedAggregateRoot<Guid>
{
    ......

    [NotNull]
    /// <summary>
    /// 原料Id
    /// </summary>
    public Guid RawMaterialId { get; set; } // 其它聚合根Id

    /// <summary>
    /// 订单Id
    /// </summary>
    public Guid? OrderId { get; set; }  // 其它聚合根Id， 可null

    /// <summary>
    /// 成品Id
    /// </summary>
    public Guid? FinishProductId { get; set; } // 其它聚合根Id，可null

    ......

    // 其它属性
}

现在需求是，在查询聚合根 RawMaterialOutwarehouseRecord 集合结果中包含：

聚合根 RawMaterial 的 SerialNumber、Name
聚合根 Order 的 SerialNumber
聚合根 FinishProduct 的 SerialNumber

有问题查询实现

先看下面的查询实现：

    public virtual async Task<PagedResultDto<RawMaterialOutwarehouseRecordWithDetialsDto>> GetRawMaterialOutwarehouseRecordList2Async(GetRawMaterialOutwarehouseRecodsInput input)
    {
        var query =
            from outwarehouseRecord in (await _rawMaterialOutwarehouseRecordRepository.GetQueryableAsync())
            join rawMaterial in (await _rawMaterialRepository.GetQueryableAsync())
                on outwarehouseRecord.RawMaterialId equals rawMaterial.Id
            select new
            {
                outwarehouseRecord,
                rawMaterialSerialNumber = rawMaterial.SerialNumber,
                rawMaterialName = rawMaterial.Name,
            };

        query = query
            .WhereIf(
                !input.Filter.IsNullOrWhiteSpace(),
                x => x.rawMaterialSerialNumber.Contains(input.Filter) ||
                    (x.rawMaterialName != null && x.rawMaterialName.Contains(input.Filter))
            )
            .WhereIf(!string.IsNullOrWhiteSpace(input.RawMaterialSerialNumber), x => x.rawMaterialSerialNumber == input.RawMaterialSerialNumber)
            .WhereIf(!string.IsNullOrWhiteSpace(input.RawMaterialName), x => x.rawMaterialName == input.RawMaterialName)
            .WhereIf(input.MaxOutwarehouseTime != null, x => x.outwarehouseRecord.OutwarehouseTime <= input.MaxOutwarehouseTime)
            .WhereIf(input.MinOutwarehouseTime != null, x => x.outwarehouseRecord.OutwarehouseTime >= input.MinOutwarehouseTime)
            .WhereIf(input.MaxCreationTime != null, x => x.outwarehouseRecord.CreationTime <= input.MaxCreationTime)
            .WhereIf(input.MinCreationTime != null, x => x.outwarehouseRecord.CreationTime >= input.MinCreationTime)
            .WhereIf(input.MaxModifitionTime != null, x => x.outwarehouseRecord.LastModificationTime <= input.MaxModifitionTime)
            .WhereIf(input.MinModifitionTime != null, x => x.outwarehouseRecord.LastModificationTime >= input.MinModifitionTime)
            .PageBy(input.SkipCount, input.MaxResultCount);

        var queryResult = await AsyncExecuter.ToListAsync(query);

        var outwarehouseRecordDtos =  queryResult.Select(x =>
        {
            var outwarehouseRecordDto = ObjectMapper.Map<RawMaterialOutwarehouseRecord, RawMaterialOutwarehouseRecordWithDetialsDto>(x.outwarehouseRecord);

            outwarehouseRecordDto.RawMaterialSerialNumber = x.rawMaterialSerialNumber;
            outwarehouseRecordDto.RawMaterialName = x.rawMaterialName;

            return outwarehouseRecordDto;
        }).ToList();

        foreach (var dto in outwarehouseRecordDtos)
        {
            if (dto.FinishProductId != null)
            {
                dto.FinishProductSerialNumber =
                    (await _finishProductRepository.GetAsync(x.outwarehouseRecord.FinishProductId.Value))
                    .SerialNumber;

            }

            if (dto.OrderId != null)
            {
                dto.FinishProductSerialNumber =
                    (await _orderRepository.GetAsync(x.outwarehouseRecord.OrderId.Value))
                    .SerialNumber;
            }
        }

        var totalCount = await AsyncExecuter.CountAsync(query);

        return new PagedResultDto<RawMaterialOutwarehouseRecordWithDetialsDto>(
           totalCount,
           outwarehouseRecordDtos
       );
    }

出现性能问题的代码如下：

        foreach (var dto in outwarehouseRecordDtos)
        {
            if (dto.FinishProductId != null)
            {
                dto.FinishProductSerialNumber =
                    (await _finishProductRepository.GetAsync(dto.FinishProductId.Value))
                    .SerialNumber;

            }

            if (dto.OrderId != null)
            {
                dto.FinishProductSerialNumber =
                    (await _orderRepository.GetAsync(dto.OrderId.Value))
                    .SerialNumber;
            }
        }

如果查询结果需要 100 条 outwarehouseRecordDtos, 那为了获取聚合根 Order、FinishProduct 中的属性，
就需要额外执行 100 * （1 + 1) = 200 次数据库查询，严重影响查询效率。

改进查询实现

using System.Linq.Dynamic.Core;  // 支持 Linq 字符串排序
  
namespace WarehouseMs.WarehouseService.RawMaterials;

public class RawMaterialAppService : WarehouseServiceAppServiceBase, IRawMaterialAppService
{
    private readonly IRawMaterialRepository _rawMaterialRepository;
    private readonly IRawMaterialOutwarehouseRecordRepository _rawMaterialOutwarehouseRecordRepository;
    private readonly IFinishProductRepository _finishProductRepository;
    private readonly IOrderRepository _orderRepository;

    public RawMaterialAppService(
        IRawMaterialRepository rawMaterialRepository,
        IRawMaterialOutwarehouseRecordRepository rawMaterialOutwarehouseRecord,
        IFinishProductRepository finishProductRepository,
        IOrderRepository orderRepository)
    {
        _rawMaterialRepository = rawMaterialRepository;
        _rawMaterialOutwarehouseRecordRepository = rawMaterialOutwarehouseRecord;
        _finishProductRepository = finishProductRepository;
        _orderRepository = orderRepository;
    }  

    ......
    public virtual async Task<PagedResultDto<RawMaterialOutwarehouseRecordWithDetialsDto>> GetRawMaterialOutwarehouseRecordListAsync(GetRawMaterialOutwarehouseRecodsInput input)
    {
        var query =
            from outwarehouseRecord in (await _rawMaterialOutwarehouseRecordRepository.GetQueryableAsync())
            join rawMaterial in (await _rawMaterialRepository.GetQueryableAsync())
                on outwarehouseRecord.RawMaterialId equals rawMaterial.Id 

            join leftFinishProduct in (await _finishProductRepository.GetQueryableAsync())
                on outwarehouseRecord.FinishProductId equals leftFinishProduct.Id into LeftJionFinishProduct
            from finishProduct in LeftJionFinishProduct.DefaultIfEmpty() // 左外部联接

            join leftOrder in (await _orderRepository.GetQueryableAsync())
                 on outwarehouseRecord.OrderId equals leftOrder.Id into LeftJionOrder
            from order in LeftJionOrder.DefaultIfEmpty() // 左外部联接
            select new 
            {
                outwarehouseRecord,
                rawMaterialSerialNumber = rawMaterial.SerialNumber,
                rawMaterialName = rawMaterial.Name,
                finishProductSerialNumber = finishProduct != null ? finishProduct.SerialNumber : null,
                orderSerialNumber = order != null ? order.SerialNumber : null,
            };

        query = query
            .WhereIf(
                !input.Filter.IsNullOrWhiteSpace(),
                x => x.rawMaterialSerialNumber.Contains(input.Filter) ||
                    (x.rawMaterialName != null && x.rawMaterialName.Contains(input.Filter))
            )
            .WhereIf(!string.IsNullOrWhiteSpace(input.RawMaterialSerialNumber), x => x.rawMaterialSerialNumber == input.RawMaterialSerialNumber)
            .WhereIf(!string.IsNullOrWhiteSpace(input.RawMaterialName), x => x.rawMaterialName == input.RawMaterialName)
            .WhereIf(!string.IsNullOrWhiteSpace(input.FinishProductSerialNumber), x => x.finishProductSerialNumber == input.FinishProductSerialNumber)
            .WhereIf(!string.IsNullOrWhiteSpace(input.OrderSerialNumber), x => x.orderSerialNumber == input.OrderSerialNumber)
            .WhereIf(input.MaxOutwarehouseTime != null, x => x.outwarehouseRecord.OutwarehouseTime <= input.MaxOutwarehouseTime)
            .WhereIf(input.MinOutwarehouseTime != null, x => x.outwarehouseRecord.OutwarehouseTime >= input.MinOutwarehouseTime)
            .WhereIf(input.MaxCreationTime != null, x => x.outwarehouseRecord.CreationTime <= input.MaxCreationTime)
            .WhereIf(input.MinCreationTime != null, x => x.outwarehouseRecord.CreationTime >= input.MinCreationTime)
            .WhereIf(input.MaxModifitionTime != null, x => x.outwarehouseRecord.LastModificationTime <= input.MaxModifitionTime)
            .WhereIf(input.MinModifitionTime != null, x => x.outwarehouseRecord.LastModificationTime >= input.MinModifitionTime);

        // 获总数(不需要排序)
        var totalCount = await AsyncExecuter.CountAsync(query);

        query = query
            .OrderBy(
                input.Sorting.IsNullOrWhiteSpace()
                    ? "outwarehouseRecord.OutwarehouseTime desc"
                    : input.Sorting
            )
            .PageBy(input.SkipCount, input.MaxResultCount);

        var queryResult = await AsyncExecuter.ToListAsync(query);

        var outwarehouseRecordDtos = queryResult.Select(x =>
        {
            var outwarehouseRecordDto = ObjectMapper.Map<RawMaterialOutwarehouseRecord, RawMaterialOutwarehouseRecordWithDetialsDto>(x.outwarehouseRecord);

            outwarehouseRecordDto.RawMaterialSerialNumber = x.rawMaterialSerialNumber;
            outwarehouseRecordDto.RawMaterialName = x.rawMaterialName;
            outwarehouseRecordDto.FinishProductSerialNumber = x.finishProductSerialNumber;
            outwarehouseRecordDto.OrderSerialNumber = x.orderSerialNumber;

            return outwarehouseRecordDto;
        }).ToList();

        return new PagedResultDto<RawMaterialOutwarehouseRecordWithDetialsDto>(
           totalCount,
           outwarehouseRecordDtos
       );
    }
   

    ......
}

代码说明：

使用 xxxRepository.GetQueryableAsync() 返回聚合根的IQueryable<T>

排序：

     query = query
         .OrderBy(
             input.Sorting.IsNullOrWhiteSpace()
                 ? "outwarehouseRecord.OutwarehouseTime desc"
                 : input.Sorting
         )

使用程序集 System.Linq.Dynamic.Core 的动态Linq，其支持字符串排序，
注意：这里的排序字符串是: outwarehouseRecord.OutwarehouseTime desc,
其格式是 select 语句阶段：

     var query =
         from outwarehouseRecord in (await _rawMaterialOutwarehouseRecordRepository.GetQueryableAsync())
         .....
         select new 
         {
             outwarehouseRecord,
             .....
         };

“匿名对象的属性名.排序属性名” + “asc” 或“desc” ， 不区分大小写

关于 ToList 异步方法：
注意到，这里的 ToList异步方法使用了 AsyncExecuter.ToListAsync(), 而不是使用程序集 Microsoft.EntityFrameworkCore 中的 ToListAsync(),
如果使用程序集 Microsoft.EntityFrameworkCore 中的 ToListAsync(), 在 Appliction 层，就必须添加对 程序集 Microsoft.EntityFrameworkCore 引用，
导致整个架构锁定了 EF Core仓储（EfCoreRawMaterialRepository），如果切换其它仓储实现，比如：MongoDB仓储 （MongoDBRawMaterialRepository），
代码有可能就不通用了，因为 MongoDB 不支持异步 ToListAsync() 方法。

这里有 3 种方案：

使用通用异步方法（推荐）：

    var queryResult = await AsyncExecuter.ToListAsync(query);

不使用异步方法
```
    var queryResult = query.ToList();
```
在 Appliction 层添加对 程序集 Microsoft.EntityFrameworkCore 引用
```
    var queryResult = query.ToListAsync();
```
这种方案适用于已经明确以后不切换仓储实现的情况。

至于使用哪种方案，根据实际需求做取舍。

查询 GetInput：

    public class GetRawMaterialOutwarehouseRecodsInput : PagedAndSortedResultRequestDto
    {
        public string Filter { get; set; } = string.Empty;
        public string RawMaterialSerialNumber { get; set; } = string.Empty;
        public string RawMaterialName { get; set; } = string.Empty;
        public string OrderSerialNumber { get; set; } = string.Empty;
        public string FinishProductSerialNumber { get; set; } = string.Empty;
        public DateTime? MaxOutwarehouseTime { get; set; }
        public DateTime? MinOutwarehouseTime { get; set; }
        public DateTime? MaxCreationTime { get; set; }
        public DateTime? MinCreationTime { get; set; }
        public DateTime? MaxModifitionTime { get; set; }
        public DateTime? MinModifitionTime { get; set; }
    }

聚合根：
RawMaterialOutwarehouseRecord.CS


public class RawMaterialOutwarehouseRecord : AuditedAggregateRoot<Guid>
{
    [NotNull]
    /// <summary>
    /// 原料Id
    /// </summary>
    public Guid RawMaterialId { get; set; }

    [NotNull]
    /// <summary>
    /// 出库类型
    /// </summary>
    public RawMaterialOutwarehouseType OutwarehouseType { get; set; }

    [NotNull]
    /// <summary>
    /// 原料出库数量
    /// </summary>
    public int OutwarehouseCount { get; private set; }

    /// <summary>
    /// 订单Id
    /// </summary>
    public Guid? OrderId { get; set; }  // 其它聚合根Id， 可null

    /// <summary>
    /// 成品Id
    /// </summary>
    public Guid? FinishProductId { get; set; } // 其它聚合根Id，可null

    /// <summary>
    /// 成品数量
    /// </summary>
    public int? FinishProductCount { get; set; }

    [NotNull]
    /// <summary>
    /// 出库时间
    /// </summary> 
    public DateTime OutwarehouseTime { get; set; }

    /// <summary>
    /// 备注
    /// </summary>
    public string Comment { get; private set; }

继承： AuditedAggregateRoot<Guid>

Dto:

RawMaterialOutwarehouseRecordDto .CS

    public class RawMaterialOutwarehouseRecordDto : AuditedEntityDto<Guid>
    {
        public Guid RawMaterialId { get; set; }

        public RawMaterialOutwarehouseType OutwarehouseType { get; set; }

        public int OutwarehouseCount { get;  set; }

        public Guid? OrderId { get; set; }
        public Guid? FinishProductId { get; set; }

        public int? FinishProductCount { get; set; }

        public DateTime OutwarehouseTime { get; set; }
      
        public string Comment { get;  set; }
    }

继承：AuditedEntityDto<Guid>

RawMaterialOutwarehouseRecordWithDetialsDto

    public class RawMaterialOutwarehouseRecordWithDetialsDto : RawMaterialOutwarehouseRecordDto 
    {
        public string RawMaterialSerialNumber { get; set; }
        public string RawMaterialName { get; set; }
        public string OrderSerialNumber { get; set; }
        public string FinishProductSerialNumber { get; set; }
    }

继承：RawMaterialOutwarehouseRecordDto, 然后新增其它聚合根的属性。

Dto 配置：

        CreateMap<RawMaterialOutwarehouseRecord, RawMaterialOutwarehouseRecordWithDetialsDto>()
            .Ignore(p => p.RawMaterialSerialNumber)
            .Ignore(p => p.RawMaterialName)
            .Ignore(p => p.FinishProductSerialNumber)
            .Ignore(p => p.OrderSerialNumber);

注意：Dto: RawMaterialOutwarehouseRecordWithDetialsDto 中聚合根 RawMaterialOutwarehouseRecord 不存在的属性需要 Ignore()，否则运行发生异常。

posted @ 2023-10-13 13:52 easy5 阅读(163) 评论(0) 编辑收藏举报

刷新页面返回顶部

easy5

Abp vNext : Appliction 层 ApplicationService 中聚合根之间级联查询

背景

有问题查询实现

改进查询实现

公告