Linq where clause confusion-CodePudding

Good day, everyone!

I've written one query for my Automation test, but it's taking too long to execute, and I'm not sure how to optimize it effectively because I'm new to the Linq where clause.

Could someone please assist me with this?

var order = OrderRepositoryX.GetOrderByStatus(OrderStatusType.Dispatched, 4000)
            .Where(x => x.siteId == 1 || x.siteId == 10 || x.siteId == 8 || x.siteId == 16 || x.siteId == 26 || x.siteId == 27)
            .Where(x =>
            {
                var totalPrice = OrderRepository.GetOrderById(shared_parameters.testConfiguration, x.orderId).TotalPrice;
                if (totalPrice < 500)
                    return false;
                return true;
            })
            .Where(x =>
            {
                var cnt = ReturnOrderRepositoryX.CheckReturnOrderExists(x.orderId);
                if (cnt > 0)
                    return false;
                return true;
            })
            .Where(x =>
            {
                var cnt = OrderRepositoryX.CheckActiveOrderJobDetailsByOrderId(x.orderId);
                if (cnt > 0)
                    return false;
                return true;
            })
            .FirstOrDefault();

CodePudding user response：

The biggest code smell here is that you are calling other repositories inside the Where clause which (assuming that repositories actually hit database) it will effectively mean that you are hitting database per every queried item. Lets imagine that OrderRepositoryX.GetOrderByStatus(OrderStatusType.Dispatched, 4000) and first Where will result in 1000 items, only second Whereclause will lead to 1000 queries to the database (and you have some more calls to repositories in subsequent Wheres). And all of this to get just one item (i.e. FirstOrDefault).

Usual approach is to avoid calling database in loops (what Where basically does here) and rewrite such code so only single SQL query will be performed against the database returning only what is needed and performing all the filtering on the database side.

CodePudding user response：

Please try this instead Avoid too many where clauses. It gets a result and then applies another check on the whole set.

var order = OrderRepositoryX.GetOrderByStatus(OrderStatusType.Dispatched, 4000)
                    .FirstOrDefault(x => x.siteId == 1 || x.siteId == 10 || x.siteId == 8 || x.siteId == 16 ||
                                x.siteId == 26 || x.siteId == 27) &&
                (x =>
                {
                    var totalPrice = OrderRepository.GetOrderById(shared_parameters.testConfiguration, x.orderId)
                        .TotalPrice;
                    return totalPrice >= 500;
                })
                && (x =>
                {
                    var cnt = ReturnOrderRepositoryX.CheckReturnOrderExists(x.orderId);
                    return cnt <= 0;
                })
                && (x =>
                {
                    var cnt = OrderRepositoryX.CheckActiveOrderJobDetailsByOrderId(x.orderId);
                    return cnt <= 0;
                });