The following query is fairly slow.

Something that I’m aware of:

  • .AsNoTracking can be specified once at the start of the query. Subsequent calls will not do anything
  • .AsSplitQuery can be removed since it’s set globally x.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
  • EF Core Eager Loading .Includes are unnecessary since EF Core is aware of the relationships being used in the .Select and .Where clauses.

I’m writing this question mainly because of the suggestions made by @Panagiotis Kanavos. I didn’t fully understand all of his points in this question:

why is GroupJoin used in the first place? EF will generate JOINs based on the relations between the entities. One could question the need for Include too – EF will include related entities as needed if they’re used in the Select clause. Finally, var query = dataSet1.Union(dataSet2); is a smell – those are queries, not datasets. This will result in a big slow query. The performance impact will be far worse than entity tracking

The reason GroupJoin is used, is because there is no foreign key constraint/relationship between MarketTransaction and HedgeAccount, which is essentially a left join behind the scenes, just like: join h in _dbContext.HedgeAccounts on f.MarketAccount equals h.Account into gha from account in gha.DefaultIfEmpty().

Regarding .Union, I’m not completely sure how this can be omitted due to the applied filters.

  1. I know .CountAsync is used after the projection, so EF Core is forced to join all these tables, which is unnecessary.
public sealed record GetAllReviewAndReleaseQuery(ReviewAndReleaseFilterDto Filter, Context FlagContext) : IRequest<ListDto<ReviewAndReleaseItemDto>>;

public sealed class GetAllReviewAndReleaseQueryHandler : IRequestHandler<GetAllReviewAndReleaseQuery, ListDto<ReviewAndReleaseItemDto>>
{
    readonly ILogger<GetAllReviewAndReleaseQueryHandler> _logger;
    readonly string _userEmail;
    readonly AppDbContext _dbContext;
    readonly LdClient _ldClient;
    readonly EmployeeLocationService _employeeLocationService;

    public GetAllReviewAndReleaseQueryHandler(ILogger<GetAllReviewAndReleaseQueryHandler> logger, CurrentUser userEmail, AppDbContext dbContext, LdClient ldClient, EmployeeLocationService employeeLocationService)
    {
        _logger = logger;
        _userEmail = userEmail?.Email ?? CurrentUser.InternalUser;
        _dbContext = dbContext;
        _ldClient = ldClient;
        _employeeLocationService = employeeLocationService;
    }

    public async Task<ListDto<ReviewAndReleaseItemDto>> Handle(GetAllReviewAndReleaseQuery request, CancellationToken cancellationToken)
    {
        var isEmployeeGroupedLocationEnabled = _ldClient.BoolVariation(FeatureFlags.EnableEmployeeGroupedLocation, request.FlagContext);

        if (!request.Filter.ValidatePagingParametersAreGreaterThanZero())
            throw new PaginationNotValidException("Pagination parameters are not valid, please review your parameters");

        if (request.Filter.Limit > 1000)
        {
            request.Filter.Limit = 15;
        }

        var employeeLocations = _employeeLocationService.GetEmployeeLocations();
        var groupedEmployeeLocations = _employeeLocationService.GetGroupedEmployeeLocations();

        var dataSet1 = _dbContext.MarketTransactions.AsNoTracking()
            .AsSplitQuery()
            .Include(a => a.Commodity).ThenInclude(a => a.Product).AsNoTracking()
            .Include(a => a.Contract).ThenInclude(a => a.Customer).AsNoTracking()
            .Include(a => a.Offer).ThenInclude(a => a.Customer).AsNoTracking()
            .Where(f => f.IsActive == true && (f.Source == EMarketTransactionSource.ExternalFill ||
                                               f.Source == EMarketTransactionSource.PreHedge || 
                                               (isEmployeeGroupedLocationEnabled 
                                                   ? groupedEmployeeLocations.Any(em => (em.DestionationId == f.Contract.DeliveryLocationId && em.ContractLocationId == f.Contract.LocationId && em.RegionId == f.Contract.RegionId) ||
                                                                                        (em.DestionationId == f.Offer.DeliveryLocationId))
                                                   : employeeLocations.Any(em => em.LocationId == f.Contract.DeliveryLocationId || em.LocationId == f.Offer.DeliveryLocationId))))
            .GroupJoin(
                _dbContext.HedgeAccounts.AsNoTracking(), 
                transaction => transaction.MarketAccount,
                hedgeAcct => hedgeAcct.Account, 
                (transaction, hedgeAcct) => new { Transaction = transaction, HedgeAccounts = hedgeAcct }) 
            .SelectMany(
                x => x.HedgeAccounts.DefaultIfEmpty(), 
                (x, hedgeAcct) => new { x.Transaction, HedgeAccount = hedgeAcct })
            .Select(f => new ReviewAndReleaseDto
            {
                Id = f.Transaction.Id, 
                CustomerFirstName = f.Transaction.Contract == null || f.Transaction.Contract.Customer == null ? (f.Transaction.Contract.Customer.FirstName ?? f.Transaction.Offer.Customer.FirstName) : string.Empty,
                CustomerLastName = f.Transaction.Contract == null || f.Transaction.Contract.Customer == null ? (f.Transaction.Contract.Customer.LastName ?? f.Transaction.Offer.Customer.LastName) : string.Empty,
                CustomerNumber = f.Transaction.Contract == null || f.Transaction.Contract.Customer == null ? (f.Transaction.Contract.Customer.Number ?? f.Transaction.Offer.Customer.Number) : string.Empty,
                CustomerId = f.Transaction.Contract == null || f.Transaction.Contract.Customer == null ? f.Transaction.Offer.Customer.Id : f.Transaction.Contract.Customer.Id,
                CreationDate = f.Transaction.CreatedOn,
                UpdatedOn = f.Transaction.UpdatedOn,
                IsReal = true,
                FuturesPrice = f.Transaction.FuturesPrice,
                IsSell = f.Transaction.IsSell,
                MarketAccount = f.Transaction.MarketAccount,
                WasAcknowledge = f.Transaction.WasAcknowledge,
                PassFill = f.Transaction.PassFill,
                IsGtc = f.Transaction.IsGtc,
                Expiration = f.Transaction.Expiration,
                Lots = f.Transaction.Lots,
                WorkingLots = f.Transaction.WorkingLots,
                ProductId = f.Transaction.Commodity.Product.Id,
                InstrumentCode = f.Transaction.Commodity.Product.Code,
                MarketPrice = f.Transaction.MarketPrice,
                FuturesMonth = f.Transaction.FuturesMonth,
                MarketId = Convert.ToString(f.Transaction.MarketId),
                AccountName = Convert.ToString(f.HedgeAccount.Name),
                InternalCode = Convert.ToString(f.Transaction.InternalCode),
                Source = f.Transaction.Source == EMarketTransactionSource.Contract ? EMarketTransactionSource.Contract :
                    f.Transaction.Source == EMarketTransactionSource.Accumulation ? EMarketTransactionSource.Accumulation :
                    f.Transaction.Source == EMarketTransactionSource.Offer ? EMarketTransactionSource.Offer : f.Transaction.Source == EMarketTransactionSource.PreHedge ? EMarketTransactionSource.PreHedge : EMarketTransactionSource.ExternalFill,
                Type = f.Transaction.Type == EMarketTransactionType.Market ? EMarketTransactionType.Market : EMarketTransactionType.Limit,
                Event = f.Transaction.Event == ETransactionEvent.Creation ? ETransactionEvent.Creation :
                    f.Transaction.Event == ETransactionEvent.Edition ? ETransactionEvent.Edition :
                    f.Transaction.Event == ETransactionEvent.Cancelation ? ETransactionEvent.Cancelation :
                    f.Transaction.Event == ETransactionEvent.Roll ? ETransactionEvent.Roll :
                    f.Transaction.Event == ETransactionEvent.Book ? ETransactionEvent.Book :
                    f.Transaction.Event == ETransactionEvent.RollBack ? ETransactionEvent.RollBack :
                    f.Transaction.Event == ETransactionEvent.PartiallyFilled ? ETransactionEvent.PartiallyFilled :
                    f.Transaction.Event == ETransactionEvent.Filled ? ETransactionEvent.Filled : ETransactionEvent.Completed,
                State = f.Transaction.State.Value == EMarketTransactionState.Ready ? EMarketTransactionState.Ready :
                    f.Transaction.State.Value == EMarketTransactionState.Denied ? EMarketTransactionState.Denied :
                    f.Transaction.State.Value == EMarketTransactionState.Pending ? EMarketTransactionState.Pending :
                    f.Transaction.State.Value == EMarketTransactionState.Working ? EMarketTransactionState.Working :
                    f.Transaction.State.Value == EMarketTransactionState.PartiallyFilled ? EMarketTransactionState.PartiallyFilled :
                    f.Transaction.State.Value == EMarketTransactionState.Filled ? EMarketTransactionState.Filled :
                    f.Transaction.State.Value == EMarketTransactionState.Canceled ? EMarketTransactionState.Canceled :
                    f.Transaction.State.Value == EMarketTransactionState.Rejected ? EMarketTransactionState.Rejected :
                    EMarketTransactionState.Expired
            });

        var dataSet2 = _dbContext.Set<OfferMonitoring>().AsNoTracking()
            .AsSplitQuery()
            .Include(a => a.Offer).ThenInclude(a => a.Commodity).ThenInclude(a => a.Product).AsNoTracking()
            .Include(a => a.Offer).ThenInclude(a => a.Customer).AsNoTracking()
            .Where(f => f.Action.Value == EOfferMonitorAction.Add && (isEmployeeGroupedLocationEnabled ? groupedEmployeeLocations.Any(em => em.DestionationId == f.Offer.DeliveryLocationId) : employeeLocations.Any(em => em.LocationId == f.Offer.DeliveryLocationId)))
            .Select(f => new ReviewAndReleaseDto
            {
                Id = f.Id,
                CustomerFirstName = f.Offer.Customer.FirstName,
                CustomerLastName = f.Offer.Customer.LastName,
                CustomerNumber = f.Offer.Customer.Number,
                CustomerId = f.Offer.Customer.Id,
                CreationDate = f.CreatedOn,
                UpdatedOn = f.UpdatedOn,
                IsReal = false,
                FuturesPrice = f.Offer.FuturesPrice ?? 0,
                IsSell = !f.Offer.IsSell,
                MarketAccount = 0,
                WasAcknowledge = false,
                PassFill = false,
                IsGtc = f.Offer.Gtc,
                Expiration = f.Offer.Expiration,
                Lots = (int)(f.Offer.Quantity / f.Offer.Commodity.LotFactor),
                WorkingLots = 0,
                ProductId = f.Offer.Commodity.ProductId,
                InstrumentCode = f.Offer.Commodity.Product.Code,
                MarketPrice = f.Offer.FreightPrice,
                FuturesMonth = f.Offer.FuturesMonth,
                MarketId = Convert.ToString(null),
                AccountName = Convert.ToString(null),
                InternalCode = Convert.ToString(f.Offer.InternalCode),
                Source = EMarketTransactionSource.Offer,
                Type = EMarketTransactionType.Limit,
                Event = f.Action.Value == EOfferMonitorAction.Cancel ? ETransactionEvent.Cancelation :
                    f.Action.Value == EOfferMonitorAction.Add ? ETransactionEvent.Creation : ETransactionEvent.Edition,
                State = EMarketTransactionState.Pending
            });

        var query = dataSet1.Union(dataSet2);

        query = ApplyFilters(request, query);
        var total = await query.CountAsync(cancellationToken);

        query = query.OrderByDescending(x => x.UpdatedOn ?? x.CreationDate);


        query = query.GetPagedQuery(request.Filter.Start, request.Filter.Limit);

        var items = await query.ToListAsync(cancellationToken);

        var tag50CurrentUser = await _dbContext.Employees
                    .AsNoTracking()
                    .Where(e => e.Email == _userEmail)
                        .Select(e => e.Tag50)
                    .FirstOrDefaultAsync(cancellationToken);

        var mappedResults = MapToDto(items, total, tag50CurrentUser);
        _logger.ReviewAndReleaseAll();
        return mappedResults;
    }

    static ListDto<ReviewAndReleaseItemDto> MapToDto(List<ReviewAndReleaseDto> items, int total, bool tag50CurrentUser)
    {
        var data = new ListDto<ReviewAndReleaseItemDto>
        {
            Total = total,
            List = items.Select(item => new ReviewAndReleaseItemDto
            {
                Id = item.Id,
                OrderNumber = item.MarketId,
                Type = Helper.MapType(item.Type),
                TypeName = item.Type.ToString(),
                CreationDate = item.CreationDate,
                UpdateDate = item.UpdatedOn,
                Price = item.Type == EMarketTransactionType.Market ? null : item.FuturesPrice,
                IsSell = item.IsSell,
                Side = Helper.MapSide(item.IsSell),
                Quantity = item.Lots,
                WorkingQuantity = item.WorkingLots,
                Status = Helper.MapStatusGridToString(item.State, item.Event),
                AccountNumber = item.MarketAccount,
                AccountName = item.AccountName,
                AvgFillPrice = item.IsReal ? item.MarketPrice : 0,
                WasAcknowledge = item.WasAcknowledge,
                PassFill = item.PassFill,
                GTC = new()
                {
                    IsGTC = item.IsGtc,
                    Expiration = item.Expiration
                },
                FuturesMonth = item.FuturesMonth,
                InternalCode = item.InternalCode,
                Customer = new()
                {
                    Id = item.CustomerId,
                    Name = $"{item.CustomerFirstName} {item.CustomerLastName}".Trim(),
                    Number = item.CustomerNumber,
                },
                Source = Helper.MapSource(item.Source),
                Symbol = new() { Code = item.InstrumentCode },
                Restrictions = new()
                {
                    CanBeApproved = item.IsReal && item.State == EMarketTransactionState.Ready && tag50CurrentUser,
                    CanBeDenied = item.IsReal && item.Type == EMarketTransactionType.Market && tag50CurrentUser &&
                                  (item.State == EMarketTransactionState.Ready || (item.State == EMarketTransactionState.Pending && item.Event == ETransactionEvent.Creation && !item.WasAcknowledge)),
                    CanBeResent = item.IsReal && item.State == EMarketTransactionState.Rejected && item.Type == EMarketTransactionType.Market && tag50CurrentUser && !item.WasAcknowledge,
                    CanBeAcknowledged = item.IsReal && item.Type == EMarketTransactionType.Market && item.State == EMarketTransactionState.Rejected && !item.WasAcknowledge && tag50CurrentUser,
                }
            }).ToList()
        };
        return data;
    }

    static IQueryable<ReviewAndReleaseDto> ApplyFilters(GetAllReviewAndReleaseQuery request, IQueryable<ReviewAndReleaseDto> query)
    {
        var endDate = request.Filter.EndDate ?? DateTime.Now.Date;

        query = request.Filter.StartDate != null
            ? query.Where(x => x.UpdatedOn == null ? x.CreationDate.Date >= request.Filter.StartDate && x.CreationDate.Date <= endDate : x.UpdatedOn.Value.Date >= request.Filter.StartDate && x.UpdatedOn.Value.Date <= endDate)
            : query.Where(x => x.UpdatedOn == null ? x.CreationDate.Date <= endDate : x.UpdatedOn.Value.Date <= endDate);

        query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.Number), x => x.MarketId.ToUpper().Contains(request.Filter.Number.ToUpper())
                                                                                     || x.InternalCode.ToUpper().Contains(request.Filter.Number.ToUpper()));
        query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.AccountName), x => x.AccountName.ToUpper().Contains(request.Filter.AccountName.ToUpper()));

        query = query.WhereIf(request.Filter.FuturesMonth is { Count: > 0 }, x => request.Filter.FuturesMonth.Contains(x.FuturesMonth));
        query = query.WhereIf(request.Filter.Symbol is { Count: > 0 }, x => request.Filter.Symbol.Contains(x.ProductId));
        query = query.WhereIf(request.Filter.CustomerId is { Count: > 0 }, x => request.Filter.CustomerId.Contains(x.CustomerId));
        query = query.WhereIf(!string.IsNullOrEmpty(request.Filter.ContractOrCustomer), x => x.InternalCode.Contains(request.Filter.ContractOrCustomer) ||
                                                                              x.CustomerFirstName.Contains(request.Filter.ContractOrCustomer) ||
                                                                              x.CustomerLastName.Contains(request.Filter.ContractOrCustomer) ||
                                                                              x.CustomerNumber.Contains(request.Filter.ContractOrCustomer));
        if (request.Filter.Status is { Count: > 0 })
        {
            var listStatusFilter = request.Filter.Status.Select(Helper.MapStatusToEnum).ToList();
            query = query.Where(x => listStatusFilter.Contains(x.State));
        }

        return query;
    }
}