IEnumerable extension methods called on IQueryable causing performance issues

Question

Whenever I call an extension method that accepts an IEnumerable on an IQueryable, the remainder of the process where that list/entity is being used in is incredibly slow.

POR QUEEEEE?!

The problem doesn't seem to be related to the actual structure of the code, as it's otherwise optimal, and when I spin up a new database for unit testing it, the problem doesn't seem to appear.

This is the extension method:

        public static Bill FirstById(this IEnumerable<Bill> query, int billId)
        {
            return query.FirstOrDefault(r => r.Id == billId);
        }

This is how it's being used:

        public Bill GetDeposit(int depositId)
        {
            var deposit = _depositRepository
                .AndSalesOrder()
                .AndSalesOrderCustomerContact()
                .AndDepositApplications()
                .FirstById(depositId);

            return deposit;
        }

And this is how the actual entity is being used:

        public bool ConvertDeposit(List<ConvertDepositSubmitModel> conversions)
        {
            if (conversions.Any())
            {
                var depositId = conversions.Select(c => c.DepositId)
                .Distinct()
                .Single();

                var billPaymentIds = conversions.Select(c => c.BillPaymentId);

                var deposit = _dataProvider.GetDeposit(depositId);

                var billPayments = _dataProvider.GetBillPayments(billPaymentIds);

                var totalConversionAmount = conversions.Sum(c => c.Amount);

                var unappliedDepositAmount = (deposit.BillStatusId == BillStatus.Credited ? 0 : deposit.TotalSell - deposit.Balance) - deposit.DepositApplications.Select(a => a.Amount).DefaultIfEmpty(0).Sum();

                if (unappliedDepositAmount != totalConversionAmount)
                {
                    throw new Exception("The provided conversion amount would not fully convert the Deposit.");
                }

                _unitOfWork.TryTransactionAndCommit(() =>
                {

                    foreach (var conversion in conversions)
                    {
                        var billPayment = billPayments.FirstByBillPaymentId(conversion.BillPaymentId);


                        this.CreateBillPaymentBill(deposit, conversion);

                        this.CreateMoneyOnAccountTransaction(deposit, conversion);

                        this.UpdateBillPayment(conversion, billPayment);
                    }

                    this.UpdateNetProceeds(billPayments);

                    this.ApplyCreditCardFees(billPaymentIds);

                    var customerCredit = this.CreateCustomerCredit(deposit, totalConversionAmount);

                    this.CreateCustomerCreditBill(deposit, customerCredit);

                    this.UpdateDeposit(deposit, totalConversionAmount);
                });
            }
            else
            {
                throw new Exception("The provided set of bill payments was empty.");
            }

            return true;
        }

We're seeing that each method, which has been tested rigorously, is producing the following diagnostic results:

PV2ANZAC                
GetDeposit: 33434ms
GetBillPayments: 54ms
CreateBillPaymentBill1: 17775ms
CreateMoneyOnAccountTransaction1: 10774ms
UpdateBillPayment1: 10810ms
UpdateNetProceeds: 18130ms
ApplyCreditCardFees: 17206ms
Insert CustomerCredit: 10795ms
CustomerCredit SaveChanges: 16276ms
CreateCustomerCredit: 27075ms
CreateCustomerCreditBill: 10688ms

And we are definitely expecting everything to be at least an order of magnitude less than what it is.

*...when I spin up a new database for unit testing...* then you **are not unit testing**. Unit tests *have no dependencies*. You are describing **integration** testing. — Daniel Mann, May 01 '19 at 20:45

score 0 · Answer 1 · edited May 01 '19 at 20:46

Why would you create an extension method that accepts an IEnumerable?

Obviously, when you call the IEnumerable extension method on an IQueryable, the IQueryable is going to be hydrated, and you're going to bring in every row in the Bill table when you only need one!

Now, if we can assume that GetBillPayments is similarly calling the database, that explains the discrepancy here:

GetDeposit: 33434ms
GetBillPayments: 54ms

score 0 · Answer 2 · answered May 01 '19 at 20:46

Check for the following two things:

1) You're using change tracking, which can be responsible for abominable slowness. See: https://weblog.west-wind.com/posts/2014/dec/21/gotcha-entity-framework-gets-slow-in-long-iteration-loops#Turn-off-Change-Tracking.

2) You're pulling a bunch of records from the database, and then querying on that set in memory instead of querying what you want at the database level. Check what queries EF is sending, and check to make sure you don't materialize the list at the repository level.

IEnumerable extension methods called on IQueryable causing performance issues

2 Answers2